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.
Best Answer
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.