To Stop Replication Temporarily.


I want to stop replication (Transactional) temporarily. I don't want to delete any publication /subscription. I just want to stop or disable it.
So that when I enable then everything should be as it was bebore stopping/disabling.
Who is Participating?
yes keep the log reader running and stop  the agent which is same as  publication name (stop synchronizing)
it will keep on logging all the changes  once you start synchronizing it will apply all the changes to the subscriber.
Note: make the subscribtion expires to never (by default i think it is 48 hourse) otherwise if you don't sync for 48 hours you have to apply the snaphot again or it will become useless.
Aneesh RetnakaranDatabase AdministratorCommented:
TRY Stopping the distribution agent
MateenAuthor Commented:
I stopped snapshot agents, logreader agents. distributor agents.

and above all I stopped SQLServerAgent.

then I tried

alter table quality disable trigger all

I receive the error.

Could not do so because the table quailty is being used in replication.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Aneesh RetnakaranDatabase AdministratorCommented:
Actually, I am not so experienced in replication, Hope someone else will help you
MateenAuthor Commented:
>alter table quality disable trigger all

if you stop replication and then make any schema changes it wont work.
because this table exists as an article inside your publication you have to drop the article from that table if you want to do that.

one more thing it is not recommended to have trigger at the pulisher and subscriber if you want to do so you have to create the trigger with [ NOT FOR REPLICATION ]  clause.

come to your question it seems you want to disable all triggers from the table quantity generate the script for those and delete those triggers using the enterprse manager from quantity table and later on after you accomplish what you want add them. deleting trigger from publisher wont effect your replication. but diabling will.

MateenAuthor Commented:
Hi imran_fast

Thanks for your detailed answer.

Now I have got everything right.

Now my only question Is it possible to stop replication keeping publications/subscriptios etc intact?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.