SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Keeping tables online during loading with schema swapping using SSIS

By Shubhankar Thatte,

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/

 

Resources:

SwapSchema.sql | SwapSchema.dtsx
Total article views: 837 | Views in the last 30 days: 837
 
Related Articles
FORUM

Error occuring in report processing

Error occuring in report processing

FORUM

Schema and performance

does the schemas count in a database affects the server performance

ARTICLE

In-Memory OLTP Table Checkpoint Processes Performance Comparison

The article demonstrates one of the major performance enhancements in checkpoint processes in "in me...

FORUM

Processing XML Files

Ways to improve performance processing XML files

ARTICLE

Shadow IT

There are often Shadow IT groups in many companies. Steve Jones talks about the potential problems o...

 
Contribute