Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Question about how to avoid a primary key violations on the subscriber on identity columns with NOT FOR REPLICATION

Answered Question about how to avoid a primary key violations on the subscriber on identity columns with NOT FOR REPLICATION

  • Saturday, November 17, 2012 2:11 PM
     
     
    Sign In to Vote
    0

    I have transactional replication with a non updateable subscription.

    I have a published table defined as:
    CREATE TABLE [dbo].[tablename](
     [tablenameID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
     .
     .
     .
     CONSTRAINT [PK__tablename] PRIMARY KEY CLUSTERED
    (
     [tablenameID] ASC  ..........

    potentially I have many tables defined in this manner.

    When I do a DR test and turn replication off by stopping the distributor I am finding the app gets a primary key violation when trying to insert into published table on the subscriber.  I have to manually intervene and reseed the identity column in order for the app to be able to insert into the table.

    My question is is there a way to configure sql server such that this manual intervention is not required for these types of tables when in case of a disaster the DR instance must be used in place of the regular production sql instance.

     

All Replies

  • Saturday, November 17, 2012 2:57 PM
    Moderator
     
     Answered
    Sign In to Vote
    0
    No, you will need to use non-overlapping ranges on the subscriber, so odd on the publisher, even on the subscriber, or in advance do a checkident to reseed your subscriber side tables to a value in the millions - or a value which the publisher will not hit during the lifetime of the application.

    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

     
  • Saturday, November 17, 2012 10:05 PM
     
     
    Sign In to Vote
    0

    Hi Jay,

    I faced the same issue few months ago. Reason for this is your IDENTITY COLUMN.

    with replication IDENTITY COLUMN IS not a best solutions. you look for other alternatives.

    The nature of the issue is:

    You insert one row on publisher that will insert a new identity value in that table.

    Query the table at subscriber, i am sure you able to see the inserted recorded by publisher.

    Now try to insert from subscriber. you will get error message. You execute again you will succussed in the second attempt.

    The reason for this in every attempt your identity value will be increased. AT subscriber database engine do not know how many values to be incremented since your table definition says IDENTITY (1,1), SO IT will increment whatever the value it is known by 1.

    Unfortunately this value is not the value inserted by the publisher. it is the value from subscriber local memory.

    Observe here:

    Say you inserted row with table name id = 1 at publisher

    Go to subscriber query the table. you will see that record

    Insert a row at subscriber!!!! Guess what you fail (database engine try to insert 1 not 2)

    Execute again database engine will be incremented by 1 so the new value will be 2. Hence you will succussed in inserting the new row.

    Same thing from publisher you get same error.

    Bottom line identity column is not best candidate while using replication; you must be careful while using this.

    Good luck

    Kumar

     
  • Monday, November 19, 2012 4:26 PM
     
     
    Sign In to Vote
    0

    What about re initializations?  Will my identity config on my subscriber be overwritten by what the identify config is on the publisher whenever there is a re init?

     
  • Monday, November 19, 2012 4:41 PM
    Moderator
     
     
    Sign In to Vote
    0
    With reinitializations which drop and recreate the tables you will need to do this.

    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

     
  • Tuesday, November 20, 2012 1:16 PM
     
     
    Sign In to Vote
    0

    Thanks Hilary and Kumar for your sharing.