November 14, 2014 at 1:01 pm
There is an upgrade that needs to happen in application for that purpose i need to stop transactional replication for the sql 2008 r2 database.There are 2000 tables replicated so i cannot remove all tables from replication and back again. Please let me know how to stop replication temporarily.
November 17, 2014 at 8:22 am
Hi, your question is not very clear here? If you are upgrading the application that hits the Subscriber database and you don't want data to replicate during that time (for whatever reason although i cant see why) you can simply stop the Distribution Agent jobs.
If you can add more detail that would be helpful.
Thanks
November 17, 2014 at 8:35 am
The upgrade is being done on Publisher database. Though i stopped the disabled the distribution agent and log reader agent the application still shows a message that those tables are still in replication.
The only solution worked for me is to remove those tables from replication and add them later once upgrade is completed on publisherr database.
But i want to find other solution for this where i can stop the replication temporarily with the agents etc.
November 17, 2014 at 9:11 am
Ah...then this means that the upgrade is attempting to modify your replicated tables by either changing the Primary Key or trying to drop and\or recreate them. In which case unfortunately I don't think there is any way around this.
November 17, 2014 at 9:15 am
ok fine. Let me know if any option available.
November 17, 2014 at 7:52 pm
You can't just temporarily stop transactional replication - it will attempt to resume where it left off. After all, it is attempting to be transactional - so it will try to apply all transactions that occurred on the publisher to the subscriber.
You could consider scripting your publication(s) and subscription(s) and then dropping the subscriptions and publications. Then apply whatever changes to your database and then run the scripts to re-create the publication(s) and subscription(s).
This may or may not work depending on what changes you are making - you may need to make equivalent changes to all subscribers otherwise you could end up with tables that don't match and/or missing data on your subscribers.
November 18, 2014 at 4:57 pm
Is this a third party application?
What is the purpose of the subscriber database?
How many tables are being dropped/recreated or having their PK modified.
One option is to drop the articles for the tables that are being thus modified and then add them back afterward, either to the existing publication or a new publication.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply