locked
Transactional Replication - Subscriber Identity Seed? RRS feed

  • Question

  • Hey

    Hoping someone can put a bit light on this for me and help me out, I have a single Publisher and single Subscriber set-up on Transactional Replication with continuous updating.

    But I want the scenario where, if the Publisher fails then the Subscriber can take over as the main database
    Then the Subscriber DB will be restored back and used as the main publisher again once the main server is back up.

    The problem I have is the Subscriber database does not retain its identity seed against the Primary Key Columns on any table and it appears to be reset to (1)
    Therefore creating new records, creates a Primary Key Conflict.

    Is this because of the "NOT FOR REPLICATION" setting,
    If I want to let the Subscriber continue the increment the identity ID should this be set to YES or NO

    Thank you,
    Barry


    • Edited by paullyie Monday, February 22, 2016 3:59 PM
    Monday, February 22, 2016 3:45 PM

Answers

  • You will need to reseed the tables restored on the publisher. For example, determine the highest value of the identity column and do this, assuming the column value is 222222

    dbcc checkident('TableName',Reseed,222223)

    However, you should not have to restore the subscriber database back on the publisher. If the publisher goes offline, the subscriber should be failed over to queued updating where the changes will be queued on the subscriber, until the publisher comes back online. Then the queue will replay the changes originating at the subscriber back to the publisher. 

    This is documented here.

    https://technet.microsoft.com/en-us/library/ms152474(v=sql.110).aspx


    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

    Wednesday, March 2, 2016 1:39 AM
    Answerer

All replies