Tuesday, June 26, 2012 2:44 PM
I have a customer who is experiencing an issue that I am unsure how to resolve. We have three databases, the two downstream databases replicate up to a single database. We have the same schema across all three databases. When devices report into the downstream database, those records are replicated up.
What is happening is when the customer moves a device from one downstream database to the other, we start seeing primary key errors. For example, lets say A and B are the downstream databases, if a device reported into A, there is a record for that device in A that is replicated up. If we move that device to B, we see primary key errors. I can get the exact errors if necessary.
How does SQL replication handle this? I would expect if the record exists we would get an update, not an insert. Since you cannot have duplicate rows with the same ID, it appears as if database B replicating up is trying to insert instead of update, can that be true?
Tuesday, June 26, 2012 4:01 PMModerator
Add a column which is a originating server ID to the PKs and then give this column a default of @@Servername. This way you will not get these collisions. Another option is to use dbcc checkident to reseed the identity elements to ranges of identity values that will not collide.
For example on server a don't do anything. On server B set the identity values to start at 10,000,000. ON server C set the identity values to start at 20,000,000.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Proposed As Answer by amber zhangModerator Thursday, June 28, 2012 1:53 AM
Thursday, June 28, 2012 2:43 PMThat is a great suggestion but the problem is the ID column is actually a DeviceID that is used throughout the system for other things. Basically, I cannot change the ID. Is there not a way for replication to handle these collisions gracefully? There isn't a way when I setup the job to try updates prior to installs?