2

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?

3
  • Why do you want to do this? Tell us the business problem you're trying to solve, and maybe we can suggest a good way to implement it. May 22, 2015 at 13:35
  • I have a system that is used to track circuit boards around a factory - it tracks them via barcode and stores the results of each process they go through as they move round the factory. They have a new, automated test machine which stores its results in its own local database. They want these results brought into the main system in as close to real-time as possible so that they can be reported on along with all the other processes. So they need to be moved into the log table for the main system, but also processed somewhat as they are in a different schema.
    – chipples
    May 26, 2015 at 7:52
  • Maybe SQL Server Change Data Capture will be useful for you? msdn.microsoft.com/en-us/cc645937.aspx May 26, 2015 at 8:15

1 Answer 1

1

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:

  1. 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".

  2. Connect to Server A and retrieve all records from Server A that are greater than this primary key / timestamp

  3. 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.

5
  • Thank you, this is pretty much the approach I was considering, apart from having the job running continuously and sleeping. instead of having it run every 30 seconds.
    – chipples
    May 26, 2015 at 7:54
  • Be very careful with your logic to determine new or updated rows. "last updated timestamps" are dangerous to use as they are set when the record is inserted or updated, not when the transaction is committed. If there are long running transactions you could easily miss new or updated rows. The process becomes much easier of course if there are only new records, but you still have to be wary of missing data. Change Data Capture gets around all of these issues by offering trigger-like semantics by maintaining a log of changes for you. May 26, 2015 at 8:19
  • If the record is inserted or updated since the last time your process reviewed the table, you will grab it. If there are records currently being updated, they will block the process until the transaction is complete (assuming read-committed).
    – datagod
    May 26, 2015 at 20:51
  • There will only ever be new records, not updated ones. I have been just logging the highest transferred record ID in a separate table, and then using that to retrieve new records when it runs. Are there any problems with this approach? Also, what is the reasoning behind having it run continuously with a sleep, rather than having it repeatedly triggered by the scheduler?
    – chipples
    May 28, 2015 at 8:02
  • @chipples, see my note in the answer.
    – datagod
    May 28, 2015 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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