SQL Server – Do Long-Running and Locking Queries on Subscriber Databases Affect Publisher DBs?

replicationsql serversql-server-2016transactional-replication

One of our servers has a subscriber database. I've not used replication before and cannot find information on how the mechanism works.
Does the publisher have to wait on the replication completing before transactions are committed or does it make use of a queue or the transaction log before applying changes to the subscriber?
The subscriber is used as the back-end for a custom report builder (amongst other uses) which cannot make good use of indexes so tables are frequently locked for several minutes. I am concerned for any effects this might have on our publisher, which is on our live production server.
Both publisher and subscriber are in Full Recovery.

Best Answer

One of our servers has a subscriber database. I've not used replication before and cannot find information on how the mechanism works. Does the publisher have to wait on the replication completing before transactions are committed or does it make use of a queue or the transaction log before applying changes to the subscriber?

You haven't specified what type of replication, so I will assume Transactional Replication. The basic process for Trans Replication (assuming the subscriptions are already initialised) is as follows:

  1. The Log Reader Agent on your Distributor connects to the Publisher and reads the transaction logs for any transactions marked FOR REPLICATION.
  2. The transactions are then broken down into commands in stored in the distribution database.
  3. The Distribution Agent reads through the tables in the distribution database and applies the commands to the subscriber.

There are some slight variations depending on your configuration, but this is essentially the process. The publisher does not wait for the subscriber or distributor to acknowledge receipt of transactions. The only real impact is that SQL cannot clear inactive portions of the log file if the file has log records marked FOR REPLICATION that the Log Reader Agent has not yet replicated to distribution.

I am concerned for any effects this might have on our publisher, which is on our live production server. Both publisher and subscriber are in Full Recovery.

There shouldn't be any adverse effect on the Publisher during normal operations. There can be substantial impact if you have to reinitialise your subscriber(s) as a new snapshot has to be taken and this can increase load on your server and does require some schema locks during snapshot generation.

If your Distributor is the same server as your Publisher, then you may see performance impacts in general due to increased activity on the server, but typically this isn't substantial. You can also mitigate that by using a remote Distributor instead.

The subscriber is used as the back-end for a custom report builder (amongst other uses) which cannot make good use of indexes so tables are frequently locked for several minutes.

What do you mean cannot make use of indexes? Do you mean the indexes on the subscriber tables are not suitable for the reports you're running? If so, I would suggest not replicating the non-clustered indexes and creating indexes that are suitable on the subscriber. Your non-clustered indexes do not have to be replicated, which is one of the advantages of Replication for reporting purposes, so you can target your indexing strategy on the Subscriber at reporting rather than operational access like your Publisher.

Related Question