In the article, Keeping Fact Tables Online while Loading via Partition Switching, we had discussed that fact tables are often required to be kept online when data updates are happening during office hours. In order to shorten the loading window or downtime, we can use schema swapping as a method to keep fact table online. In this article, we look at the specific approach in more detail.

I have created two target tables, dbo.TGT_swap_tbl and shadow.TGT_swap_tbl, in the dbo and shadow schemas, respectively (query attached). dbo.TGT_swap_tbl is the primary fact table used for reporting purposes. The swap schema currently does not contain any tables as schema swapping cannot occur if an object of same name exists under it. During the schema swapping process, a table is simply transferred from one schema to another via a metadata change. Hence this is essentially a very quick process as no data gets transferred. In our example, schema swapping occurs via following steps:

I have used the following SSIS package, to perform the above steps (package attached):

Task 1: In this Execute SQL Task, table in shadow schema is truncated as we are focussing on loading smaller fact tables (<1,000,000 rows) which can be truncate loads. (ie. Tables loaded from scratch every time). This refers to step 5 in Fig 1. I have chosen to perform this step upfront as I want to ensure that shadow schema is empty every time prior to load. However this could be performed at the end of the package flow as well.

Task 2: In this Data Flow task, we are loading the data in the shadow schema. This is to ensure that the primary table (in dbo schema) is not affected during the load process. This refers to step 1 in Fig 1.

Task 3: In this Execute SQL Task, I am performing the schema swapping process as highlighted in Fig 1 (steps 2,3,4 in Fig 1).

We can see that the entire schema swapping process occurs at the end when the data has already been loaded into the table in shadow schema. Since schema swapping occurs very fast, the fact table would have very minimum downtime of few seconds.

Reference                                               

1. http://www.sqlservercentral.com/articles/SQL+Server/149123/