Server and subscriber content inconsistent solutions published replication
In the process of transactional replication, sometimes due to various reasons lead to the publisher and subscriber data inconsistencies, creates this kind of situation is often due to one of the following reasons:
- A Agent runtime error or Agent process crashes
- The larger issue is the use of backup and restore, and not the snapshot copy initialization, and released after the end to modify the data backup
- Non Distribution Agent thread to modify a subscriber data
The above three cases are the most common causes of release reason end and the subscriber data inconsistency, most of which third reasons are often appear, in this case, in general, can solve this problem by reinitializing the subscription, but for larger subscription, or between the publication and subscription are too far apart. The network broadband problem, will reinitialize the subscription is not so attractive proposal. Therefore, by comparing the data analysis tools to compare different data, and only update those and source does not synchronize the data is a better choice.
These tools include data comparison tools like Redgate and xSql, data comparison tools can also use the Visual Studio built-in, below I through a simple Demo to show how to solve this kind of problem.
At present, I have established a publisher and subscriber, the publisher CAREYSON-SQL released the AdventureWorks sample database SalesOrderDetail the whole table, the subscriber sqlazur\sql2012 subscribe to the table, as shown in Figure 1.
Fig. 1 basic copy information
At this point, I deleted at the subscriber data artificially, caused by the publisher and subscriber data inconsistent, as shown in Figure 2.
Figure 2 in subscriber manually delete the data, resulting in inconsistent
We come back to the server for validation subscription, as shown in Figure 3.
Figure 3 shows the subscription
You can see in Replication Monitor,, in a subscriber data leads to validate subscriptions not synchronous tips, as shown in Figure 4.
Fig. 4 subscription has not been synchronized
In order to correct this problem, we can use a database project Visual Studio to create data analysis task to find the missing data, according to the release end update subscription end, as shown in Figure 5.
Fig. 5 find deleted data
We then click " update the target ", is deleted data will be released to the subscription end end synchronization. As shown in Figure 6.
Fig. 6 the target database has been updated
Once again we validate subscriptions, appears to have been through a subscription, as shown in Figure 7.
Figure 7 update subscription end post, subscribe through verification
Posted by Candice at November 26, 2013 - 2:57 AM