Online Schema Changes | Michael J. Swart

April 27, 2012

Modifying Tables Online – Part 5: Just One More Thing

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: "sql server", online schema changes, table migrations — Michael J. Swart @ 10:50 am

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.

Part 1: Migration Strategy
Part 2: Implementation Example
Part 3: Same Example With Error Handling
Part 4: Testing
Part 5: Just One More Thing

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.

That’s It

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.

-- Comments (5)

April 26, 2012

Modifying Tables Online – Part 4: Testing

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: "sql server", online schema changes, table migrations — Michael J. Swart @ 12:00 pm

SERIES: Modifying Tables Online

Part 1: Migration Strategy
Part 2: Implementation Example
Part 3: Same Example With Error Handling
Part 4: Testing
Part 5: Just One More Thing

This is part 4 of the series (you can consider this Appendix 3, The main article is found here).

So my testing is also pretty straightforward. I

Backup the Adventureworks2012 database on my dev machine
Start sending a whack of queries to the database to simulate concurrent activity
Make Adventureworks2012.Sales.SalesOrderHeader larger by adding lots of rows
Migrate the database using the script from part 3
Look for any errors, fix them, restore the db and repeat the process if necessary
Otherwise … profit?

You should be able to handle each of these steps. Except that I want to explain exactly how I personally handle number 2: I use an application to send queries to the database many times. Sounds easy right? The trick is that I want to call the stored procedure many times at once.

Activity Generator

This is a C# program which I compile into a .net application (I can’t wait for .net 4.5′s async and await)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading;
 
namespace ConsoleApplication1 {
    class Program {
        static int counter = 0;
        static void Main( string[] args ) {
            SqlConnectionStringBuilder cs = new SqlConnectionStringBuilder();
            cs.DataSource = @".";
            cs.InitialCatalog = "Adventureworks2012";
            cs.IntegratedSecurity = true;
            cs.AsynchronousProcessing = true;
            string connectionString = cs.ToString();
            string sql = @"s_DoSomething";
 
            for( int i = 0; i < 100000; i++ ) {
                SqlConnection conn = new SqlConnection( connectionString );
                conn.Open();
                SqlCommand cmd = new SqlCommand( sql, conn );
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.BeginExecuteNonQuery( new AsyncCallback( EndExecution ), cmd );
            }
            Console.WriteLine( string.Format( @"Error count: {0}", counter ) );
            Console.ReadLine();
        }
 
        static void EndExecution( IAsyncResult c ) {
            SqlCommand endCmd = ( c.AsyncState as SqlCommand );
            try {
                endCmd.EndExecuteNonQuery( c );
            } catch( Exception ex ) {
                //counter++;
                Interlocked.Increment( ref counter );
                Console.WriteLine( ex.Message );
            } finally {
                endCmd.Connection.Close();
            }
        }
    }
}

You might have noticed that this program is extremely boring. It’s just calling one stored procedure: s_DoSomething over and over as fast as it can. The advantage is that I’m free to modify s_DoSomething (which is for me a lot easier than modifying this application).

So what does this procedure do? I’m glad you asked:

s_DoSomething

This procedure executes at random one task chosen from a set of typical OLTP tasks that use the table Sales.SalesOrderHeader. Here’s the one I used:

create procedure s_DoSomething 
as
begin
 
    declare @choice int = 1 + RAND() * 6
    declare @SalesOrderId int;
 
    if (@choice = 1)
        insert Sales.SalesOrderHeader(DueDate, OrderDate, CustomerID, BillToAddressID, ShipToAddressID, ShipMethodID)
        select top 1000 DueDate, DATEADD(day, -1, DueDate), CustomerID, BillToAddressID, ShipToAddressID, ShipMethodID
        from Sales.SalesOrderHeader
    else if (@choice = 2)
        delete top (100) Sales.SalesOrderHeader
    else if (@choice = 3)
        begin
            declare @personid int;
 
            select top 1 @personid = BusinessEntityID
            from Sales.SalesPerson
            order by newid();
 
            select * 
            from sales.SalesOrderHeader
            where SalesPersonID = @personid;
        end;
    else if (@choice IN (4,5,6))
        begin
            select top 1 @SalesOrderId = SalesOrderId
            from Sales.SalesOrderHeader with (nolock);
 
            select top 100 * 
            from Sales.SalesOrderHeader 
            where SalesOrderId > @SalesOrderId
        end
 
end

-- Comments (0)

April 20, 2012

Modifying Tables Online – Part 3: Example With Error Handling

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: "sql server", online schema changes, table migrations — Michael J. Swart @ 1:56 pm

SERIES: Modifying Tables Online

Part 1: Migration Strategy
Part 2: Implementation Example
Part 3: Same Example With Error Handling
Part 4: Testing
Part 5: Just One More Thing

This is part 3 of the series (you can consider this Appendix 2, The main article is found here).

What you find below is exactly the same script as you find in Part 2: Implementation Example with the exception that I’ve included error handling and wrappers to handle the following goals:

If a statement fails, no data is lost
If a statement fails, other queries are not impacted (i.e. we’re online)
If a statement fails anywhere, it can be retried from the beginning with no lost work. This means for example that when data is copied to the staging table in batches, and that process is interrupted and restarted, the script “picks up where it left off”.
The process is re-runnable. This means that if the script succeeds and for some reason the script is run again then no errors are raised and no work is performed.
The last step, the switch, is an atomic transaction. It succeeds 100% or fails and rolls back 100%.

I present without any further comment, the scripts (with error-handling).

Create Staging Table

use AdventureWorks2012
 
-- original table isn't migrated, staging table doesn't exist
IF NOT EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader_new'
    )
AND NOT EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader' 
        AND COLUMN_NAME = 'Rowversion'
    )
BEGIN
 
    CREATE TABLE Sales.SalesOrderHeader_new(
        SalesOrderID int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
        RevisionNumber tinyint NOT NULL,
        OrderDate datetime NOT NULL,
        DueDate datetime NOT NULL,
        ShipDate datetime NULL,
        [Status] tinyint NOT NULL,
        OnlineOrderFlag dbo.Flag NOT NULL,
        SalesOrderNumber  AS (isnull(N'SO'+CONVERT(nvarchar(23),SalesOrderID),N'*** ERROR ***')),
        PurchaseOrderNumber dbo.OrderNumber NULL,
        AccountNumber dbo.AccountNumber NULL,
        CustomerID int NOT NULL,
        SalesPersonID int NULL,
        TerritoryID int NULL,
        BillToAddressID int NOT NULL,
        ShipToAddressID int NOT NULL,
        ShipMethodID int NOT NULL,
        CreditCardID int NULL,
        CreditCardApprovalCode varchar(15) NULL,
        CurrencyRateID int NULL,
        SubTotal money NOT NULL,
        TaxAmt money NOT NULL,
        Freight money NOT NULL,
        TotalDue  AS (isnull((SubTotal+TaxAmt)+Freight,(0))),
        Comment nvarchar(128) NULL,
        rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
        ModifiedDate datetime NOT NULL,
        [Rowversion] rowversion NOT NULL, -- This is the new column!!
        constraint PK_SalesOrderHeader_new_SalesOrderID
            primary key clustered (SalesOrderID ASC),
        constraint FK_SalesOrderHeader_new_Address_BillToAddressID 
            foreign key(BillToAddressID) references Person.[Address] (AddressID),
        constraint FK_SalesOrderHeader_new_Address_ShipToAddressID 
            foreign key(ShipToAddressID) references Person.[Address] (AddressID),
        constraint FK_SalesOrderHeader_new_CreditCard_CreditCardID 
            foreign key(CreditCardID) references Sales.CreditCard (CreditCardID),
        constraint FK_SalesOrderHeader_new_CurrencyRate_CurrencyRateID 
            foreign key(CurrencyRateID) references Sales.CurrencyRate (CurrencyRateID),
        constraint FK_SalesOrderHeader_new_Customer_CustomerID 
            foreign key(CustomerID) references Sales.Customer (CustomerID),
        constraint FK_SalesOrderHeader_new_SalesPerson_SalesPersonID 
            foreign key(SalesPersonID) references Sales.SalesPerson (BusinessEntityID),
        constraint FK_SalesOrderHeader_new_SalesTerritory_TerritoryID 
            foreign key(TerritoryID) references Sales.SalesTerritory (TerritoryID),
        constraint FK_SalesOrderHeader_new_ShipMethod_ShipMethodID 
            foreign key(ShipMethodID) references Purchasing.ShipMethod (ShipMethodID),
        constraint CK_SalesOrderHeader_new_DueDate 
            check (DueDate>=OrderDate),
        constraint CK_SalesOrderHeader_new_Freight 
            check (Freight>=0.00),
        constraint CK_SalesOrderHeader_new_ShipDate 
            check (ShipDate>=OrderDate or ShipDate is null),
        constraint CK_SalesOrderHeader_new_Status 
            check ([Status]>=0 and [Status]<=8),
        constraint CK_SalesOrderHeader_new_SubTotal 
            check (SubTotal>=0.00),
        constraint CK_SalesOrderHeader_new_TaxAmt 
            check (TaxAmt>=0.00)
    )
 
    -- add default constraints
    alter table Sales.SalesOrderHeader_new add
    constraint DF_SalesOrderHeader_new_RevisionNumber  
        default 0 for RevisionNumber,
    constraint DF_SalesOrderHeader_new_OrderDate  
        default getdate() for OrderDate,
    constraint DF_SalesOrderHeader_new_Status  
        default 1 for [Status],
    constraint DF_SalesOrderHeader_new_OnlineOrderFlag  
        default 1 for OnlineOrderFlag,
    constraint DF_SalesOrderHeader_new_SubTotal  
        default 0.00 for SubTotal,
    constraint DF_SalesOrderHeader_new_TaxAmt  
        default 0.00 for TaxAmt,
    constraint DF_SalesOrderHeader_new_Freight  
        default 0.00 for Freight,
    constraint DF_SalesOrderHeader_new_rowguid  
        default newid() for rowguid,
    constraint DF_SalesOrderHeader_new_ModifiedDate  
        default getdate() for ModifiedDate
 
        -- two nc ak
    create unique nonclustered index AK_SalesOrderHeader_new_rowguid 
        on Sales.SalesOrderHeader_new(rowguid ASC)
 
    create unique nonclustered index AK_SalesOrderHeader_new_SalesOrderNumber
        on Sales.SalesOrderHeader_new(SalesOrderNumber)
 
        -- two nc ix
    create nonclustered index IX_SalesOrderHeader_new_CustomerID
        on Sales.SalesOrderHeader_new(CustomerID)
 
    create nonclustered index IX_SalesOrderHeader_new_SalesPersonID
        on Sales.SalesOrderHeader_new(SalesPersonID)
END
GO

Add Triggers

-- staging table exists
-- original table isn't migrated, 
-- triggers don't exist
IF EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader_new'
    )
AND NOT EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader' 
        AND COLUMN_NAME = 'Rowversion'
    )
AND NOT EXISTS
    (
        SELECT 1 
        FROM sys.triggers
        WHERE name = 't_i_SalesOrderHeader'
    )
BEGIN
 
    EXEC sp_executesql N'
        create trigger t_i_SalesOrderHeader
        on Sales.SalesOrderHeader
        after insert
        as 
            set identity_insert Sales.SalesOrderHeader_new on;
 
            insert SalesOrderHeader_new(SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
            select SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate
            from inserted
 
            set identity_insert Sales.SalesOrderHeader_new off;';
END
GO
 
IF EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader_new'
    )
AND NOT EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader' 
        AND COLUMN_NAME = 'Rowversion'
    )
AND NOT EXISTS
    (
        SELECT 1 
        FROM sys.triggers
        WHERE name = 't_u_SalesOrderHeader'
    )
