4

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

1

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.

0

Unfortunately, MERGE functionality does not work over Linked server (at least not in 2012, not sure bout 2016). Otherwise, that's the first thing I'd suggest.

To Gary's point, I'd keep a local copy on your server, then just bring all records you want to check from the linked server to your local server (as CTE or Temp tables). Then you can use those to verify changes and eventually MERGE with your local tables.

I actually had a similar situation (where a third party app updates the tables on a remote server), but I ended up just running a procedure remote query every hour (for each table) which checks for any records which have not been inserted locally yet.

Something like:

INSERT INTO [LocalDB].[dbo].[LocalTable]
([ID] --This is a PK/IDENTITY on the Remote side
,[Col2]
,[Col3])
SELECT
 [ID]
,[Col2]
,[Col3]
FROM OPENQUERY ( [RemoteServer], 'SELECT
                                  [ID] --PK/IDENTITY
                                 ,[Col2]
                                 ,[Col3]
                                 FROM [RemoteDB].[dbo].[RemoteTable]') AS [RemoteTable]
WHERE [RemoteTable].[ID] NOT IN (SELECT DISTINCT ([LocalTable].[ID])
                                 FROM [LocalDB].[dbo].[LocalTable])

That runs every hour for about 6-7 Tables, and has been doing so for the last year or two.

I also have two variables: One which counts the number of rows in the local table before the above query starts, and one that counts the number of rows in the local table after the query runs. This gives me an accurate count of how many records were inserted. I then log to a transaction table as such:

--Log the Transaction to the Replication Log
    BEGIN TRANSACTION
        INSERT INTO [LocalDB].[dbo].[LocalLogTable]
        (   
           [TableName]
          ,[StartDateTime]
          ,[EndDateTime]
          ,[Status]
          ,[ReplicationType]
          ,[RowsBeforeReplication]
          ,[RowsAfterReplication]
        )
        VALUES
        (
           'RemoteTableName'
          ,@ReplicationStartDateTime
          ,@ReplicationEndDateTime
          ,@Status
          ,'INSERT'
          ,@RowsBeforeReplication
          ,@RowsAfterReplication
          )

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.