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?

Answer 1

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 distribution database 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 distribution database 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.

Answer 2

as @AMtwo described earlier, Just stop and disable the subscriber agent of the particular database to pause replication. To achieve this task you can do it by below step:

1. Browse sql server agent and disable the job.

To get the job name, query to distributor database with below command. Here you can get all agents installed information.

USE Distribution 
GO 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
-- Get the publication name based on article 
SELECT DISTINCT  
  srv.srvname publication_server  
, a.publisher_db 
, p.publication_id publication_ID 
, p.publication as publication_name 
, a.article_id as article_id 
, a.article as article_name
, a.destination_object 
, ss.srvname as subscription_server 
, s.subscriber_db  as Subscriber_DB
, da.id   AS distribution_agentID
, da.name AS distribution_agent_job_name 
, sa.id   AS SnapShot_agentID
, sa.name AS SnapShot_agentjob_name 
, la.id   AS logreader_agentID
, la.name AS logreader_agentjob_name 
FROM MSpublications p 
LEFT JOIN MSsubscriptions s ON p.publication_id = s.publication_id  
LEFT JOIN MSArticles a ON a.publication_id = p.publication_id 
LEFT JOIN master..sysservers ss ON s.subscriber_id = ss.srvid 
LEFT JOIN master..sysservers srv ON srv.srvid = p.publisher_id 
LEFT JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id AND da.subscriber_id = s.subscriber_id 
LEFT JOIN MSsnapshot_agents sa ON sa.publisher_id = p.publisher_id --AND sa.subscriber_id = s.subscriber_id
LEFT JOIN MSlogreader_agents la ON la.publisher_id = p.publisher_id --AND sa.subscriber_id = s.subscriber_id
--where A.article = 'STAGING_PL_BATCH'
ORDER BY 1,2,3