We help IT Professionals succeed at work.

SQL Replication tablediff

Medium Priority
435 Views
Last Modified: 2013-02-10
Hi,

Second question of the day.. just discovered the tablediff utility. Nice..

I have used it to generate a script and it has done for my table that is out of sync on the subscriber by 2 records due to the subscriber server crashing.

If I run this script to insert the missing records at the subscriber will replication try to insert them at the publisher? And will this cause a problem?

Im guessing it will throw up a conflict.. but I can just answer the conflict with let loser win or something like that??

SQL Server 2008
Windows server 2008
Merge Replication

Thanks

Antony
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

Commented:
If I run this script to insert the missing records at the subscriber will replication try to insert them at the publisher? And will this cause a problem?
Yes and yes.

>>but I can just answer the conflict with let loser win or something like that??<<
No.  You need to Reinitialize the Subscription.

Author

Commented:
And what does re initialize the subscription do? Will it cause it to re download the entire database to the subscriber... as that would be a MAJOR problem for me given the DB is over 16GB!
CERTIFIED EXPERT
Top Expert 2012

Commented:
It will re-sync the differences using the current snapshot as the source, unless you re-initialize the snapshot, in which case yes, it will "download the entire database to the subscriber" as you put it.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
>> If I run this script to insert the missing records at the subscriber will replication try to insert them at the publisher? And will this cause a problem? <<

Yes.

Maybe.  Merge replication has conflict resolution potential, so it would depend on the exact settings and transactions.  In most cases, you can also direct SQL on how to resolve conflicts, again depending on the specific settings for the merge replication when you set it up.
CERTIFIED EXPERT
Top Expert 2012

Commented:
Oops, I missed that detail.  I am sorry, I thought this question was related to Transactional not Merge Replication.
I took the script that table diff created. I ran it at the Subscriber to insert the missing records..

...and Replication....

Did not complain or grumble it just continued working happily!!!

I also deleted some records at the Publisher that I couldnt insert at the subscriber, due to identity range management, and it again happily continued without a whimper or murmur!!

So there you go... non convergence issues use tablediff generate the script to insert the missing rows.. and insert them at the side that is missing the data.

Antony
CERTIFIED EXPERT
Top Expert 2012

Commented:
Did not complain or grumble it just continued working happily!!!
Yes, sorry about that.  It would have helped if I had read the question.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
>> Did not complain or grumble it just continued working happily!!! <<

That's why I said "maybe".  It depends on what the associated key values were, and the details of how merge replication was defined.


Glad it worked out for you!


Reinitializing a subscription is easy to specify, but it can take a long time for SQL to actually do it, and it usually generates a lot of db and/or transaction activity.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Good luck on future qs!!

Author

Commented:
Im sorry im not attempting to shun anyone here.. if you feel you should off had the points please say..

Author

Commented:
Although the other guys perhaps steered me, i took the plunge and tried my solution!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.