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?