Snapshot in transaction Replication

In transaction replication, the snapshotSnapshotagent reads the article information and places the content and scripts of the article in the snapshot folder. Then, the distributionagent reads these snapshot files, transfers them to the subscription, and completes initialization. During this period, the distributionagent needs to handle many tasks, such as determining whether a snapshot is available.

In transaction replication, the snapshot Snapshotagent reads the article information and places the content and scripts of the article in the snapshot folder. Then, the distributionagent reads these snapshot files, transfers them to the subscription, and completes initialization. During this period, the distributionagent needs to handle many tasks, such as determining whether a snapshot is available.

Snapshot in transaction Replication

The Snapshotagent reads the article information, saves the record-free space, and places the content and scripts of the article in the snapshot folder. Then, the distributionagent reads the snapshot files, transfers them to the subscription, and completes initialization. During this period, the distributionagent needs to handle a lot of things, such as determining whether the snapshot is available, applying those snapshot files, and what to do when the transmission is interrupted. These must be coordinated by the distributionagent. After reading this article, you will understand these processing methods and help you better judge the current transaction replication status and troubleshoot errors.

Before that, I would like to briefly introduce how distributionagent works:

The Distributionagent contains two processes: the Hong Kong server, reader, and writer. Reader reads data from the distribution database, and Writer writes the data read by reader to the subscription database.

Reader reads data from the distribution database (read the Msrepl_transactions table and Msrepl_Commands table) through sp_MSget_repl_commands.

The following is the parameter definition of sp_MSget_repl_commands.

CREATEPROCEDUREsys. sp_MSget_repl_commands

(

@ Agent_id int,

@ Last_xact_seqno varbinary (16 ),

@ Get_count tinyint = 0, -- 0 = no count, 1 = cmd and tran (legacy), 2 = cmd only

@ Compatibility_level int = 7000000,

@ Subdb_version int = 0,

@ Read_query_size int =-1

)

This stored procedure has six parameters. In Transactionalreplication, only the first four parameters are used (and the values of the third and fourth parameters are fixed. 0 and 10000000 respectively ). The following is an example:

@ Agent_id indicates distributionagentid. Each subscription has a separate distributionagent to process data. After @ agent_id is included, you can find the corresponding publication and all articles of the subscription.

@ Last_xact_seqno indicates the last LSN passed to the subscription.

The general logic is that Reader reads data with LSN greater than @ last_xact_seqno in the distribution database. Writer writes the read data to the subscription and updates the subscribed LSN. (transaction_timestamp column in The MSreplication_subscriptions table ). Then, Reader will continue to use the new LSN to read the subsequent data and pass it to Writer. During the process of sp_MSget_repl_commands, the data in the Msrepl_Commands table (identified by the type column) is roughly divided into two types: data generated by 1 snapshot, and data generated by 2 normal updates.

Now that the basic knowledge is introduced, let's start with the topic

How to determine whether a snapshot is available

Distributionagent uses the Stored Procedure sp_MSsubscription_status (exec sp_MSsubscription_status @ agendId) to determine the status of the current snapshot:

If sync_method is native, the status value is directly updated to 2,

How to Apply snapshot files

Snapshot will generate Snapshot files and virtual hosts in the Snapshot folder or alt_snapshot_folder. How does the distribution agent find and apply these files?

In the MSrepl_commands table, the following is the query of the two tables after snapshot is generated.

So what does this content mean? We can view it through sp_browsereplcmds,

Usedistribution

Go

Execsp_browsereplcmds '0x0000001e0000008f001e ', '0x0000001e0000008f001e '.

The table contains the name and path of the snapshot file. Then, the distribution agent applies snapshot to the subscription database based on the information.

How to determine whether a snapshot is required

In sp_MSget_repl_commands:

When the first Initialization is performed, the subscription database's LSN (MSreplication_subscriptions table's transaction_timestamp) is smaller than the snapshot file's LSN. In this way, all lsns larger than the subscription (including snapshots) data will be synchronized to the subscription.

Next there is a problem. If a new subscription database is added to the release, the New subscription database requires the distribution agent to complete the snapshot initialization process. Therefore, a new snapshot is generated. The xact_seqn of these snapshots must be greater than the existing subscription xact_seqno. However, you do not need to apply these snapshots to existing subscription databases. How does the distribution agent handle this situation?