SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server replication and tablediff


SQL Server replication and tablediff

Author
Message
Antony Symonds
Antony Symonds
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 98
Hi All,

So my subscriber server had a crash the other day...

The result of which meant that some row inserts didnt get replicated to the subscriber...

Replication is working fine going forward.. but I have these missing rows..

example : tbl_asn @ subscriber has 400 records tbl_asn at publisher has 402.

I have run tablediff and generated a script(see at end of message)

If I run this at the subscriber(where it is missing) I assume it will attempt to replicate this back to the publisher(where it exists)

My question is : Will this cause replication to fall over or will it just go "oh well its there, these arent the records you're looking for, go about your business, move along" and be happy?

Thanks

Antony


SET IDENTITY_INSERT [dbo].[tbl_asn] ON
INSERT INTO [dbo].[tbl_asn] ([asn_bulkOrder],[asn_busyFlag],[asn_Count],[asn_date],[asn_guid],[asn_id],[asn_order],[asn_Sent],[rowguid]) VALUES (1370218,0,1,N'2012-08-14 12:34:22.677','Null',112949,8002925,0,'a1f2f2f7-03e6-e111-a20f-001e4f2031f5')
INSERT INTO [dbo].[tbl_asn] ([asn_bulkOrder],[asn_busyFlag],[asn_Count],[asn_date],[asn_guid],[asn_id],[asn_order],[asn_Sent],[rowguid]) VALUES (1371103,0,1,N'2012-08-14 12:34:23.350','Null',112951,8002927,0,'3b67edfd-03e6-e111-a20f-001e4f2031f5')
SET IDENTITY_INSERT [dbo].[tbl_asn] OFF



Time to make a change


MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5067 Visits: 15346
Antony Symonds (8/15/2012)
Hi All,

So my subscriber server had a crash the other day...

The result of which meant that some row inserts didnt get replicated to the subscriber...

Replication is working fine going forward.. but I have these missing rows..

example : tbl_asn @ subscriber has 400 records tbl_asn at publisher has 402.

I have run tablediff and generated a script(see at end of message)

If I run this at the subscriber(where it is missing) I assume it will attempt to replicate this back to the publisher(where it exists)

My question is : Will this cause replication to fall over or will it just go "oh well its there, these arent the records you're looking for, go about your business, move along" and be happy?

Thanks

Antony


SET IDENTITY_INSERT [dbo].[tbl_asn] ON
INSERT INTO [dbo].[tbl_asn] ([asn_bulkOrder],[asn_busyFlag],[asn_Count],[asn_date],[asn_guid],[asn_id],[asn_order],[asn_Sent],[rowguid]) VALUES (1370218,0,1,N'2012-08-14 12:34:22.677','Null',112949,8002925,0,'a1f2f2f7-03e6-e111-a20f-001e4f2031f5')
INSERT INTO [dbo].[tbl_asn] ([asn_bulkOrder],[asn_busyFlag],[asn_Count],[asn_date],[asn_guid],[asn_id],[asn_order],[asn_Sent],[rowguid]) VALUES (1371103,0,1,N'2012-08-14 12:34:23.350','Null',112951,8002927,0,'3b67edfd-03e6-e111-a20f-001e4f2031f5')
SET IDENTITY_INSERT [dbo].[tbl_asn] OFF



You have potential to get an error if you dont run the script on the subscriber and someone deletes/updates those rows on the publisher. You can do what you like on a subscriber and wont get an error until a command gets issued from the publisher that relates to those rows
Antony Symonds
Antony Symonds
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 98
Im planning on running the insert on the subscriber... it will then I assume try to send that insert to the publisher and find it there..

Will this just cause a conflict that I can fix or will it mess with replication too much?

Time to make a change


MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5067 Visits: 15346
Antony Symonds (8/15/2012)
Im planning on running the insert on the subscriber... it will then I assume try to send that insert to the publisher and find it there..

Will this just cause a conflict that I can fix or will it mess with replication too much?


You dont mention what replication this is so I've made the assumption (possibly incorrect) that this is one way, transactional as this generally the most common. If it is then it is one way and no it wont.

If its merge you can insert on the subscriber and resolve the conflict in conflict manager.
Antony Symonds
Antony Symonds
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 98
MysteryJimbo (8/15/2012)
Antony Symonds (8/15/2012)
Im planning on running the insert on the subscriber... it will then I assume try to send that insert to the publisher and find it there..

Will this just cause a conflict that I can fix or will it mess with replication too much?


You dont mention what replication this is so I've made the assumption (possibly incorrect) that this is one way, transactional as this generally the most common. If it is then it is one way and no it wont.

If its merge you can insert on the subscriber and resolve the conflict in conflict manager.


Hi,

Sorry I must of missed saying it was a Merge Replication... do apologise. Smile

So yes its a merge replication... and tablediff has generated the insert commands to re insert the data with identity insert ON so it would mirror precisely what it says in the table at the publisher...

The database is very big and I would not be able to re replicate it from scratch... that is my nightmare scenario.. long distance to the publisher server and we have poor speed lines due to being out in the middle of nowhere.

So you would say it should be ok and I jsut need to fix the conflicts?

Thanks

Antony

Time to make a change


MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5067 Visits: 15346
Yes. As you would run the script via a query session the inserts would be picked up and cause a conflict. It shouldnt be an issue to resolve as the values should be identical
Antony Symonds
Antony Symonds
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 98
Thank you mate for that info.. i took the lpunge and ran the insert and ... sadly i got this error

Conversion failed when converting from a character string to uniqueidentifier.

So tablediff generated this script but it wont work... any ideas?

Thanks

Antony

Time to make a change


Antony Symonds
Antony Symonds
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 98
Would seem I had an empty guid that i never used.. and I can get round that no problem... but I have hit another snag Sad

When I try and insert the value I get

The INSERT statement conflicted with the CHECK constraint "repl_identity_range_D15CCC90_AB85_424C_8043_093A8B1625FF". The conflict occurred in database "GSPRO", table "dbo.tbl_asn", column 'asn_id'.

Due to managed identity range.. is there anyway to get round this?

Time to make a change


MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5067 Visits: 15346
Antony Symonds (8/15/2012)
Would seem I had an empty guid that i never used.. and I can get round that no problem... but I have hit another snag Sad

When I try and insert the value I get

The INSERT statement conflicted with the CHECK constraint "repl_identity_range_D15CCC90_AB85_424C_8043_093A8B1625FF". The conflict occurred in database "GSPRO", table "dbo.tbl_asn", column 'asn_id'.

Due to managed identity range.. is there anyway to get round this?


Its an automatically created constraint, created by replication to reduce the risk of duplicate identities across publisher and subscribers. If these rows are meant to be there and are missing then you will be safe to disable the constraint, insert the data and re-enable it.

--disable the  constraint 
ALTER TABLE dbo.tbl_asn NOCHECK CONSTRAINT repl_identity_range_D15CCC90_AB85_424C_8043_093A8B1625FF
--do something
--enable the constraint
ALTER TABLE dbo.tbl_asn CHECK CONSTRAINT repl_identity_range_D15CCC90_AB85_424C_8043_093A8B1625FF


Antony Symonds
Antony Symonds
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 98
Hey,

Thanks loads for your help with this.

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!!!

Before seeing your last reply about switching off the check constraint i decided to delete the records from that table(the rest of the tables were manual identity ranges) at the Publisher that I couldnt insert at the subscriber 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.

All working as hoped without any problems.

Thanks again,

Antony

Time to make a change


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search