Post reply

Replicate a Table Receiving Inserts From a Publisher and Replicate Back

  • January 16, 2020 at 4:16 pm

    #3716032

    Untitled Diagram (1)

    Let me quickly explain my situation and I've also included a diagram.  My company is in the process of migrating to a better and sound high availability design but we need to have a temporary solution for the design we have currently.

    For redundancy, we have a transactional replication setup from Primary to Subscriber.  We have an app calling a stored proc that inserts data to various tables including TableC.  Without going into detail and the various optimization already implemented, updating/deleting from TableC on Primary started to cause performance issues; as a result, we've decided to move this process to the Subscriber.  Basically, inserts to TableC will still happen on Primary but updating/deleting from TableC will be moved over to Subscriber.  Also note, rows in TableC in Primary are not important and will be routinely cleaned up since they have already been replicated to Subscriber.

    Since Subscriber is going to be handling transactions, we needed to have redundancy setup for TableC.  I was thinking to create a publisher in Subscriber and have TableC replicated back to Primary; however, it will be called TableC_Failover in Primary.  In the event Subscriber fails, we will drop TableC and rename TableC_Failover to TableC in Primary, then change the App to point to Primary.

    Is this a sound design?  Please note, it is meant for a temporary solution without too much drastic changes.

    I've tested this setup and everything seems to be working fine; that is, inserts in TableC are replicating from Primary to Subscriber successfully.  Updates/deletes from TableC is also replicating properly from Subscriber to Primary.TableC_Failover.  However, the initial insert to TableC from Primary to Subscriber is not replicating to Primary TableC_Failover; I can perform a separate insert directly in Subscriber.TableC and do see it replicated to Primary successfully.

    Thanks in advance and apologize for the long post.

    -Ken

     

  • January 17, 2020 at 1:59 pm

    #3716300

    Does anyone know why the initial insert into TableC in Primary which gets replicated to Subscriber doesn't trigger the log reader agent on Subscriber to send the transaction back to Primary?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply