August 15, 2012 at 5:51 am
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
August 15, 2012 at 6:43 am
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
August 15, 2012 at 6:49 am
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?
August 15, 2012 at 6:55 am
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.
August 15, 2012 at 7:04 am
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
August 15, 2012 at 7:16 am
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
August 15, 2012 at 7:28 am
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
August 15, 2012 at 7:52 am
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?
August 15, 2012 at 9:51 am
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
August 16, 2012 at 5:45 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply