none
Updatable Subscriptions on SQL Server 2012

    Question

  • We have a primary web/database server (SQL Server 2012) which publishes data to our backup web/database server (also SQL Server 2012) subscriber using transaction replication. We want to be able to direct users to the backup server in case the primary server fails but writing to the subscription database directly from the website on the backup server throws a "cannot insert a duplicate key" error. I've read that this is by design and I've also read that "Updateable Subscriptions" was deprecated on SQL Server 2012. Since we cannot afford to purchase the Enterprise Edition, we are looking for an inexpensive/easy way to do this. I'm wondering if deleting the local subscription on the backup server would cause the identity field value to correct increment from the last value written during the replication. And I'm wondering if once the primary server is repaired if it would be possible to backup/restore the database from the backup server to the primary server. Any help would be appreciated.
    Thursday, October 11, 2018 10:25 PM

Answers

  • Hi SEMofWA,

     

    >>I'm wondering if deleting the local subscription on the backup server would cause the identity field value to correct increment from the last value written during the replication.

     

    Yes, the identity field value will still keep the increment.

     

    >>And I'm wondering if once the primary server is repaired if it would be possible to backup/restore the database from the backup server to the primary server.

     

    Replication supports restoring replicated databases to the same server and database from which the backup was created.

     

    So if you restore a backup of a replicated database to another server or database, replication settings cannot be preserved.

     

    You can restore a backup from backup server to the primary server and re-initialize replication.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by SEMofWA Friday, October 12, 2018 5:53 PM
    Friday, October 12, 2018 7:23 AM

All replies

  • Hi SEMofWA,

     

    >>I'm wondering if deleting the local subscription on the backup server would cause the identity field value to correct increment from the last value written during the replication.

     

    Yes, the identity field value will still keep the increment.

     

    >>And I'm wondering if once the primary server is repaired if it would be possible to backup/restore the database from the backup server to the primary server.

     

    Replication supports restoring replicated databases to the same server and database from which the backup was created.

     

    So if you restore a backup of a replicated database to another server or database, replication settings cannot be preserved.

     

    You can restore a backup from backup server to the primary server and re-initialize replication.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by SEMofWA Friday, October 12, 2018 5:53 PM
    Friday, October 12, 2018 7:23 AM
  • Thank you Emily!
    Friday, October 12, 2018 5:55 PM
  • Merge replication is likely a better fit here.  Updateable subscriptions is designed for situations where the majority of the changes occur on your publisher.

    Also think about using bi-directional transactional replication.

    Monday, October 15, 2018 1:38 PM
    Moderator