BEGIN
    EXEC sp_executesql N'
        create trigger t_u_SalesOrderHeader
        on Sales.SalesOrderHeader
        after update
        as 
            -- assuming pk values are never altered.
            update Sales.SalesOrderHeader_new
            set 
                RevisionNumber = i.RevisionNumber,
                OrderDate  = i.OrderDate,
                DueDate  = i.DueDate,
                ShipDate  = i.ShipDate,
                [Status]  = i.[Status],
                OnlineOrderFlag  = i.OnlineOrderFlag,
                PurchaseOrderNumber  = i.PurchaseOrderNumber,
                AccountNumber  = i.AccountNumber,
                CustomerID  = i.CustomerID,
                SalesPersonID  = i.SalesPersonID,
                TerritoryID  = i.TerritoryID,
                BillToAddressID  = i.BillToAddressID,
                ShipToAddressID  = i.ShipToAddressID,
                ShipMethodID  = i.ShipMethodID,
                CreditCardID  = i.CreditCardID,
                CreditCardApprovalCode = i.CreditCardApprovalCode,
                CurrencyRateID  = i.CurrencyRateID,
                SubTotal  = i.SubTotal,
                TaxAmt  = i.TaxAmt,
                Freight  = i.Freight,
                Comment  = i.Comment,
                rowguid  = i.rowguid,
                ModifiedDate = i.ModifiedDate
            from Sales.SalesOrderHeader_new s
            join inserted i 
                on s.SalesOrderID = i.SalesOrderID;';
END
GO
 
IF EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader_new'
    )
AND NOT EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader' 
        AND COLUMN_NAME = 'Rowversion'
    )
AND NOT EXISTS
    (
        SELECT 1 
        FROM sys.triggers
        WHERE name = 't_d_SalesOrderHeader'
    )
BEGIN
    EXEC sp_executesql N'
        create trigger t_d_SalesOrderHeader
        on Sales.SalesOrderHeader
        after delete
        as 
            delete Sales.SalesOrderHeader_new
            from Sales.SalesOrderHeader_new s
            join deleted d 
                on d.SalesOrderID = s.SalesOrderID ;';
END
GO

Move the data over in batches

set nocount on;
set transaction isolation level serializable;
set deadlock_priority LOW
 
declare @i int;
declare @new_i int = 0;
declare @largestSalesOrderId int;
declare @minBatchSize int = 5000
 
set identity_insert Sales.SalesOrderHeader_new on;
 
-- largest SalesOrderId to migrate 
select @largestSalesOrderId = isnull(max(SalesOrderID), 0)
from Sales.SalesOrderHeader
 
-- get @i to be just under the lowest SalesOrderId to migrate
select top 1 @i = so.SalesOrderId - 1
from Sales.SalesOrderHeader so
left join Sales.SalesOrderHeader_new so_new
	ON so.SalesOrderId = so_new.SalesOrderId
where so_new.SalesOrderId is null
ORDER BY so.SalesOrderId
 
