This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
How to pause the subscriber end of transactional replication
Question
How do I go about pausing transactional replication on the subscriber end.
We have a publisher on Server A, a distributor on Server B and a subscriber on Server C. All are in the same network.
Some background, we have quite a busy BizTalk server where we replicate the data from the BAM parts to a separate database server which will be used for a customized web application for statistics, searching etc. Seeing as the tables in the BizTalk server are both quite large and missing indexes etc since they’re basically just there to have data dumped into them we opted to have the second, replicated database in which we can apply various optimizations for reading the data.
One thing I’ve noticed though is that adding a basic, non-clustered, index takes forever and causes all kinds of locks (we’re using SQL Server Standard so Online indexing isn’t an option unfortunately) I was thinking that giving the database a bit of respite from all the write operations from the BizTalk data while creating the indexes would be a good idea (right or wrong?).
Question is, how do I pause the replication (without losing any data from the publisher) on the subscriber end while creating the indexes?
asked 2019-02-12 by JaggenSWE
Answer
Replication runs using “Agents“–essentially external executables that do the work. For transactional Replication, you have three agents:
- Snapshot Agent – this is the process who does the snapshot to initialize Replication (assuming you aren’t initializing via another method. Most people use snapshots). There is one per publication. It lives on either the Publisher or the Distributor.
- Log Reader Agent – this is the process that reads the transaction log on the publisher, identifies changes, and writes those changes to the
distributiondatabase on your distributor. There is one per publication. It lives on either the Publisher or the Distributor. - Distribution Agent – this is the process that takes changes from your
distributiondatabase and pushes it to the subscriber. There is one per subscriber. It lives on either the Subscriber or the Distributor.
The standard setup is that the agents run as SQL Agent jobs. If you want to stop distributing commands to a subscriber, simply stop the jobs. You can find distribution agents based on the job category of REPL-Distribution. The job name will be composed to include publisher/subscriber info to help you identify the correct job to disable. Those values may be truncated when making the job name, so if you have multiple distributors, you may need to look at the distribution job step itself to be 100% sure you have identified the correct job.
Note you can also confirm the correct job by querying the distribution database–but I generally find it quicker and easier to look at the job itself.
answered 2019-02-12 by Andy Mallon