I have 2 SQL Server Databases A and B, and I want to synchronize (continuously, every 10 minutes, max) the data from 70-80% percent of the tables from A to B (just A -> B, B is a read only database), but with some constraints:

  • The schemas of the tables will 99% the same - tables from database B will have and extra DeletedDateTime column. This means that deleting an entry from a table in database A should result in a soft delete in that same table in database B.
  • There some transformations might be needed on some cells before moving the data: like changing the reference system of some latitude and longitude values
  • Any change in database A should be reflected in database B in 5 - 10 minutes
  • I have to use SQL Server 2014 Standard Edition, so features like "Change data capture" aren't available. Upgrading to Enterprise edition or using a newer version of SQL server isn't an option unfortunately.

Now, I've thought of possible ways of doing this, but given that SQL Server isn't really my area of expertise, I'm looking for some suggestions:

  1. Use SQL Server Change Tracker along side SSIS. This seems like it's doable.
  2. Any commercial products that would do this pretty much out of the box? Red Gate, ApexSQL, etc.?
  3. Custom .NET implementation with Windows Services - this one I don't really want to do.
  4. Any other suggestions?

This doesn't sound like such an exotic scenario and I'm pretty sure someone has done it before.

I'm just looking to be pointed in the right direction :)

Thank you

  • I think "synchronization" is a misnomer in this case if you're talking translating a hard delete into a soft delete + transforming other data. Just use SSIS to do it, because you're starkly into the ETL category IMO. – LowlyDBA Apr 1 '18 at 2:30
  • Yeah, maybe synchronization isn't the exact term. Would using ssis and change tracking have any limitations in terms of table size and number of tables? Thanks – Mihai Apr 1 '18 at 4:51
  • Not inherently, but it all depends how you approach it. There are a ton of ways you can do one thing in SSIS, so make sure you're doing load testing to make sure your approach can scale properly. – LowlyDBA Apr 1 '18 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.