Maintaining multiple database servers at multiple sites in active mode and closely synchronizing copies of the data on all servers is a challenge for any DBA. But as long as you can tolerate a little latency, one good option for keeping your data current at all locations is to use SQL Server’s transactional replication with queued updates (TRQU). In the first article in this series, “Queuing Up,” December 2003, InstantDoc 40567, I showed how to set up TRQU. In this article, I look at how SQL Server replicates data from Publisher to Subscriber and from Subscriber to Publisher and describe how SQL Server resolves data conflicts. And, for information about TRQU security, see the sidebar “Login Security for TRQU,” InstantDoc ID 42824.
Modifying the Database Schema
Setting up TRQU modifies the database schema at the Publisher and the Subscriber and adds jobs at the Distributor. Knowing the details of these modifications will help you understand the entire TRQU process.
Modifications at the Publisher. For this article’s illustration, I set up the Authors table in the Pubs database as part of the pubs publication. For each table involved in the replication, the TRQU setup process adds the msrepl_tran_version uniqueidentifier column and the sp_MSsync_upd_trig_Authors UPDATE trigger to the Authors table. In the Publisher database, TRQU adds the sp_MSSync_ins_Authors, sp_MSSync_del_Authors, sp_MSSync_upd_Authors, and sp_MScft_pubs_Authors stored procedures and the conflict_pubs_Authors conflict table.
Modifications at the Subscriber. In the subscribing database (the Northwind database in this scenario), the TRQU process adds the msrepl_tran_version uniqueidentifier column to each table that receives and sends data to the corresponding table at the Publisher—in this example, a table that has the same name and structure as the Authors table. For each replicated table, the setup process also adds to the subscribing database:
- The conflict_pubs_Authors conflict table
- The MSReplication_objects, MSReplication_queue, MSReplication_subscription, MSSubscription_agents, MSSubscription_articles, and MSSubscription_properties system tables
- The trg_MSSync_upd_Authors, trg_MSSync_ins_Authors, and trg_MSSync_del_Authors triggers
- The sp_MS_ins_Authors, sp_MS_del_Authors, and sp_MS_upd_Authors stored procedures
Modifications at the Distributor. The TRQU setup process adds four jobs at the Distributor. The Snapshot Agent job creates the snapshot in the repl_snapshot job category. The Log Reader Agent job reads the publishing database log and writes to a staging table on the Distributor in the repl_logreader job category. The Distribution Agent job applies the changes from the staging table on the Distributor to the destination table on the Subscriber in the repl_distribution job category. And the Queue Reader Agent job, in the repl_queuereader job category, reads the queue at the Subscriber and applies those changes to the Publisher.
Updating the Data
The setup process creates other jobs such as history cleanup, but these jobs aren’t relevant to this article’s discussion. So, now let’s look under the hood and determine what happens when TRQU updates data.
Changes at the Publisher. Insertions, deletions, and updates take place in the publishing table. An UPDATE statement, for example, fires the sp_MSSync_upd_trig_Authors trigger. This trigger updates a row’s msrepl_tran_version column with a new globally unique identifier (GUID) value. The new GUID distinguishes the row from its previous version. SQL Server uses this GUID value to detect data conflicts by comparing the row versions at the Publisher and the Subscriber.
Next, in the Publisher database log, SQL Server marks an entry for replication. The Log Reader job runs on a set schedule on the Distributor, connects to the Publisher, reads from the Publisher log the transactions that are marked for replication, then moves commands for those marked transactions to the msrepl_commands table in the distribution database on the Distributor. Finally, the Distribution Agent job runs according to the set schedule on the Distributor (for a push subscription), picks up the pending commands from the Distributor’s msrepl_commands table, then applies them to the corresponding table at the Subscriber.
To insert, delete, or update rows in the destination table, the Distribution Agent job typically executes the sp_MS_ins_Authors, sp_MS_del_Authors, and sp_MS_upd_Authors stored procedures in the subscribing database. These stored procedures contain INSERT, DELETE, and UPDATE statements. Microsoft documentation says that you can append custom code to these stored procedures if your business rules warrant the addition. Note that when the Distribution Agent applies the commands to the table, the triggers on the destination table at the Subscriber don’t fire because the triggers’ code contains a NOT FOR REPLICATION declaration.
Changes at the Subscriber. Next, insertions, deletions, and updates take place in the subscribing table. SQL Server takes four steps in applying these commands. First, SQL Server fires the trigger that corresponds to one of three commands: trg_MSSync_ins_Authors, trg_MSSync_del_Authors, or trg_MSSync_upd_Authors. Next, each trigger captures the appropriate change and writes it to the MSReplication_queue table. Then, as the trigger carries out these actions, it captures the row’s before and after states with the GUID value of the corresponding row versions. For example, the trigger captures an after state for an INSERT, a before state for a DELETE, and before and after states for an UPDATE. Finally, the Queue Reader job runs at the Distributor on a defined schedule, connects to the Subscriber, picks up the transactions from the MSReplication_queue table, connects to the Publisher, then applies the changes to the Publisher. The job applies these changes by using the appropriate stored procedure in the publishing database: sp_MSSync_ins_Authors, sp_MSSync_del_Authors, or sp_MSSync_upd_Authors.
Prev. page
[1]
2
3
next page 
