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:

 

    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.

 

DEMO

    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.

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.

  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.

    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.

  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.

    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.

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.

7

Figure 7 update subscription end post, subscribe through verification

Posted by Candice at November 26, 2013 - 2:57 AM