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.

  • How big are these tables? Have you considered using an ETL tool like SQL Server Integration Services? It would be better suited for this task than a C# app IMHO. – CasualFisher Jul 30 '18 at 23:23
  • I've written about using schema transfer before, but Kendra Little covers an even better approach here: littlekendra.com/2017/01/19/… – Aaron Bertrand Jul 30 '18 at 23:53
  • @sqldavedb There is a lot more to the full picture than the above question (the app does a lot of other integration work). I will look into SSIS, but will likely prefer to keep the integration work in the current app instead of involving a separate tool for this part. – user208115 Jul 31 '18 at 0:04
  • @aaron-bertrand Interesting article (but I am not sure that it offers benefits over the approach that I outline above in my scenario). – user208115 Jul 31 '18 at 0:38
  • 1
    The one benefit (and the reason I pointed you to the article) is that rename / schema transfer / drop/create synonym requires full-on Sch-M locks, while the switch solution allows you to wait at low priority. But, you know, ¯\_(ツ)_/¯ – Aaron Bertrand Jul 31 '18 at 0:38

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.