Processing after the subscription end goes down in the replication topology of SQL server ..... (1)

Prerequisites: This section describes SQL Server 2008r2. Other versions are not tested. The replication types are mainly transaction replication and P2P replication.

 

Whether it is high availability, high scalability, or high performance, replication and distribution is a good option, the configuration is relatively easy, and there are few changes to the foreground program, so it is widely used, however, after routine maintenance, troubleshooting is troublesome,

The principle of replication and distribution can be achieved only after a considerable understanding of the metadata table. If you do not know the principle, you will be overwhelmed. The omnipotent solution is to reinitialize the metadata. This article tests whether the subscription end is suddenly down or shut down manually,

The Publishing Database is still executing insert/update constantly, causing the link to be suspended. The processing method after the subscription end is reconnected is as follows. In this test, the subscriber has short-term and long-term interruptions.

 

Publication Database: mysales_normal

Publication: pub_mysales_normal

Publication type: Transaction replication/peer-to-peer Replication

Sub‑database: mysales_normal

One is the default instance, and the other is the command instance. The table involved in the replication and distribution is vendor, as shown in:

 

 

 

 

 

 

 

 

 

 

First, I disable the command instance service and agent, and then insert two records to the publishing end's vendor table.

 

USE mysales_normalGOINSERT INTO [mysales_normal].[myinventory].[Vendor]VALUES(1,'peter','beishangguang','shanghai','40034','13458294')INSERT INTO [mysales_normal].[myinventory].[Vendor]VALUES(2,'top','yyyyyyyy','guangzhou','40034','13458294')GO

In this case, the logreader agent reads two log files from mysales_normal and writes them to distribution... msrepl_commands. The distribution agent reads commands from this table.

Then, write the data to the corresponding table in the subscription, but the connection is interrupted. We can see the error information from the replication monitor.

 

 

We can use sp_browereplcmds to read the command in the msrepl_commands table:

SELECT mt.publisher_db,id,article,article_id FROM distribution.dbo.MSpublisher_databases md INNER JOIN distribution.dbo.msarticles mtON mt.publisher_db=md.publisher_dbWHERE mt.publisher_db='mysales_normal'exec distribution..sp_browsereplcmds@publisher_database_id=7, @article_id=8

The two commands are already in the msrepl_commands table. In replication monitor, you can also see that two commands are not transferred to distributior:

 

At this time, we restart the named instance service to see if these two commands can be uploaded to the subscription end:

We found that the two commands have been sent to the subscription end to check the data in the subscription table:

USE mysales_normalGOSELECT *  FROM [mysales_normal].[myinventory].[Vendor]

If data already exists, it indicates that the data has been written to the subscription table. However, when the command is not sent in replication Monitor, the undistributed commands is still 2 ,:

According to the description on the BOL: Undistributed commands is the number of commands that have not been sent to the subscription server.

The number of commands in the distribution database that have not been delivered to the selected subscriber.

A command consists of one Transact-SQL data manipulation language (DML) statement or one Data Definition Language (DDL) statement.

In this case, insert tracer in replication monitor and find that the link is accessible, while msrepl_commands contains two commands.

exec distribution..sp_browsereplcmds@publisher_database_id=7, @article_id=8 

 

Who cleared these useless commands? This is the turn of the distribution Clean Up: Distribution job, which runs every 10 minutes and clears the commands that have been written to the subscription end.

Run it once manually. Check the result and find that sp_browereplcmds returns an empty result set. However, msrepl_commands must keep at least one record for the replication Monitor Tool ,:

exec distribution..sp_browsereplcmds  @publisher_database_id=7

You can manually delete a row of data in msrepl_commands,

DELETE  FROM distribution.dbo.msrepl_commands WHERE publisher_database_id=7

Conclusion: 1: In replication monitor, undistributed commands cannot reflect unsent commands in real time. It only shows the number of records corresponding to the release item in msrepl_commands. Distribution Clean Up: Distribution

This job runs every 10 minutes, so undistributed commands lags behind for 10 minutes. We can only use insert tracer to monitor whether the current link is smooth in real time. If not, you can roughly determine how many commands are suspended!

2: After the subscription end is re-connected, the Publishing Server automatically delivers the suspended command to the subscription end without manual intervention!