May 20, 2008 at 3:13 pm
Ive setup a transaction replication, sometimes updates are done to this system where procedures are dropped and added (thats how our automated system changes procedure code). Is there any way to accommodate this with the replication now setup?
As I currently understand it you cannot add/drop on the fly (I do know about removing the article and then adding it back)?
May 20, 2008 at 10:22 pm
Unfortunately, replication does not permit an object that is published to be dropped.
SQL 2005 does allow you to ALTER a stored procedure that is published and can replicate that change to the subscriber. This is what I would use - you might need to adjust some work practices at your site though.
Otherwise, you will need to drop subscriber(s), drop article(s), drop/create sproc, add article(s), add subscriber(s).
May 21, 2008 at 3:08 pm
happycat59 (5/20/2008)
Unfortunately, replication does not permit an object that is published to be dropped.SQL 2005 does allow you to ALTER a stored procedure that is published and can replicate that change to the subscriber. This is what I would use - you might need to adjust some work practices at your site though.
Otherwise, you will need to drop subscriber(s), drop article(s), drop/create sproc, add article(s), add subscriber(s).
Darn, is there a way to ensure that the above steps will only send a snapshot of the changes and not a complete new snapshot. I tried it and it seemed to send the whole snapshot again.
May 21, 2008 at 5:12 pm
Transactional replication will only send (to the subscriber) enough of the snapshot to cover the new articles. The snapshot itself is always complete (includes all articles).
Are you dropping all articles ? This is not necessary. You only need to drop the articles for the objects that must be dropped.
It is possible to get replication to conditionally deliver the snapshot for each article in a publication. I wouldn't recommend this for now since it is based on whether the objects already exist on the subscriber which would normally be the case for you
May 23, 2008 at 7:06 pm
he would have to drop all the articles if they are part of one publication, which means, the snapshot will send everything to the subscribers.
you can always break out your publication so that everything isn't sent.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply