How to handle large transaction operations in SQL Server transactional replication

How to handle large transaction operations in SQL Server transactional replication

Working mechanism of transactional replication

Transactional replication is implemented by the SQL Server Snapshot Agent, the Log Reader Agent, and the Distribution Agent. The Snapshot Agent prepares the snapshot file, which contains the schema and data for the published table and database objects, and then stores the files in the snapshot folder and records the synchronization jobs in the distribution database at the Distributor.

The Log Reader Agent monitors the transaction log for each database configured for transactional replication and copies the transactions marked for replication from the transaction log to the distribution database, and the distribution database acts as a reliable storage-forward queue. The Distribution Agent replicates the initial snapshot files in the snapshot folder and the transactions in the distribution database tables to the Subscriber.

Incremental changes made at the Publisher are directed to subscribers based on the Distribution Agent's schedule, and the Distribution Agent can run continuously to minimize latency or run at scheduled intervals. For push subscriptions, the Distribution Agent runs at the Distributor, and for pull subscriptions, the Distribution Agent runs at the Subscriber. The agent moves the transaction from the distribution database to the Subscriber. If the subscription is marked as requiring authentication, the Distribution Agent also checks to see if the data at the Publisher and Subscriber matches.

Synchronous delay processing method for large transaction

In transactional replication, it is often the case that data synchronization delays are encountered. Sometimes these delays are due to an update being performed in publication, such as Update TA set col=? Where?, this update contains a huge amount of data. on the subscription side, this update is decomposed into multiple commands (one command per row of data by default) applied to subscription. in case of necessity, we need to skip this big transaction and let replication continue to run.

Now introduce some of the principles and specific methods of transactional replication:

When the article of publication database is updated, a corresponding log is generated, and log reader reads the log messages and writes them to MSrepl_transactions and MSREPL of the distribution database. The _commands.

Each record in the MSrepl_transactions has a unique identifier that identifies the LSN in the xact_seqno,xact_seqno corresponding log. So it is possible to infer the order of their generation in publication database by xact_seqno, the generation time of the large number is late, and the generation time of the number is small.

Distributionagent contains two sub-processes, reader and writer. Reader is responsible for reading data from the distribution database, and writer is responsible for writing reader-read data to the subscription database.

Reader reads data from the distribution database (reading MSrepl_transactions tables and MSrepl_commands tables) through Sp_msget_repl_commands.

The approximate logic is that reader reads the Transaction_timestamp column of the MSreplication_subscriptions table of the subscription database, obtains the last LSN number of the update, It then reads data in the distribution database that is greater than this number. Writer writes the read data to the subscription and updates the Transaction_timestamp column of the MSreplication_subscriptions table. Reader will then continue to use the new LSN to read the subsequent data, and then pass it on to writer, and so forth.

If we update the Transaction_timestamp column manually, setting this value to the LSN of the large transaction that is currently executing, then the Distribution Agent will not read the large transaction, but will skip it.

Specific logic See:

SQL Server replication series 3– stored procedures Sp_msins_dbotablename_msrepl_ccs & SP_MSDEL_DBOTABLENAME_MSREPL_CCS role

How to skip a transaction in SQL Server replication series 4–transactional replication

DBA's recommendations

To minimize the impact, we recommend that you use a replicated stored procedure to encapsulate the update operation as a standalone transaction, invoke the replicated stored procedure at the Subscriber, and perform a batch update locally.

After the high-concurrency database has been deleted after the archive, in order to avoid the business impact, the deletion will be recycled in batches, waiting for a certain period of time between each batch. Here, we can also use control tables to control large transaction batching operations. Combine control logic with replicated stored procedures to increase batches and reduce execution time. This process can also work as well as non-replicated updates, with virtually no replacement of the exec SP operation with the actual update.

For specific scripts see:

Large Updates on replicated Tables

Deep optimization of the replication architecture

For multiple publishers, multiple publications, multiple subscriptions, we can optimize and scale from the schema. Separate each publisher from a distribution database and put it on a separate server to relieve the pressure on the Distribution Agent. For subscriptions, there is no need for real-time requirements, with pull subscriptions, minimizing the number of push subscriptions.

For subscription databases, you can configure the bulk mode to optimize log writes for batch operations. Create a lightweight subscription database to reduce unnecessary indexes and triggers. For pull subscriptions, modify the fetch interval. Increase the frequency of log backups. Configure a high-performance configuration file.

Copy optimization See:

SQL Server replication tips in minutes

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1905168

How to handle large transaction operations in SQL Server transactional replication