We have a local SQL Server database which we have full control over, and in this local server we are linking a remote SQL Server database, to which we have only read access (no schema changes, no creation of triggers, stored procs, views, etc.). I'm not sure if we are able to enable distributed transactions on the remote server, but I can check if that is important to know.
A 3rd party applies inserts/updates/deletes to the remote server tables, and we want to know when something changes in the remote server (ideally in real-time, but polling might be sufficient), so we can re-process some data in the local server.
What would be our main options for achieving this? We would prefer lighter-weight solutions if possible (i.e. not SSIS – because none of us have really used SSIS).
Best Answer
Are you looking to synchronize the data locally? Ideally, you would accomplish this through some form of replication from the source.
If you have a local copy of all tables, you can create a script or stored procedure to check for records deleted, inserted, or updated by comparing to your copy of the data. This would have to be done on a table by table basis. If there are hundreds of tables, then this approach is probably not feasible.
It helps if there is a time stamp of some sort on the table row. That will assist in determining when an update occurred. Inserts and deletes are easier as you just find records that exist at the source but not in the destination and vice versa.
Schedule your script to run at defined intervals via SQL Agent.