Data transfer between two linked SQL servers RRS feed

  • Question

  • Hi,

    I need to build a service that copies new rows from SqlServerA.DB1.TABLE_X to SqlServerB.DB2.TABLE_X. Direction is from SqlServerA to SqlServerB

    Source and destination tables are the same structure. SqlServerA and SqlServerB are located in two different cities and there is VPN connectivity between them. The VPN is based on Internet and reasonably fast.

    The service need to be able to recover when VPN is down and try again later when VPN is running again.

    I wonder if linked servers are suitable for this scenario? I can avoid joining distributed tables together and leverage linked servers by using INSERT statements.

    I am aware of MERGE replication and I wonder if MERGE replication is a simpler alternative?


    Thank you!


    Tuesday, November 2, 2010 3:37 AM


  • Some of the considerations while using Merge Replication :-


    A.)   It will add an extra column to the tables at source and destination , the column name would be “rowguid”, so make sure that this additional column does not break your existing application DML operations.

    B.)    It’s not just the inserts but also the updates and deletes that will flow from Source to Destination.

    C.)    Any changes made on the Destination will also flow back to Source by default , but can be avoided by choosing “Download Only” Article property.

    D.)   While the connectivity is down, the Merge agent will fill the Replication Monitor with connectivity errors but it should not harm anything else, once VPN is up and running it will start working fine, can schedule the Merge agent to run when VPN is expected to be up.


    If the above mentioned points does not bother you, configuring Merge would be a good option with less manageability and human intervention.


    Regards Rishi Maini
    Tuesday, November 2, 2010 5:25 AM