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
alter table [AppShadow].[<table_name>] nockeck constraint all;
- Truncate all the tables in the
- Switch the partition of all the tables from
- Re-enable all the constraints on the tables in the
alter table [AppShadow].[<table_name>] with check check constraint all;
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?