Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
Replication and primary key issues

Proposed Answer Replication and primary key issues

  • Tuesday, June 26, 2012 2:44 PM
     
     
    Sign In to Vote
    0
     

    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?

     

All Replies

  • Tuesday, June 26, 2012 4:01 PM
    Moderator
     
     Proposed Answer
    Sign In to Vote
    0

    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

     
  • Thursday, June 28, 2012 2:43 PM
     
     
    Sign In to Vote
    0
    That 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?