SERIES: Modifying Tables Online
In the next few days I want to describe a way to modify the definition of very large tables while keeping the table available for other queries. Some table changes are already online operations. But offline changes need extra work to keep the table available. I explore one way to do that in this series.
This article used to be a single blog post, but I broke it into four parts because it seemed too long. Read Part 1, and understand the strategy I’m using. Treat Parts 2-4 as appendixes. When you want to use this plan (or one like it), come back and use them as examples. You won’t be able to make use of the examples by cutting and pasting directly (unless you happen to be running an bike equipment store called Adventureworks), but modifications shouldn’t be too hard.
Just One More Thing
So Rob Volk commented on Part 3 of my blog. The comment prompted me to write a part five (which you’re reading now). So you can thank him for this bonus section.
I’m going to quote Rob directly. He wrote:
One question/suggestion, have you considered using a new schema (e.g. Sales_New) and creating the new table in that schema? The benefit is that all the defaults, keys, triggers, and indexes can retain the same names as the original, and the switch becomes a single ALTER SCHEMA…TRANSFER operation. (or two, one for the old table and one for the new)
He’s absolutely right. The switch step becomes much much simpler and simpler is almost always better (as it is in this case). My migration would start by creating some extra schemas and then creating the staging table. In the Adventureworks example I’ve been using, that looks something like this:
use AdventureWorks2012 go create schema staging; go create schema obsolete; go CREATE TABLE staging.SalesOrderHeader( -- etc...
So now our DB is prepared and looks like this:
Copy the data over to the staging table the same way. In my SalesOrderHeader example, that code doesn’t change at all except that I replace “Sales.SalesOrderHeader_new” with “Staging.SalesOrderHeader”.
When ready, the switch now includes code like this:
ALTER SCHEMA obsolete TRANSFER Sales.SalesOrderHeader; ALTER SCHEMA Sales TRANSFER staging.SalesOrderHeader; drop trigger obsolete.t_i_SalesOrderHeader; drop trigger obsolete.t_u_SalesOrderHeader; drop trigger obsolete.t_d_SalesOrderHeader;
Looks simple right! It is. Simple is better. The schema now looks something like this:
A few things to be careful of:
- Foreign keys: Although no renaming is necessary, they still have to dropped from and to the obsolete table. And foreign keys pointing to the new table still need to be added.
- Existing triggers: They should recreated on the new table.
So dear reader, that’s the series. It was fun for me to explore in depth an intermediate topic. Thanks for bearing with me. I know the series was a little dry, but I figured that I wanted to use this walk-through for myself as a template for future migration projects and that if I thought it was useful, maybe you would think so too.