none
Compare two tables and reflect changes in one of them RRS feed

  • Question

  • There is mssql table in external customer network. The aim is to create and reflect same table in local server. External mssql table of course can be changed (data) every hour and somehow i have to check for changes and reflect that changes in local table, when new rows are added/deleted or updated. Is there any efficient way to do it? Additionaly i know that this table will have thousands of records. First of all i though about some windows service application but have no idea what approach to do, i do not think datatable/dataset with regards to so much records is fine as i remember memory out of exception in past. Any ideas?

    Wednesday, June 26, 2019 7:18 AM

All replies

  • INO, you should be looking into MS SQL Server triggers, which you could implement across databases over a network, like a local area network (LAN)  or wide area network (WAN)/Internet with  communications between two databases. 

    http://www.sqlservertutorial.net/sql-server-triggers/

    https://docs.microsoft.com/en-us/sql/database-engine/dev-guide/clr-triggers?view=sql-server-2014

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-service-broker?view=sql-server-2017

    <copied>

    SQL Server Service Broker provide native support for messaging and queuing in the SQL Server Database Engine and Azure SQL Database Managed Instance. Developers can easily create sophisticated applications that use the Database Engine components to communicate between disparate databases, and build distributed and reliable applications.

    <end>

    https://www.amazon.com/dp/1590599993/?tag=stackoverfl08-20

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlnetfx

    You can even use C# code in the CLR triggers.

    https://www.c-sharpcorner.com/UploadFile/37db1d/create-your-first-clr-trigger-in-sql-server-2008-using-C-Sharp/

    And Service Broker can use C# too.

    SB is powerful database technology.

    Wednesday, June 26, 2019 8:38 AM
  • How much ability do you have to make changes in the remote SQL Server? There are several options that would facilitate this operation and would work even with millions of records.

    One is to use Change Data Capture (CDC) or Change Tracking (CT), if the version/edition of the server supports any of them. When you enable one of these features on the table(s) that you want to synchronize, the server keeps track of all the changes performed on the tables. You can then query for "all the changes since (last time you synchronized)", and you can merge the changes into your table. You don't need to do this manually; you can use SQL Server Integration Services (SSIS), which understands CDC and has a Wizard that generates the dataflow for doing all the syncing.

    Another option is to add a field for "last change" in the remote table, and then do a Select that retrieves al row that were changed after your last sync. This will not bring-in the deletions. To sync the deletions, you can add a trigger on the remote database that moves deleted rows to a staging table. You can the select rows from that table and delete them from the synced table.

    Another option is to add the remote server as a linked server to your local database, and then use the MERGE statement.

    Note that all of these options are "pure SQL", you don't need to write any C# code, although of course you can trigger any of them from your C# program. If you need to dig deeper into any of these options, I suggest asking in one of the SQL Server forums.

    Wednesday, June 26, 2019 9:26 AM
    Moderator
  • First of all, i just send question whether is it possible to enable Service Broker for me. If that would be possible i could use SqlTableDependency which according to documentation can create their own Service Broker staff to be notified about crud operations. What you think would it be good approach?


    One more important question: Will SqlTableDependency work also with views? I just saw there are no table but views i will have access.

    Thursday, June 27, 2019 10:27 AM
  • First of all, i just send question whether is it possible to enable Service Broker for me. If that would be possible i could use SqlTableDependency which according to documentation can create their own Service Broker staff to be notified about crud operations. What you think would it be good approach?


    One more important question: Will SqlTableDependency work also with views? I just saw there are no table but views i will have access.

    You should use the power of the database engine and the service it provides, IMO.

    https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency

    <copied>

    Track record table change under the hood

    SqlTableDependency's record change audit, provides the low-level implementation to receive database record table change notifications creating SQL Server triggers, queues and service broker that immediately notifies your application when a record table change happens.

    Assuming we want to monitor the Customer table content, we create a SqlTableDependency object specifying the Customer table and the following database objects will be generated:

    <end>

    About views, you should post to the DBA(s) in the SQL Server forums.



    • Edited by DA924x Thursday, June 27, 2019 3:19 PM
    Thursday, June 27, 2019 3:16 PM