I'm designing a database whose data will be generated daily by an ETL. After some research, I found that one can use partition switching or schema transfer to maximize the uptime of the "live" data. Just for completeness of information, this database will be deployed on Azure SQL Database and developed on SQL Server 2019 Developer Edition on Docker. It consists in ~ 20 tables, each one having less than 10M rows (expected).

So now in my design there are two schemas:

  • [App]: containing multiple tables (w/o foreign keys) with the live data used by the application.
  • [AppShadow]: containing the exact same tables as the [App] schema (but w/ foreign keys), usually empty, used as destination for the ETL.

My flow looks like this:

  • Perform the ETL using the tables in the [AppShadow] schema as a destination. The foreign keys ensure that my data is consistent.
  • set transaction isolation level serializable; begin transaction;
  • Disable all the constraints on the tables in the [AppShadow] schema using alter table [AppShadow].[<table_name>] nockeck constraint all;
  • Truncate all the tables in the [App] schema
  • Switch the partition of all the tables from [AppShadow] to [App]
  • Re-enable all the constraints on the tables in the [AppShadow] schema using alter table [AppShadow].[<table_name>] with check check constraint all;
  • commit transaction;

This solutions seems to work fine and the downtime is minimal, but I can't figure out any advantage or disadvantage over transferring the entire tables between schemas using a [Swap] schema and the alter schema <schema_name> transfer <table_name> statement. SQL Server being SQL Server, I'm pretty sure there are some hidden caveats that I'm unaware of that should let me prefer a solution over the other.

Do you have any advice or suggestion?


Do you have any advice or suggestion?

All the operations you described are metadata-only, so I wouldn't expect any significant difference in performance.

It is strange that you don't have Foreign Keys on your [App] schema. You've already paid the cost of checking the DRI, and if you have validated foreign keys in your database the Query Optimizer can take advantage of them.

Schema transfer would allow you to leave all the foreign keys in-place.

  • Thanks David, I didn't put FKs in the tables of the [App] schema in order to avoid the cost of re-enabling them after the switch. In fact I might use schema transfer to keep them in place or re-enable the checks without the with check clause to prevent recalculation and still use partition swap. Is there any benefit of a particular solution knowing that I'll be using dacpacs to automatically deploy changes to the database? Can the schema transfer method result in a "schema drift" when deploying? Mar 1 at 21:50
  • 1
    Schema drift should be fine so long as you don't leave objects in the [Swap] schema. The database will have all the same objects, just with different ObjectId's, which DACPAC doesn't care about. BTW if you disable and re-enable an FK without with check the query optimizer won't trust the FK. Mar 1 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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