Pause SQL server replication temporarily

sql-server sql-server-2008 replication database-replication
PyQL picture PyQL · Feb 20, 2013 · Viewed 34.7k times · Source

We have a transactional replication setup using 3 SQL Servers, 1st as publisher, 2nd as distributor, and 3rd as subscriber.

We have an activity to change the location of the replicated DB (subscriber) using de-attach and attach method. During this activity, I will need to stop the SQL server and hence all replicated transactions will fail.

What's the proper way to pause the replication during this activity, so when I attach the DB again and start the SQL service, replication will resume normally.

Thanks

Answer

steoleary picture steoleary · Feb 20, 2013

Please see the following link for details of how to accomplish this:

Start and Stop a Replication Agent

The above article doesn't appear to give information on stopping the distribution agent, this can be achieved by using the stored procs detailed in the link below:

Start/Stop SQL Server Replication Agent using TSQL

Related questions

Why SQL Server doesn't allow to remove a Distributor exactly after Configuration?

I Configured a distribution in SQL Server 2008 using both Wizard and T-SQL but after it when I want to remove it Using Wizard (right clicking on Replication and choosing 'Disable Publishing and Distribution...') or executing following command with and …

Read More
How do I check SQL replication status via T-SQL?

I want to be able to check the status of a publication and subscription in SQL Server 2008 T-SQL. I want to be able to determine if its okay, when was the last successful, sync, etc.. Is this possible?

Read More
Guide for Replication in MS SQL Server 2008

I wonder if anyone know/have a good guide for Replication in MS SQL Server 2008? I want to set up a Replication between two different computers that run MS SQL Server 2008, the master runs on Windows Vista, and the slave …

Read More