Over the years I have worked on many projects using SQL Server with applications in C#, Java, Python, and Scala. Even though the programming languages and libraries we use for managing migration scripts might change, the basic principles of how we write migration scripts for clustered applications using a SQL Server instance have remained the same.
There are very few occasions (if any) where a schema change to a database should require downtime, so in this article I have outlined step by step instructions on how to apply some of the more common types of schema changes without any need to take the database offline. Since I have been following these steps for years but never wrote them down anywhere, I finally decided to document them in this article. I hope this helps!
Why Backwards Compatible Schema Changes Matter
Anytime you are making a change to a SQL database such as adding a column, renaming a table, etc, you must always ensure your change is backwards compatible with the currently deployed version of the application for two reasons:
- Clustered Application. Our application runs in a clustered environment. So when the application is deployed, it will be deployed one node at a time which means there will be a short period of time where your new version of the application with the new version of the database will be running side-by-side with an older version of the application that is also running on the new version of the database
- Rolling Back. If the application deploy needs to be rolled back, we want to be in a state where the database changes added are backwards compatible with the previous version, to make rolling back the application not require undoing database changes. Thinking this way is important in a clustered environment, since if you started the rollback of your application one node at a time and rolled back your database changes, then your nodes running the newer version of the application would break until they had all been rolled back.
Common Migration Patterns
Note: In the steps for each of these patterns, I am assuming each script is being run automatically by some migration script management library (For example: Flyway) when you are releasing your application.
Rename a Table
Scenario: Rename an existing table while remaining backwards compatible
Pattern: Use a database synonym to maintain backwards compatibility with the old table name
Steps:
- Add script that renames the table and also creates a synonym
- Update code to use the new table name
- Release app
- Create an issue for a future sprint to drop the synonym
Here is an example migration script that renames the table and creates a synonym:
IF EXISTS (
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = N'MySchema' AND TABLE_NAME = N'MyOldTable')
BEGIN
EXEC sp_rename 'MySchema.MyOldTable', 'MyNewTable';
CREATE SYNONYM MySchema.MyOldTable
FOR MySchema.MyNewTable;
END
GO
In a future sprint create a script to drop the old synonym:
IF EXISTS(SELECT * FROM sys.synonyms WHERE name = 'MyOldTable')
BEGIN
DROP SYNONYM [MySchema].[MyOldTable]
END
GO
Move a Table to a Different Schema
Scenario: Move an existing table to a different schema while remaining backwards compatible
Pattern: Use a database synonym to maintain backwards compatibility with the old schema name for the table
Steps:
- Add script that both moves the table to the new schema and also adds a synonym
- Update code to use new schema name
- Release App
- Create an issue for a future sprint to drop the synonym
Here is an example migration script that changes the table schema and creates a synonym:
IF EXISTS (
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = N'MyOldSchema' AND TABLE_NAME = N'MyTable')
BEGIN
ALTER SCHEMA MyNewSchema
TRANSFER MyOldSchema.MyTable;
CREATE SYNONYM MyOldSchema.MyTable
FOR MyNewSchema.MyTable;
END
GO
In a future sprint create a script to drop the old synonym:
IF EXISTS(SELECT * FROM sys.synonyms WHERE name = 'MyTable')
BEGIN
DROP SYNONYM [MyOldSchema].[MyTable]
END
GO
Alter Columns Used in an Index
Scenario: Move an index from being on columns A and B to being on columns A and C while remaining backwards compatible
Pattern: Rather than altering the existing index, create a new index, then drop the old index in a future release
Steps:
- Add script to create a new index with the columns you want
- Update code to have queries use the new index (If needed)
- Release app
- Create an issue for a future sprint to drop the old index
Example script to add the new index:
IF NOT EXISTS(
SELECT * FROM sys.indexes
WHERE name='IX_MySchema_MyTable_ColumnA_ColumnC')
BEGIN
CREATE UNIQUE NONCLUSTERED INDEX IX_MySchema_MyTable_ColumnA_ColumnC
ON [MySchema].[MyTable]([ColumnA],[ColumnC])
END
GO
In a future sprint, add a script to drop the old index:
IF EXISTS(
SELECT * FROM sys.indexes
WHERE name='IX_MySchema_MyTable_ColumnA_ColumnB')
BEGIN
DROP INDEX IX_MySchema_MyTable_ColumnA_ColumnB
ON [MySchema].[MyTable]
END
GO
Add a New Non-Nullable Column
Scenario: Add a new non-nullable column to an existing table where the table already has data while remaining backwards compatible
Pattern: Add the new column as nullable first, populate the data, then make it non-nullable later
Steps:
- Add script that adds the column as a new nullable column
- Update code to start writing to, but not reading from, this new column
- Release app
- Add script to backfill any rows where the column value is still null
- Add script to make the column non-nullable
- Update code to start reading from the new non-nullable column
- Release app
Example script to add the nullable column:
IF NOT EXISTS(
SELECT * FROM sys.columns
WHERE Name = N'MyColumn' AND Object_ID = Object_ID(N'MySchema.MyTable'))
BEGIN
ALTER TABLE MySchema.MyTable
ADD [MyColumn] [uniqueIdentifier] NULL
END
GO
In a future sprint, once all rows in the table have this column populated:
ALTER TABLE [MySchema].[MyTable]
ALTER COLUMN MyColumn [uniqueIdentifier] NOT NULL
GO
Drop a Non-Nullable Column
Scenario: Drop a non-nullable column from an existing table while remaining backwards compatible
Pattern: Alter the column to make it nullable first, remove usages, then drop the column
Steps:
- Add script to make the existing column nullable
- Update code to stop reading from the column
- Release app
- Update code to stop writing to the column
- Release app
- Create an issue for a future sprint to drop the column
Example script to make the column nullable:
IF EXISTS(
SELECT * FROM sys.columns
WHERE Name = N'MyColumn' AND Object_ID = Object_ID(N'MySchema.MyTable'))
BEGIN
ALTER TABLE [MySchema].[MyTable]
ALTER COLUMN MyColumn [uniqueIdentifier] NULL
END
GO
In a future sprint, once we will nolonger rollback to a version that references this column, add a script to drop it:
IF EXISTS(
SELECT * FROM sys.columns
WHERE Name = N'MyColumn' AND Object_ID = Object_ID(N'MySchema.MyTable'))
BEGIN
ALTER TABLE MySchema.MyTable
DROP COLUMN [MyColumn]
END
GO
Rename a Non-Nullable Column
Scenario: Rename an existing non-nullable column while remaining backwards compatible
Pattern: Rather than altering the name of the existing column, add a new column with the name you want, populate the data, then drop the old column after all usages have been moved to the new column
Steps:
- Add script that adds the column as a new nullable column (it must be nullable temporarily)
- Update code to start writing to the new column, while still writing to and reading from the old column
- Release app
- Add script to copy all values from the old column to the new column
- Update code to start reading from the new column
- Release app
- Add script to make old column nullable
- Update code to stop writing to the old column
- Add script to make the new column non-nullable (now we can enforce the new column is non-nullable)
- Release app
- Create an issue for a future sprint to drop the old column
Example script to add the nullable column (Note, it must be nullable until we are writing to it and have migrated the old data):
IF NOT EXISTS(
SELECT * FROM sys.columns
WHERE Name = N'NewColumn' AND Object_ID = Object_ID(N'MySchema.MyTable'))
BEGIN
ALTER TABLE MySchema.MyTableADD [NewColumn] [varchar(100)] NULL
END
GO
Example script to copy the values from the old column to the new column:
NOTE: If the table has a lot of traffic, it may be a better approach to migrate the data using a cursor (Cursor Example):
UPDATE MySchema.MyTable
SET NewColumn = OldColumn
WHERE OldColumn IS NOT NULL AND NewColumn IS NULL
Example script to update the new column to be non-nullable (now that we have populated the data for the new column in all rows):
IF EXISTS(
SELECT * FROM sys.columns
WHERE Name = N'NewColumn' AND Object_ID = Object_ID(N'MySchema.MyTable'))
BEGIN
ALTER TABLE MySchema.MyTable
ALTER COLUMN [NewColumn] [varchar(100)] NOT NULL
END
GO
In a future sprint, once we will no longer rollback to a version that references the old column, add a script to drop it:
IF EXISTS(
SELECT * FROM sys.columns
WHERE Name = N'OldColumn' AND Object_ID = Object_ID(N'MySchema.MyTable'))
BEGIN
ALTER TABLE MySchema.MyTable
DROP COLUMN [OldColumn]
END
GO
Summary
There are many types of schema change scenarios I did not touch on in this article. I just wanted to focus on the most common ones and show that for many types of schema changes there is always a way to do it that does not involve taking the database offline.
I hope that helps!
My Adventures in Coding 