locked
SYNC data in two tables in SQL RRS feed

  • Question

  • Hello Friends

    We have a requirement like, we have inserted data into sql table A from WCF service. Now we want to sync data in table B from A. So that both tables should have similar data. Any help will be appreciated.


    anagh

    Tuesday, December 6, 2016 1:14 PM

Answers

All replies

  • Hi Anagh,

    1) If you wish to do this in Biztalk. 

    You need to get the source table polled by a receive port and when data is detected, it knows to
    retrieve the records and then insert them into your destination database.

    Refer: BizTalk WCF adapter to achieve data synchronization between heterogeneous database / system

    You have several other obvious choices:

    1) SSIS : https://blogs.msdn.microsoft.com/jorgepc/2010/12/07/synchronize-two-tables-using-sql-server-integration-services-ssispart-i-of-ii/

    2) SQL Server Service Broker: http://msdn.microsoft.com/en-us/library/bb522893.aspx

    3) SQL Job/script. : https://msdn.microsoft.com/en-us/library/dn266028(v=vs.103).aspx

    The data sync would be much better and faster, if it can be done on the basis of some kind of delta identifier or flag. Basically, you should update the target db data rows only when it is out of sync with the source db.

    In SQL server db, you can take the help of the Checksum fn also to build the delta based identifier.

    You should develop a SQL based job to get invoked at a certain time of the day or night to get this sql logic fired. It is better to run it as a nightly SQL job, when the db usage is very low. If the delta of the source and the target db records does not match, then pull those records only. But the downside would be to calculate the checksum of the source data rows every time and then compare it with the target data.

    If you have a column like "LastModifiedDate" in the source db tables, then you can skip the checksum approach. This way, your evaluation will be executed on the date based column and takes less time in comparison to the checksum approach.

    Rachit Sikroria (Microsoft Azure MVP)

    • Marked as answer by Anagh Wednesday, December 7, 2016 5:37 AM
    Tuesday, December 6, 2016 2:16 PM
    Moderator
  • This is a very common scenario.

    Like Rachit mentioned there is plenty of ways of going at this now, however if you want to set this up as a permanent solution I would suggest you to create a stored procedure on the SQL Server, this can then write to the to tables/databases you want. Then simply connect your BizTalk application to this stored procedure and it will automatically update both tables and keep them in sync.

    // Tord

    Tuesday, December 6, 2016 6:22 PM
  • Thanks Rachit

    anagh

    Wednesday, December 7, 2016 5:29 AM