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

Next, the previous chapter

 

When the subscription end has not been connected for a long time and there are a large number of insert statements on the release end, let's take a look:

 

USE mysales_normalGOINSERT INTO [mysales_normal].[myinventory].[Vendor]SELECT addressid,substring(addressline1,1,20),substring(city,1,10),substring(city,1,10),substring(city,1,5),substring(city,1,10) FROM AdventureWorks.person.ADDRESSWHERE AddressID>10000

 

This statement is inserted with 18510 statements. Wait! You can find the unsent command from replication monitor:

 

SELECT count(*) FROM distribution.dbo.msrepl_commands (nolock) dmWHERE dm.publisher_database_id=7and article_id=8

 

 

If you run the [distribution Clean Up: distribution] job, there is no effect, because the command is not sent to the subscription end!

 

 

This is the second day, and it has been interrupted for more than 10 hours. After inserting insert tracer, we will find that the status of distributor to subscriber is pending, and there is one more command not sent ,:

After the named instance is started, the distributor immediately sends the suspended command to subscriber ,:

Let's take a look at the undistributed comands option in replication monitor:

Only the last insert tracer command is not cleared by the job [distribution Clean Up: distribution]. Check the related commands in msrepl_commmands:

Select count (*) from distribution. DBO. msrepl_commands (nolock) DM
Where DM. publisher_database_id = 7
And DM. article_id = 8

The result is 0.

Let's take a look at the commands that have not been cleared up and use the following script. In this case, the @ article_id parameter cannot be used.

exec distribution..sp_browsereplcmds@publisher_database_id=7

 

 

 

 

 

The subscriber table has the corresponding data!

 

Conclusion: 1: once again, it is proved that undistributed commands only displays the command of the corresponding release item in msrepl_commands, including the sent and unsent commands. This command number can only be used as an auxiliary judgment,

To prove this, You can manually pause the [distribution Clean Up: distribution] job, And the undistributed commands will remain unchanged.

2: [distribution Clean Up: distribution] only clears the sent command and runs it once every 10 minutes. The key judgment should be confirmed by [insert tracer, for details about this command, click here

3: In this test, Microsoft found this link and gave a summary conclusion in fqa for replication. The transcript is as follows:

Does replication resume if a connection is dropped

Yes. Replication processing resumes at the point at which it left off if a connection is dropped. If you are using merge replication over an unreliable network,

Consider using logical records, which ensures related changes are processed as a unit.

4: The following two sites are very good about copy and distribution. You can check them out frequently!

A: repltalk covers using and tuning SQL Replication

B: replicationanswers