Q:

Periodically update a table with new records from another server

I have databases on two different servers. I need to regularly retrieve new records from a table on server A, and process them in order to use them to update a table on server B (with a different schema). 
I was going to use a trigger for this, but if this fails, the inserts on server A are rolled back. The inserts on table A must not fail, so the update of server B needs to be as decoupled from this as possible. 
I am now thinking of using a scheduled stored procedure on server B to retrieve the results from server A and update server B. This would need to run every 30 seconds. 
Is there anything wrong with this approach, or is there a better or more 'correct' way of achieving this?

A:

Create a linked server connection with read access from Server B to Server A.  Have a process running on Server B that does the following:

Examine the table on ServerB to identity the last inserted record from Server A.  Hopefully you have an auto-incrementing primary key (int).  If not, look for the greatest "last updated timestamp".
Connect to Server A and retrieve all records from Server A that are greater than this primary key / timestamp
Insert these new records into Server B.

I always recommend full error trapping and process logging in any sort of automated system like this.  You need to know how it is behaving, and you need to be alerted if there are any problems.
I would put all the logic in a stored procedure, connect from A to B using a linked server, and have the procedure sleep for X seconds after it is finished processing.
Put the stored procedure in a SQL Server job that is set to run continuously.  It will appear to always be running, but the stored procedure will be sleeping most of the time.
Note: The reason I create jobs to run continuously and then having the procedure sleep (using a wait statement) is to prevent clutter in the job history logs.  I actually create the job to run on a schedule (every minute) but because the stored procedure sleeps, the job never finishes.  You can chalk it up to personal preference.

