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:
- Use SQL Server Change Tracker along side SSIS. This seems like it's doable.
- Any commercial products that would do this pretty much out of the box? Red Gate, ApexSQL, etc.?
- Custom .NET implementation with Windows Services - this one I don't really want to do.
- 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 :)