Post reply

Need a cascade solution across slow WAN

  • July 17, 2008 at 2:43 am

    #189699

    Hi there,

    I need to replicate a table across a very slow WAN link. The problem I have is that for a large transaction, the destination table becomes locked for a long period of time.

    I want a solution that allows me to insert,update,delete the table at the publisher, have this replicate to the subscriber over the WAN link. When the transaction has been committed at the subscriber, change the data in an identical table in the same database (perhaps in a different schema) so that the locking is reduced to the time to make the changes at the subscriber. The users application is pointed at the final table ([dbo].[Orders] in Northern Iceland).

    So for example, I have a published table in New York called [dbo].[Orders]. This is replicated to the [replstaging].[Orders] table in Northern Iceland using a slow WAN link (about 100kbit). The bit I'm interested in comes next. I want some kind of solution to wait for committed transactions in [replstaging].[Orders] and make those changes in dbo.Orders in Northern Iceland, but only after they have been committed.

    I tried doing this using Republishing in SQL Server replication but it seems that SQL Server starts performing the third hop into the final table before the commit to replstaging has completed. It therefore locks the table/lots of pages for a long time. I also tried using INSERT, UPDATE and DELETE triggers on replstaging to move the data into the final destination but they seem to fire before the data has been committed (which surprised me). I thought triggers fired on COMMIT - doesn't seem to be the case.

    So, my question is, how do I copy the data from replstaging to the final destination AFTER the data has been committed to replstaging?

  • July 17, 2008 at 5:21 am

    #844280

    I've just realised that to solve this, all I have to do is use SNAPSHOT ISOLATION at the subscriber. The subscriber does not need to change data, so this would be a perfect solution for us. Thanks for reading.

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

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