1

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?

Improve this question
6
  • What is the purpose of keeping XLOCAL in sync? What are you using the local copy for? – Aaron Bertrand Apr 27 '18 at 11:18
  • The local copy XLOCAL is the one that will be accessed by all web applications.Actually, the only purpose of XCLOUD is to produce XLOCAL. – carlo.borreo Apr 27 '18 at 13:55
  • And do "all web applications" read from XLOCAL or do they also write? – Aaron Bertrand Apr 27 '18 at 14:19
  • I am still not clear with your requirements as @AaronBertrand already asked in his comment. This answer How do I copy SQL Azure database to my local development server? might help if you are looking for daily copy of whole database. – SqlWorldWide Apr 28 '18 at 11:47
  • If the database is in Azure SQL Server VM, you could setup a basic availability group and "mirror" with your local database, IF your web apps only need to read from the local database. – stacylaray Apr 29 '18 at 23:40
0

This is returns column and table names you can use to build up the statements 

select column_name, table_name, ORDINAL_POSITION 
from information_schema.columns
order by table_name, ordinal_position

I would do it in this order: delete, update, insert
Merge is known to have some issues

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.