1

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.

  • What transactional replication configuration are you using: standard transactional publication, transactional publication with updatable subscriptions, peer-to-peer transactional replication, or bidirectional transactional replication? – John Eisbrener Aug 16 at 14:09
1
+50

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.

  • "What do you mean cannot make use of indexes?" - The report builder generates queries based on user-defined column filters, so there are too many combinations to create ideal composite indexes. – cloudsafe Aug 19 at 10:34
1

Long running and locking queries on subscriber databases wouldn't typically cause performance issues on the publisher. The distributer can be run separately if you are worried about them affecting the performance of your SQL server.

The Log Reader Agent will take the changes from the Publisher and store them in the distribution database. The Distribution Agent will attempt to apply these to the Subscriber. If the subscriber is busy or tables are locked it will wait for them to be available or timeout and keep retrying on the schedule defined during setup.

You can view how many unreplicated commands exist and how far behind your subscriber is from the Publisher by using the Replication Monitor or querying the sp_browsereplcmds procedure.

For more info on the agents, and the settings you can change, take a look at this Microsoft docs page.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.