Transaction replication arch

Transcation replication is setup to replicate transcational data ( insert/update/delete) to another server located either in same data center or geographically diffirent datacenter. This is mainly done to achieve source and target database to be in synch.

We cannot consider this as a DR startegy as the server or db does not failover but may be somewhat HA as the most of the data is available on the target server.

Bread crumbs of replication -

1. Publisher - Source server / database

2. Article - source objects ( tables/procs) participate in the replication

3. Subscriber - Target server /database

4. Agents- Helps in moving the data from source to target

5. Reader and writer threads for each agent - helps reading and writing the data. Agents are:

snapshot agent , log reader agent & distributor agent

Snapshot agent - Creates the snapshot of the published articles. Snapshot consists of the article’s schema generation scripts (.sch), BCP’ed out data of each published article (.bcp), index generation scripts (.idx) etc to the snapshot folder based on the articles and objects selected. Writes the information about snapshot generation and completion in MSRepl_transactions table in the distribution database on the Distributor Server.

Log reader agent - While the Snapshot agent is running, Log Reader Agent copies the transactions (marked for replication) from the publication database’s transaction log file to MSRepl_transactions and MSRepl_commands table in the distribution database.

Distribution Agent- Once Snapshot generation is completed, Distribution Agent applies the Snapshot on the subscription database Distribution Agent then reads the transactions and commands from MSRepl_Transactions and MSRepl_commands table in distribution database and applies them on the subscription database

Functions of threads in the agent -

Log Reader Reader thread - is reading the Transaction Log via stored procedure sp_replcmds, a wrapper for xp_replcmds. It scans the transaction log for transactions marked for replication, skipping not replicated transactions.

Log Reader Writer thread - writes queued transaction from the Reader thread into the Distribution database using sp_MSadd_replcmds.

Distribution Reader thread - executes sp_MSget_repl_commands query to retrieve pending commands from the Distribution database and storing in an internal queue.

Distribution Writer thread - writing queue commands to the Subscriber via parameterized stored procedures prefixed with sp_MSupd..., sp_MSins..., sp_MSdel... to apply individual row changes to each article at the subscriber.

For subsequent runs.......

Log Reader Agent copies the incremental transactions (marked for replication) from the publication database’s transaction log file to MSRepl_transactions and MSRepl_commands table in the distribution database.

Distribution Agent reads the transactions and commands from MSRepl_Transactions and MSRepl_commands table in distribution database and applies them on the subscription database