-- batch loop
while @i < @largestSalesOrderId
begin
 
    declare @counter int = 0;
    declare @error int = 0;
 
    -- retry loop:
    while (1=1)
    begin
        begin try
            select top (@minBatchSize) @new_i = SalesOrderID
            from Sales.SalesOrderHeader
            where SalesOrderID > @i
            order by SalesOrderID asc;
 
            merge Sales.SalesOrderHeader_new as target
            using (
                    select SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate
                    from Sales.SalesOrderHeader
                    where SalesOrderID > @i
                    and SalesOrderID <= @new_i
                ) as source (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
            on target.SalesOrderID = source.SalesOrderID
            when not matched then
                insert (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
                values (source.SalesOrderID, source.RevisionNumber, source.OrderDate, source.DueDate, source.ShipDate, source.[Status], source.OnlineOrderFlag, source.PurchaseOrderNumber, source.AccountNumber, source.CustomerID, source.SalesPersonID, source.TerritoryID, source.BillToAddressID, source.ShipToAddressID, source.ShipMethodID, source.CreditCardID, source.CreditCardApprovalCode, source.CurrencyRateID, source.SubTotal, source.TaxAmt, source.Freight, source.Comment, source.rowguid, source.ModifiedDate);
 
            --success            
            select @i = @new_i, @error = 0;
            break;
 
        end try
        begin catch
            select @counter    = @counter + 1,
                @error = @@ERROR;
 
            if (@counter >= 5)
            begin
                -- this batch failed five times! Something's wrong
                raiserror (N'Could not copy Sales.SalesOrderHeader rows to Sales.SalesOrderHeader_new',
                    16, 1, @error);
                break;
            end
        end catch
    end 
 
    if @error <> 0
    begin
        -- an error has been raised. Discontinue.
        break;
    end
 
end
 
set identity_insert Sales.SalesOrderHeader_new off;
go

Do The Switch

set xact_abort on
set deadlock_priority high
set transaction isolation level serializable
begin transaction
IF EXISTS 
    (
        SELECT 1 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'Sales' 
        AND TABLE_NAME = 'SalesOrderHeader_new'
    )
BEGIN
    begin try
        -- rename pks and indexes from original table
        exec sp_rename N'Sales.PK_SalesOrderHeader_SalesOrderID', N'PK_SalesOrderHeader_old_SalesOrderID', N'OBJECT';
        exec sp_rename N'Sales.SalesOrderHeader.AK_SalesOrderHeader_rowguid', N'AK_SalesOrderHeader_old_rowguid', N'INDEX'
        exec sp_rename N'Sales.SalesOrderHeader.AK_SalesOrderHeader_SalesOrderNumber', N'AK_SalesOrderHeader_old_SalesOrderNumber', N'INDEX'
        exec sp_rename N'Sales.SalesOrderHeader.IX_SalesOrderHeader_CustomerID', N'IX_SalesOrderHeader_old_CustomerID', N'INDEX'
        exec sp_rename N'Sales.SalesOrderHeader.IX_SalesOrderHeader_SalesPersonID', N'IX_SalesOrderHeader_old_SalesPersonID', N'INDEX'
 
        -- rename checks and defaults
        exec sp_rename N'Sales.CK_SalesOrderHeader_Status', 'CK_SalesOrderHeader_old_Status', N'OBJECT';
        exec sp_rename N'Sales.CK_SalesOrderHeader_DueDate', 'CK_SalesOrderHeader_old_DueDate', N'OBJECT';
        exec sp_rename N'Sales.CK_SalesOrderHeader_ShipDate', 'CK_SalesOrderHeader_old_ShipDate', N'OBJECT';
        exec sp_rename N'Sales.CK_SalesOrderHeader_SubTotal', 'CK_SalesOrderHeader_old_SubTotal', N'OBJECT';
        exec sp_rename N'Sales.CK_SalesOrderHeader_TaxAmt', 'CK_SalesOrderHeader_old_TaxAmt', N'OBJECT';
        exec sp_rename N'Sales.CK_SalesOrderHeader_Freight', 'CK_SalesOrderHeader_old_Freight', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_RevisionNumber', 'DF_SalesOrderHeader_old_RevisionNumber', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_OrderDate', 'DF_SalesOrderHeader_old_OrderDate', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_Status', 'DF_SalesOrderHeader_old_Status', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_OnlineOrderFlag', 'DF_SalesOrderHeader_old_OnlineOrderFlag', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_SubTotal', 'DF_SalesOrderHeader_old_SubTotal', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_TaxAmt', 'DF_SalesOrderHeader_old_TaxAmt', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_Freight', 'DF_SalesOrderHeader_old_Freight', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_rowguid', 'DF_SalesOrderHeader_old_rowguid', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_ModifiedDate', 'DF_SalesOrderHeader_old_ModifiedDate', N'OBJECT';
 
        -- drop fks from original table
        alter table Sales.SalesOrderHeader
            drop constraint 
            FK_SalesOrderHeader_Address_BillToAddressID,
            FK_SalesOrderHeader_Address_ShipToAddressID,
            FK_SalesOrderHeader_CreditCard_CreditCardID,
            FK_SalesOrderHeader_CurrencyRate_CurrencyRateID,
            FK_SalesOrderHeader_Customer_CustomerID,
            FK_SalesOrderHeader_SalesPerson_SalesPersonID,
            FK_SalesOrderHeader_ShipMethod_ShipMethodID,
            FK_SalesOrderHeader_SalesTerritory_TerritoryID
 
        -- drop fks to original table
        alter table Sales.SalesOrderDetail
            drop constraint FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID;
 
        alter table Sales.SalesOrderHeaderSalesReason
            drop constraint FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID;
 
        -- drop triggers on original table
        drop trigger Sales.t_i_SalesOrderHeader;
        drop trigger Sales.t_u_SalesOrderHeader;
        drop trigger Sales.t_d_SalesOrderHeader;
 
        -- drop uSalesOrderHeader (but remember its definition)
        declare @triggersql nvarchar(max);
        select @triggersql = OBJECT_DEFINITION(object_id) from sys.triggers where name = 'uSalesOrderHeader';
        drop trigger Sales.uSalesOrderHeader; 
 
        -- rename original table to "old"
        exec sp_rename N'Sales.SalesOrderHeader', N'SalesOrderHeader_old', N'OBJECT';
 
        --rename the new indexes
        exec sp_rename N'Sales.PK_SalesOrderHeader_new_SalesOrderID', N'PK_SalesOrderHeader_SalesOrderID', N'OBJECT';
        exec sp_rename N'Sales.SalesOrderHeader_new.AK_SalesOrderHeader_new_rowguid', N'AK_SalesOrderHeader_rowguid', N'INDEX'
        exec sp_rename N'Sales.SalesOrderHeader_new.AK_SalesOrderHeader_new_SalesOrderNumber', N'AK_SalesOrderHeader_SalesOrderNumber', N'INDEX'
        exec sp_rename N'Sales.SalesOrderHeader_new.IX_SalesOrderHeader_new_CustomerID', N'IX_SalesOrderHeader_CustomerID', N'INDEX'
        exec sp_rename N'Sales.SalesOrderHeader_new.IX_SalesOrderHeader_new_SalesPersonID', N'IX_SalesOrderHeader_SalesPersonID', N'INDEX'
 
        -- rename the new checks and defaults
        exec sp_rename N'Sales.CK_SalesOrderHeader_new_Status', 'CK_SalesOrderHeader_Status', N'OBJECT';
        exec sp_rename N'Sales.CK_SalesOrderHeader_new_DueDate', 'CK_SalesOrderHeader_DueDate', N'OBJECT';
        exec sp_rename N'Sales.CK_SalesOrderHeader_new_ShipDate', 'CK_SalesOrderHeader_ShipDate', N'OBJECT';
        exec sp_rename N'Sales.CK_SalesOrderHeader_new_SubTotal', 'CK_SalesOrderHeader_SubTotal', N'OBJECT';
        exec sp_rename N'Sales.CK_SalesOrderHeader_new_TaxAmt', 'CK_SalesOrderHeader_TaxAmt', N'OBJECT';
        exec sp_rename N'Sales.CK_SalesOrderHeader_new_Freight', 'CK_SalesOrderHeader_Freight', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_new_RevisionNumber', 'DF_SalesOrderHeader_RevisionNumber', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_new_OrderDate', 'DF_SalesOrderHeader_OrderDate', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_new_Status', 'DF_SalesOrderHeader_Status', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_new_OnlineOrderFlag', 'DF_SalesOrderHeader_OnlineOrderFlag', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_new_SubTotal', 'DF_SalesOrderHeader_SubTotal', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_new_TaxAmt', 'DF_SalesOrderHeader_TaxAmt', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_new_Freight', 'DF_SalesOrderHeader_Freight', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_new_rowguid', 'DF_SalesOrderHeader_rowguid', N'OBJECT';
        exec sp_rename N'Sales.DF_SalesOrderHeader_new_ModifiedDate', 'DF_SalesOrderHeader_ModifiedDate', N'OBJECT';
 
        -- rename "new" table
        exec sp_rename N'Sales.SalesOrderHeader_new', N'SalesOrderHeader', N'OBJECT';
 
        --rename fks pointing out
        exec sp_rename N'Sales.FK_SalesOrderHeader_new_Address_BillToAddressID', N'FK_SalesOrderHeader_Address_BillToAddressID', N'OBJECT'
        exec sp_rename N'Sales.FK_SalesOrderHeader_new_Address_ShipToAddressID', N'FK_SalesOrderHeader_Address_ShipToAddressID', N'OBJECT' 
        exec sp_rename N'Sales.FK_SalesOrderHeader_new_CreditCard_CreditCardID', N'FK_SalesOrderHeader_CreditCard_CreditCardID', N'OBJECT'
        exec sp_rename N'Sales.FK_SalesOrderHeader_new_CurrencyRate_CurrencyRateID', N'FK_SalesOrderHeader_CurrencyRate_CurrencyRateID', N'OBJECT'
        exec sp_rename N'Sales.FK_SalesOrderHeader_new_Customer_CustomerID', N'FK_SalesOrderHeader_Customer_CustomerID', N'OBJECT'
        exec sp_rename N'Sales.FK_SalesOrderHeader_new_SalesPerson_SalesPersonID', N'FK_SalesOrderHeader_SalesPerson_SalesPersonID', N'OBJECT'
        exec sp_rename N'Sales.FK_SalesOrderHeader_new_ShipMethod_ShipMethodID', N'FK_SalesOrderHeader_ShipMethod_ShipMethodID', N'OBJECT'
        exec sp_rename N'Sales.FK_SalesOrderHeader_new_SalesTerritory_TerritoryID', N'FK_SalesOrderHeader_SalesTerritory_TerritoryID', N'OBJECT'
 
        -- re-add fks pointing in (without checks for now)
        alter table Sales.SalesOrderDetail with nocheck add
        constraint FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID foreign key (SalesOrderId)
            references Sales.SalesOrderHeader(SalesOrderId) 
 
        alter table Sales.SalesOrderHeaderSalesReason with nocheck add
        constraint FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID foreign key (SalesOrderId)
            references Sales.SalesOrderHeader(SalesOrderId)
 
        -- re-add trigger (use sp_executesql in order to keep in transaction)
        exec sp_executesql @triggersql;
    end try
 
    begin catch
        declare @error nvarchar(max) = ERROR_MESSAGE();
        select 'An error was encountered... Rolling back' as [message],
            @error as errorMessage
        raiserror (@error, 16, 1)
        rollback
    end catch
END
if @@TRANCOUNT > 0
    commit
GO

-- Comments (3)

April 17, 2012

Modifying Tables Online – Part 2: Implementation Example

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: "sql server", online schema changes, table migrations — Michael J. Swart @ 12:00 pm

SERIES: Modifying Tables Online

Part 1: Migration Strategy
Part 2: Implementation Example
Part 3: Same Example With Error Handling
Part 4: Testing
Part 5: Just One More Thing

So this is me putting the strategy into practice. I’ve decided to migrate a table from everyone’s favorite sample datatabase Adventureworks (2012 edition). And the change that I’m implementing is the addition of a rowversion column which is considered an offline operation:

-- adding a rowversion column offline
ALTER TABLE Sales.SalesOrderHeader
ADD [rowversion] rowversion;

The table I picked is Sales.SalesOrderHeader. I picked this table because it’s got foreign keys (columns pointing to records in other tables). And other tables have foreign keys pointing to SalesOrderHeader too. I also noticed that this table has a large variety of objects and settings which are dependent on it. Besides the foreign keys, other dependencies include

triggers
indexes (some UNIQUE and some not, some nonclustered and one not)
an identity column
default constraints
check constraints
computed columns

These make Sales.SalesOrderHeader interesting. So here’s the script (an implementation of the strategy already mentioned). You might be surprised at its size, it’s directly related to how interesting the table is. I don’t use any 2012-only features so you’ll be able to use these examples on SQL Server 2008 or SQL Server 2008 R2.

Create Staging Table

use AdventureWorks2012
 
-- new table
CREATE TABLE Sales.SalesOrderHeader_new(
	SalesOrderID int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	RevisionNumber tinyint NOT NULL,
	OrderDate datetime NOT NULL,
	DueDate datetime NOT NULL,
	ShipDate datetime NULL,
	[Status] tinyint NOT NULL,
	OnlineOrderFlag dbo.Flag NOT NULL,
	SalesOrderNumber  AS (isnull(N'SO'+CONVERT(nvarchar(23),SalesOrderID),N'*** ERROR ***')),
	PurchaseOrderNumber dbo.OrderNumber NULL,
	AccountNumber dbo.AccountNumber NULL,
	CustomerID int NOT NULL,
	SalesPersonID int NULL,
	TerritoryID int NULL,
	BillToAddressID int NOT NULL,
	ShipToAddressID int NOT NULL,
	ShipMethodID int NOT NULL,
	CreditCardID int NULL,
	CreditCardApprovalCode varchar(15) NULL,
	CurrencyRateID int NULL,
	SubTotal money NOT NULL,
	TaxAmt money NOT NULL,
	Freight money NOT NULL,
	TotalDue  AS (isnull((SubTotal+TaxAmt)+Freight,(0))),
	Comment nvarchar(128) NULL,
	rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
	ModifiedDate datetime NOT NULL,
	[Rowversion] rowversion NOT NULL, -- This is the new column!!
	constraint PK_SalesOrderHeader_new_SalesOrderID
		primary key clustered (SalesOrderID ASC),
	constraint FK_SalesOrderHeader_new_Address_BillToAddressID
		foreign key(BillToAddressID) references Person.[Address] (AddressID),
	constraint FK_SalesOrderHeader_new_Address_ShipToAddressID
		foreign key(ShipToAddressID) references Person.[Address] (AddressID),
	constraint FK_SalesOrderHeader_new_CreditCard_CreditCardID
		foreign key(CreditCardID) references Sales.CreditCard (CreditCardID),
	constraint FK_SalesOrderHeader_new_CurrencyRate_CurrencyRateID
		foreign key(CurrencyRateID) references Sales.CurrencyRate (CurrencyRateID),
	constraint FK_SalesOrderHeader_new_Customer_CustomerID
		foreign key(CustomerID) references Sales.Customer (CustomerID),
	constraint FK_SalesOrderHeader_new_SalesPerson_SalesPersonID
		foreign key(SalesPersonID) references Sales.SalesPerson (BusinessEntityID),
	constraint FK_SalesOrderHeader_new_SalesTerritory_TerritoryID
		foreign key(TerritoryID) references Sales.SalesTerritory (TerritoryID),
	constraint FK_SalesOrderHeader_new_ShipMethod_ShipMethodID
		foreign key(ShipMethodID) references Purchasing.ShipMethod (ShipMethodID),
	constraint CK_SalesOrderHeader_new_DueDate
		check (DueDate>=OrderDate),
	constraint CK_SalesOrderHeader_new_Freight
		check (Freight>=0.00),
	constraint CK_SalesOrderHeader_new_ShipDate
		check (ShipDate>=OrderDate or ShipDate is null),
	constraint CK_SalesOrderHeader_new_Status
		check ([Status]>=0 and [Status]=0.00),
	constraint CK_SalesOrderHeader_new_TaxAmt
		check (TaxAmt>=0.00)
)
 
-- add default constraints
alter table Sales.SalesOrderHeader_new add
constraint DF_SalesOrderHeader_new_RevisionNumber
	default 0 for RevisionNumber,
constraint DF_SalesOrderHeader_new_OrderDate
	default getdate() for OrderDate,
constraint DF_SalesOrderHeader_new_Status
	default 1 for [Status],
constraint DF_SalesOrderHeader_new_OnlineOrderFlag
	default 1 for OnlineOrderFlag,
constraint DF_SalesOrderHeader_new_SubTotal
	default 0.00 for SubTotal,
constraint DF_SalesOrderHeader_new_TaxAmt
	default 0.00 for TaxAmt,
constraint DF_SalesOrderHeader_new_Freight
	default 0.00 for Freight,
constraint DF_SalesOrderHeader_new_rowguid
	default newid() for rowguid,
constraint DF_SalesOrderHeader_new_ModifiedDate
	default getdate() for ModifiedDate
 
	-- two nc ak
create unique nonclustered index AK_SalesOrderHeader_new_rowguid
	on Sales.SalesOrderHeader_new(rowguid ASC)
 
create unique nonclustered index AK_SalesOrderHeader_new_SalesOrderNumber
	on Sales.SalesOrderHeader_new(SalesOrderNumber)
 
	-- two nc ix
create nonclustered index IX_SalesOrderHeader_new_CustomerID
	on Sales.SalesOrderHeader_new(CustomerID)
 
create nonclustered index IX_SalesOrderHeader_new_SalesPersonID
	on Sales.SalesOrderHeader_new(SalesPersonID)
GO

Add Triggers

/*
create triggers to maintain new table
*/
 
use AdventureWorks2012;
go
 
create trigger t_i_SalesOrderHeader
on Sales.SalesOrderHeader
after insert
as
	set identity_insert Sales.SalesOrderHeader_new on;
 
	insert SalesOrderHeader_new(SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
	select SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate
	from inserted
 
	set identity_insert Sales.SalesOrderHeader_new off;
go
 
create trigger t_u_SalesOrderHeader
on Sales.SalesOrderHeader
after update
as
	-- assuming pk values are never altered.
	update Sales.SalesOrderHeader_new
	set
		RevisionNumber = i.RevisionNumber,
		OrderDate  = i.OrderDate,
		DueDate  = i.DueDate,
		ShipDate  = i.ShipDate,
		[Status]  = i.[Status],
		OnlineOrderFlag  = i.OnlineOrderFlag,
		PurchaseOrderNumber  = i.PurchaseOrderNumber,
		AccountNumber  = i.AccountNumber,
		CustomerID  = i.CustomerID,
		SalesPersonID  = i.SalesPersonID,
		TerritoryID  = i.TerritoryID,
		BillToAddressID  = i.BillToAddressID,
		ShipToAddressID  = i.ShipToAddressID,
		ShipMethodID  = i.ShipMethodID,
		CreditCardID  = i.CreditCardID,
		CreditCardApprovalCode = i.CreditCardApprovalCode,
		CurrencyRateID  = i.CurrencyRateID,
		SubTotal  = i.SubTotal,
		TaxAmt  = i.TaxAmt,
		Freight  = i.Freight,
		Comment  = i.Comment,
		rowguid  = i.rowguid,
		ModifiedDate = i.ModifiedDate
	from Sales.SalesOrderHeader_new s
	join inserted i
		on s.SalesOrderID = i.SalesOrderID;
go
 
create trigger t_d_SalesOrderHeader
on Sales.SalesOrderHeader
after delete
as
	delete Sales.SalesOrderHeader_new
	from Sales.SalesOrderHeader_new s
	join deleted d
		on d.SalesOrderID = s.SalesOrderID ;
go

Move the data over in batches

/* 
populate new table in batches
*/
set nocount on;
set transaction isolation level serializable;
 
declare @i int = 0;
declare @new_i int = 0;
declare @rowcount int = -1;
declare @minBatchSize int = 5000
 
set identity_insert Sales.SalesOrderHeader_new on;
 
while @rowcount <> 0
begin
	select top (@minBatchSize) @new_i = SalesOrderID
	from Sales.SalesOrderHeader
	where SalesOrderID > @i
	order by SalesOrderID asc;
 
	merge Sales.SalesOrderHeader_new as target
	using (
			select SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate
			from Sales.SalesOrderHeader
			where SalesOrderID > @i
			and SalesOrderID <= @new_i
		) as source (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
	on target.SalesOrderID = source.SalesOrderID
	when not matched then
		insert (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
		values (source.SalesOrderID, source.RevisionNumber, source.OrderDate, source.DueDate, source.ShipDate, source.[Status], source.OnlineOrderFlag, source.PurchaseOrderNumber, source.AccountNumber, source.CustomerID, source.SalesPersonID, source.TerritoryID, source.BillToAddressID, source.ShipToAddressID, source.ShipMethodID, source.CreditCardID, source.CreditCardApprovalCode, source.CurrencyRateID, source.SubTotal, source.TaxAmt, source.Freight, source.Comment, source.rowguid, source.ModifiedDate);
 
	set @rowcount = @@ROWCOUNT
 
	set @i = @new_i
 
end
 
set identity_insert Sales.SalesOrderHeader_new off;

Do The Switch

-- rename pks and indexes from original table
exec sp_rename N'Sales.PK_SalesOrderHeader_SalesOrderID', N'PK_SalesOrderHeader_old_SalesOrderID', N'OBJECT';
exec sp_rename N'Sales.SalesOrderHeader.AK_SalesOrderHeader_rowguid', N'AK_SalesOrderHeader_old_rowguid', N'INDEX'
exec sp_rename N'Sales.SalesOrderHeader.AK_SalesOrderHeader_SalesOrderNumber', N'AK_SalesOrderHeader_old_SalesOrderNumber', N'INDEX'
exec sp_rename N'Sales.SalesOrderHeader.IX_SalesOrderHeader_CustomerID', N'IX_SalesOrderHeader_old_CustomerID', N'INDEX'
exec sp_rename N'Sales.SalesOrderHeader.IX_SalesOrderHeader_SalesPersonID', N'IX_SalesOrderHeader_old_SalesPersonID', N'INDEX'
 
-- rename checks and defaults
exec sp_rename N'Sales.CK_SalesOrderHeader_Status', 'CK_SalesOrderHeader_old_Status', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_DueDate', 'CK_SalesOrderHeader_old_DueDate', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_ShipDate', 'CK_SalesOrderHeader_old_ShipDate', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_SubTotal', 'CK_SalesOrderHeader_old_SubTotal', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_TaxAmt', 'CK_SalesOrderHeader_old_TaxAmt', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_Freight', 'CK_SalesOrderHeader_old_Freight', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_RevisionNumber', 'DF_SalesOrderHeader_old_RevisionNumber', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_OrderDate', 'DF_SalesOrderHeader_old_OrderDate', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_Status', 'DF_SalesOrderHeader_old_Status', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_OnlineOrderFlag', 'DF_SalesOrderHeader_old_OnlineOrderFlag', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_SubTotal', 'DF_SalesOrderHeader_old_SubTotal', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_TaxAmt', 'DF_SalesOrderHeader_old_TaxAmt', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_Freight', 'DF_SalesOrderHeader_old_Freight', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_rowguid', 'DF_SalesOrderHeader_old_rowguid', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_ModifiedDate', 'DF_SalesOrderHeader_old_ModifiedDate', N'OBJECT';
 
-- drop fks from original table
alter table Sales.SalesOrderHeader
	drop constraint
	FK_SalesOrderHeader_Address_BillToAddressID,
	FK_SalesOrderHeader_Address_ShipToAddressID,
	FK_SalesOrderHeader_CreditCard_CreditCardID,
	FK_SalesOrderHeader_CurrencyRate_CurrencyRateID,
	FK_SalesOrderHeader_Customer_CustomerID,
	FK_SalesOrderHeader_SalesPerson_SalesPersonID,
	FK_SalesOrderHeader_ShipMethod_ShipMethodID,
	FK_SalesOrderHeader_SalesTerritory_TerritoryID
 
-- drop fks to original table
alter table Sales.SalesOrderDetail
	drop constraint FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID;
 
alter table Sales.SalesOrderHeaderSalesReason
	drop constraint FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID;
 
-- drop triggers on original table
drop trigger Sales.t_i_SalesOrderHeader;
drop trigger Sales.t_u_SalesOrderHeader;
drop trigger Sales.t_d_SalesOrderHeader;
 
-- drop uSalesOrderHeader (but remember its definition)
declare @triggersql nvarchar(max);
select @triggersql = OBJECT_DEFINITION(object_id) from sys.triggers where name = 'uSalesOrderHeader';
drop trigger Sales.uSalesOrderHeader; 
 
-- rename original table to "old"
exec sp_rename N'Sales.SalesOrderHeader', N'SalesOrderHeader_old', N'OBJECT';
 
--rename the new indexes
exec sp_rename N'Sales.PK_SalesOrderHeader_new_SalesOrderID', N'PK_SalesOrderHeader_SalesOrderID', N'OBJECT';
exec sp_rename N'Sales.SalesOrderHeader_new.AK_SalesOrderHeader_new_rowguid', N'AK_SalesOrderHeader_rowguid', N'INDEX'
exec sp_rename N'Sales.SalesOrderHeader_new.AK_SalesOrderHeader_new_SalesOrderNumber', N'AK_SalesOrderHeader_SalesOrderNumber', N'INDEX'
exec sp_rename N'Sales.SalesOrderHeader_new.IX_SalesOrderHeader_new_CustomerID', N'IX_SalesOrderHeader_CustomerID', N'INDEX'
exec sp_rename N'Sales.SalesOrderHeader_new.IX_SalesOrderHeader_new_SalesPersonID', N'IX_SalesOrderHeader_SalesPersonID', N'INDEX'
 
-- rename the new checks and defaults
exec sp_rename N'Sales.CK_SalesOrderHeader_new_Status', 'CK_SalesOrderHeader_Status', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_new_DueDate', 'CK_SalesOrderHeader_DueDate', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_new_ShipDate', 'CK_SalesOrderHeader_ShipDate', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_new_SubTotal', 'CK_SalesOrderHeader_SubTotal', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_new_TaxAmt', 'CK_SalesOrderHeader_TaxAmt', N'OBJECT';
exec sp_rename N'Sales.CK_SalesOrderHeader_new_Freight', 'CK_SalesOrderHeader_Freight', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_RevisionNumber', 'DF_SalesOrderHeader_RevisionNumber', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_OrderDate', 'DF_SalesOrderHeader_OrderDate', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_Status', 'DF_SalesOrderHeader_Status', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_OnlineOrderFlag', 'DF_SalesOrderHeader_OnlineOrderFlag', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_SubTotal', 'DF_SalesOrderHeader_SubTotal', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_TaxAmt', 'DF_SalesOrderHeader_TaxAmt', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_Freight', 'DF_SalesOrderHeader_Freight', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_rowguid', 'DF_SalesOrderHeader_rowguid', N'OBJECT';
exec sp_rename N'Sales.DF_SalesOrderHeader_new_ModifiedDate', 'DF_SalesOrderHeader_ModifiedDate', N'OBJECT';
 
-- rename "new" table
exec sp_rename N'Sales.SalesOrderHeader_new', N'SalesOrderHeader', N'OBJECT';
 
--rename fks pointing out
exec sp_rename N'Sales.FK_SalesOrderHeader_new_Address_BillToAddressID', N'FK_SalesOrderHeader_Address_BillToAddressID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_Address_ShipToAddressID', N'FK_SalesOrderHeader_Address_ShipToAddressID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_CreditCard_CreditCardID', N'FK_SalesOrderHeader_CreditCard_CreditCardID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_CurrencyRate_CurrencyRateID', N'FK_SalesOrderHeader_CurrencyRate_CurrencyRateID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_Customer_CustomerID', N'FK_SalesOrderHeader_Customer_CustomerID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_SalesPerson_SalesPersonID', N'FK_SalesOrderHeader_SalesPerson_SalesPersonID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_ShipMethod_ShipMethodID', N'FK_SalesOrderHeader_ShipMethod_ShipMethodID', N'OBJECT'
exec sp_rename N'Sales.FK_SalesOrderHeader_new_SalesTerritory_TerritoryID', N'FK_SalesOrderHeader_SalesTerritory_TerritoryID', N'OBJECT'
 
-- re-add fks pointing in (without checks for now)
alter table Sales.SalesOrderDetail with nocheck add
constraint FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID foreign key (SalesOrderId)
	references Sales.SalesOrderHeader(SalesOrderId) 
 
alter table Sales.SalesOrderHeaderSalesReason with nocheck add
constraint FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID foreign key (SalesOrderId)
	references Sales.SalesOrderHeader(SalesOrderId)
 
-- re-add trigger
exec sp_executesql @triggersql;

-- Comments (3)

April 16, 2012

Modifying Tables Online – Part 1: Migration Strategy

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: "sql server", online schema changes, table migrations — Michael J. Swart @ 12:00 pm

SERIES: Modifying Tables Online

Part 1: Migration Strategy
Part 2: Implementation Example
Part 3: Same Example With Error Handling
Part 4: Testing
Part 5: Just One More Thing

Database Schema Changes

Database schema changes are inevitable. Some of these changes can be performed quickly (like altering ntext columns to nvarchar columns). Changes like these don’t have to process every single row in the table and we say that these quick changes are online.
Of course that means there are also such things as offline changes. These are changes that need to lock the table and process each row (like adding a persisted computed column to a table). Offline changes are sometimes okay when:

You have scheduled maintenance window to do work
The table isn’t that large or
the estimated amount of downtime is tolerable

But sometimes the estimated downtime is not tolerable and you want your changes to be online. Microsoft has improved and continue to improve SQL Server’s online features. For example:

Since 2005, we’ve had online index rebuilds (and even that has improved in SQL Server 2012)
Remus Rusanu talks about adding non-nullable columns as an online operation.

There are a few operations that are still not online. Here’s an example of one of them:

ALTER TABLE [Tablename]
ADD Rowversion [Rowversion] NOT NULL

The downtime for this table change can last seconds or minutes depending on the size of the table. If your business can’t tolerate that downtime, what options are there? I’m going to explain one strategy which helps me with these following goals:

I want to add a rowversion column to a table. (But the strategy applies to many other offline changes).
The straightforward alter statement would lock the table for longer than we can tolerate
My change can place an exclusive lock on the table, but only for a short amount of time. This means that the change should work concurrently with quick transactions, but not necessarily with large bulk transactions.
It would be an advantage if this were a database-only solution because while I might understand the applications and processes that query my database, I don’t necessarily have as much control over when or how these queries are sent.

So here’s something that might work:

The Strategy

The strategy is fairly straightforward:

A) I create a staging table which will store the same data as the original table but with the new rowversion column. The staging table is empty at first:

B) Then I add triggers to the original table to keep the staging table up to date. Any changes in the original table will also be applied to matching data in the staging table.

C) Fill the staging table a little at a time. This processing may take time, but the original table is still available to others.

D) Finally remove triggers and rename the tables:

Some Notes

Dependencies I didn’t mention it yet, but we also have to worry about table dependencies. Tables can have a lot of dependencies (triggers, constraints, indexes etc…). You can create dependencies for the staging table, and rename them as well during the switch.

Step D, the Switch: The last step is the tricky one. It’s the one that takes a schema modification (Sch-M) lock on the tables. A Sch-M lock on the table is incompatible with every other kind of lock. So that means that no in-flight transactions are allowed to use the table during the switch. SQL Server handles it pretty well, but if there are long running queries on this table, there will be blocking.

What’s next?

In part 2, I implement this strategy for Adventureworks’ Sales.SalesOrderHeader table which seems to demonstrate every kind of table dependency SQL Server allows.
In part 3, I throw in error handling code to make the script more robust. So that if things go wrong, the application doesn’t notice and I don’t lose data.
In part 4, I explain the testing I do. It gives me more confidence that I don’t miss an update or a delete somehow.

Thanks

I posted a question on dba.stackexchange.com to look for some strategies. The answer I got there (thanks Brent!) is slightly different than the solution I’m describing here. Brent described a solution which used a view that UNION’s the staging table and the original table. It uses less space than the solution I describe, but it’s got some extra gotchas.

Some of this work was done in the course of my regular job. So thanks to Desire2Learn for letting me share some of the lessons I learned there. By the way, we’re hiring.

-- Comments (6)