I'm trying to find a better solution when publishing data to a remote table.

So we have Server A and Server B. Server A holds a master table which should be replicated to Server B.

Currently, we have this pretty chaotic stored procedure that runs every minute or so. It locates any changes and inserts them into a temporary table on Server B. Once loaded, it inserts new records or updates existing records from this temp table (The table is hit constantly and we want to limit any for of lock).

We don't have a DBA currently so I'm trying my best (I'm just a web developer) to figure out better solutions that could scale in the future.

Note: We have a couple dozen stored procedures that are like this.

1 Answer 1


Your best bet is to use Transactional Replication.

T-Rep is meant to be suited in your scenario as you want to replicate a table from server A to server B.

No need to run a store procedure every min (unless you are doing something specific with these SP's which you have not highlighted in your question).

Note: Only when you generate an initial snapshot in transactional replication, there will be locks on the table. So better to set up during maintenance window if your table is very large.

Also, in T-Rep changes are tracked asynchronously and applied asynchronously to the subscriber (destination) server. It has Wizards to allow you to configure T-Rep correctly with no coding efforts.

Service Broker on other hand will track data changes synchronously and apply to the destination server asynchronously. It will require more coding and depending on the amount of data to be transferred, it will be slow.

I have dealt with Service Broker and trust me ... its very hard to debug .. just like a black box vs Replication will show you errors on GUI and is easy to troubleshoot as well.

  • +1 for very hard to debug - SB processes all run in the background as system users and are incredibly hard to troubleshoot
    – JNK
    Commented Jul 29, 2013 at 16:05
  • @JNK Thanks ! Also tracking data changes synchronously is resource intensive when the table is having huge Inserts/updates/Deletes.
    – Kin Shah
    Commented Jul 29, 2013 at 16:36
  • From what I've seen so far, Service Broker isn't such a black box, as long as you know where to look. :) As I discovered when researching my answer here, Service Broker activity shows up in traces, and I suspect Extended Events as well. Also, if a stored proc outputs anything in a result set, the results go to the SQL Server Error Log. In any event, I agree with Transactional Replication, so +1.
    – Jon Seigel
    Commented Jul 29, 2013 at 16:42
  • @JonSeigel Thanks ! My whole point was to emphasize T-Rep opposed to Service Broker for this scenario. I agree with you that SB have evolved but still requires good troubleshooting skills.
    – Kin Shah
    Commented Jul 29, 2013 at 16:51
  • Awesome. To be honest I was really hoping the SB would not be the way to go. Thanks for the help. I'll research T-Rep.
    – Antares
    Commented Jul 29, 2013 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.