Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
+1 for very hard to debug - SB processes all run in the background as system users and are incredibly hard to troubleshoot –  JNK Jul 29 '13 at 16:05
    
@JNK Thanks ! Also tracking data changes synchronously is resource intensive when the table is having huge Inserts/updates/Deletes. –  Kin Jul 29 '13 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 Jul 29 '13 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 Jul 29 '13 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 Jul 29 '13 at 18:12
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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