SQLTeam.com | Weblogs | Forums

Enabling and disabling all articles in a SQL replication


#1

I have the need to stop and start replication of all articles in a specific database in SQL. When a deployment occurs, we need to stop replication, and when deployment ends, to start the replication again.


#2

Can't you just stop/start the log reader agent? That's what we've done in the past during deployments.


#3

well, you can just stop the replication job, this will not replicate the data to the other servers. You can keep the log reader going as it will prepare all the data to be ready for replication. Keep in mind about the timing, if you set to keep your historical data for a very short period of time, your subscription might expire and need to re-sync again.


#4

Thank you both for your prompt reply.
Both solutions sounds ideal since this will release the table/articles for replication. But how do you do it sqlquery and or script?
since we are doing deployments via RM I need to be able to in cooperate it in our RM as a Release component- which is basically a method/tool of invoking a script...
I have done research and found that there are stored procedures: But this points to a distribution and not a publication.
sp_MSstopdistribution_agent
As I said I can do this via the SQLMS but it defeats the purpose of continuous release.


#5

The log reader agent runs as a job, so you would disable/enable it via sp_update_job.

  • This forum software isn't allowing me to include the sp_update_job code. You would use @enabled to disable (0)/enable (1) it. @job_name would be the name of the log reader agent job.