I have a SQL Express 2012 DB in which there are tables that need frequent full "refreshs" (on-hours) from an external source without any disruption of the users. Let's say the tables are Item, Inventory & Barcode.
I need to keep these tables in-sync with a MySQL database (the system of record) and I am looking for a fast & reliable way to fully replace the contents of the SQL tables with data from the MySQL database. I will be using a C# app for this (reading the data from MySQL into memory, then writing it to the SQL server [likely using Bulk Copy]).
The approach that I am considering is to have an _A and a _B version of each of the tables above, with a synonym pointing to the current one. The update would work something like this:
1) Read control record which tells me that A is the current version (i.e. the synonyms are currently pointing to the _A versions).
2) Drop the constraints of and truncate the _B tables (Item_B, Inventory_B & Barcode_B)
3) Do the Bulk Copy to populate the _B tables
4) Add the constraints back to the _B tables.
5) Drop and re-create the synonyms so that they point to _B (i.e. Item -> Item_B, Inventory -> Inventory_B, Barcode -> Barcode_B)
6) Note: the _A and _B tables would never be dropped, just truncated, so that the permissions do not get lost.
The above approach seems fairly good to me, except for the fact that I am using code-first Entity Framework on the SQL side and it gets a bit messy when there is a schema change; I assume that EF will apply migrations to the _A or _B tables depending on the current synonyms, so if it updates the schema of the _A tables, I will have to manually do the same to the _B tables. Not a huge deal.
I am looking for feedback on the above as well as suggestions on how to do it in a cleaner / better way.