November 22, 2010 at 11:44 am
Hi,
I have setup a merge replication between 3 SQL Servers in 3 geographical locations, all linked over a VPN. I have problems with tables with identity columns. Rows from distributed database are replacing rows at subscribers with the same identity. In the conflit manager, I have message for each "lost" rows:
The row was inserted at 'FLOETC.production' but could not be inserted at 'PRO-SQL2000\VEGSQL.Production'. Violation of PRIMARY KEY constraint 'PK_TB_expExpeditionLot'. Cannot insert duplicate key in object 'TB_expExpeditionLot'.
After each time the merge agent runned, I have to reseed identity of tables in problems.
Identity columns of every tables are "not for replication".
what I'm doing wrong here??
thanks for your help
November 22, 2010 at 11:46 am
You have the identity column defined as a primary key. It can't insert the same value in a primary key column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 11:50 am
Yeah, both tables have PK as identity and are supposed to be identical, but replication isn't supposed to handle this itself with the "Not for replication" settings ??! Otherwise, how am I suppose to setup a merge replication between tables with PK as identity ??!
November 22, 2010 at 12:14 pm
That's why GUIDs are recommended for merge replication.
"Not for replication" will allow replicated rows to be inserted into an identity column, much the same as "set identity_insert on" will. That still won't allow you to insert duplicate values into the primary key.
There are a couple of ways around that.
The simplest is to make sure each instance of the database will have a different range of values, using the seed and increment settings for the ID column. That has limitations in terms of making sure they will NEVER colide, and making sure any new instance is accounted for correctly. E.g.: If you have three instances of the database, and you want to leave room for a possible fourth, you could set one instance to use odd positive numbers, one to use even positive numbers, and the third to use odd negative numbers, leaving room for even negative numbers.
Potentially more effective is to use a two-column primary key, with each instance of the database being assigned a different value in the second column. E.g.: The second column could be the server's name or IP address. Again, this can cause problems if a name or IP address changes, or if a new instance is set up without being correctly modified to insert a different value in that column, or if two servers end up with the same name, or the same IP address on a different subnet. Very effective, requires some careful administration.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 1:13 pm
thanks for the very informative reply. I can't change de INT identity to GUID, because it would be way too much rework, but I could change the tables seed and increment...
I guess there is no way I can do that without dropping subscriptions ? If I drop the publication, do I have to apply a new snapshot to the subscribers? because new data will be added on the subscribers DB while I'm doing the change in the published database...
November 22, 2010 at 1:19 pm
You'll probably need to at least pause and restart replication. You might need to drop and re-create subscriptions/publications. It'll be one or the other, most likely.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 1:29 pm
While I'm doing the changes in the databases on 3 servers (on the seed/increment of each table with an Identity PK), data will be added on those databases. Production almost never stop. When I'll setup replication again, it will want to apply a snapshot. If I do this, all subscribers will lose all unique data and will be replaced by the publisher's snapshot. Am I right? So... how can I setup the replication without loosing anything on the 2 subscribers? How can I make the 3 databases "mirror" before setting up the replication??
thanks
November 22, 2010 at 1:34 pm
Dominic,
I'm sorry that this is an untested recommendation, but I believe DBCC CHECKIDENT can be used to change the seed on a replicated table if the identity is set for not for replication on all servers, without resetting the snapshot.
My current location doesn't allow enough sysadmin access for me to test that for you. If you do that, however, you'll need to locate your problem identities in all the mergeable systems, get them to a temp table, delete them, and reinsert them to workaround the existing PK issue. That's a manual job.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 22, 2010 at 1:50 pm
thanks for the reply. I think you're right on that, as I already did it a few time. that was a stupid question!
anyway, actually, I have disabled the replication (uncheck the Enable checkbox in the Merge Agent job) until I could figure a solution to my problem. So, for 6 hours, data have been added to the 3 servers, without being replicated. So even if I change the tables seed/increment now, those data will still be in conflicts and will be dropped by the replication and put into the conflict table. So, how can I "restore" those data and apply them to the other servers?
November 22, 2010 at 2:10 pm
New snapshots.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 2:17 pm
new snapshot from the publisher, applied to the subscriber, will replace existing data by the publisher's data, am I right? How can I do otherwise?
sorry if questions seems basic, I'm no DBA and quite new at replication setup.
thanks for your understanding and help.
November 22, 2010 at 2:20 pm
Unfortunately, I'm not particularly familiar with merge replication. I'm more accustomed to either transactional or snapshot. Can't really help you on that point.
If it were me, I'd read up on merge replication. There have got to be ways to re-initialize it without wiping out data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 2:28 pm
thanks you very much for your help. I'll certainly go read on that subject.
take care.
November 22, 2010 at 2:37 pm
Dominic Gagné (11/22/2010)
new snapshot from the publisher, applied to the subscriber, will replace existing data by the publisher's data, am I right? How can I do otherwise?sorry if questions seems basic, I'm no DBA and quite new at replication setup.
thanks for your understanding and help.
Yes, new snapshot removes existing data.
The primary issue you have is that your different sources have used the same key. You need to fix this *first*. Which means, you have to get all your sources into a single table somewhere, identify the duplications, and then renumber the duplications on the non-source system into their new seed values. So, you'll find the dups, drop them to a server specific table, bring that back to that server, delete the old inserts, reinsert them so they get newly seeded, and then update all subordinate data that references the old IDs.
Then, I believe, you can reactivate your merge subscriptions.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 23, 2010 at 6:21 am
thanks for the hints. I was figuring something like that to "rescue" the data on subscribers. that'll be a pain, but, no pain no gain! 🙂
thanks all for your precious help
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply