I have an SQL Server 2016 Azure database in the cloud called XCLOUD, with many tables big and small, and a local SQL Server 2016 database with the same tables and data called XLOCAL.
In all databases there are neither PK, nor FK.
My goal is to keep XLOCAL updated with all the changes in XCLOUD, minimizing network traffic and keeping XLOCAL available all the time. Performances on Azure are not a problem: once XCLOUD is filled, the server does nothing else until the next day.
A daily process on the Azure server truncates all the tables in XCLOUD and fills them again with almost the same data. I don't have control on this process: if I had, I would change it to only INSERT/UPDATE/DELETE and then I would use log shipping, but this is not possible.
One idea I had is to create a new database from XCLOUD1 on the Azure server: when the process filling XCLOUD finishes, I would somehow compare the differences between XCLOUD and XCLOUD1, update XCLOUD1 with MERGE statetements as needed, then use log shipping from XCLOUD1 to XLOCAL. But I would need to code the MERGE statements one by one, unless you can recommend some tool for this task.
What would you do?