Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server replication and tablediff Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 5:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 5:51 AM
Points: 38, Visits: 94
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

Post #1345222
Posted Wednesday, August 15, 2012 6:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 1,361, Visits: 15,264
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
Post #1345234
Posted Wednesday, August 15, 2012 6:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 5:51 AM
Points: 38, Visits: 94
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

Post #1345238
Posted Wednesday, August 15, 2012 6:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 1,361, Visits: 15,264
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.
Post #1345242
Posted Wednesday, August 15, 2012 7:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 5:51 AM
Points: 38, Visits: 94
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. :)

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

Post #1345246
Posted Wednesday, August 15, 2012 7:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 1,361, Visits: 15,264
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
Post #1345260
Posted Wednesday, August 15, 2012 7:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 5:51 AM
Points: 38, Visits: 94
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

Post #1345265
Posted Wednesday, August 15, 2012 7:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 5:51 AM
Points: 38, Visits: 94
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

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

Post #1345275
Posted Wednesday, August 15, 2012 9:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 1,361, Visits: 15,264
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

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

Post #1345361
Posted Thursday, August 16, 2012 5:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 19, 2014 5:51 AM
Points: 38, Visits: 94
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

Post #1345864
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse