Best Possible Approach : Service Broker or replication?


  • I have a business scenario. I have an application whose DB resides on SQL Server A. My application resides on SQL server B. Both SQL servers are on the same network. Server A has a table that is being constantly updated via a web application. We want that table to be on Server B. At present it is updated nightly from Server A to Server B.

    We want the changes to be replicated to server B in almost real time. 30 minutes delay could work.

    Now considering this business case what would eb my best options:

    1. Implement CDC on server A for the interested table ( Please note that CDC is only for 1 table, I'm not sure if this is possible) . Bring the table from Server A once to Server B using a Linked Server. Then use the CDC from server A to Merge and Update on table on Server b using a SP and Schedule this SP after 30 minutes.


    2. Use CDC and Service combination.


    3. use Replication.

    OR  ...???

    Any help will greatly be appreciated.

    Monday, January 13, 2014 2:49 PM


All replies

  • Transactional replication is built in and will work for what you describe as long as the table has a primary key. 

    Monday, January 13, 2014 2:59 PM
  • 1) and 2) will both work.  Would you rather implement and operate Transactional Replication, or write and support a couple of stored procedures.

    Also Change Tracking would work for this since you only want the latest version of each source row.



    Monday, January 13, 2014 3:07 PM
  • Snapshot Replication is OK solution here.  You would need to set the snapshot period to 30 min or less. If you want changes to get populated immediately use transactional replication. Service Broker is the best approach since you wouldn't have to maintain infrastructures associated with establishing replication facility. But use of Service Broker will require some expertise. Also take a look at this product called SnipeDB framework SnipeDB framework would not replicate the table, but would allow any subscriber application to get an update on any change in your data.
    Wednesday, January 15, 2014 5:12 PM