Page 60 - MSDN Magazine, November 2017

Figure 3 Table Schema
scripts based on the targeted database version. Check out the flowchart in Figure 4 for an understanding of the logic involved. At the start of the AdventureWorks.Database.Migration project, I check the current version of the database and based on that run the migration scripts all the way to the latest version. Here’s the code snip- pet I use to decide the migration path, which I’ll refer to as Script 1:
DECLARE @currentDBVersion BIGINT = NULL;
-- Get the current version of the database.
SELECT TOP 1 @currentDBVersion = Id FROM [internal].[DatabaseVersion] ORDER BY [DatabaseVersionId] DESC
-- Jump to the incremental migration scripts based on the current version. IF @currentDBVersion = 1 GOTO Version11
ELSE IF @currentDBVersion = 2 GOTO Version12
ELSE
RETURN
Now that I’ve shown how the migration scripts run, let’s drive the migration with a few fictional scenarios that help illustrate what’s going on. I’ll discuss two version changes from the base project that I created earlier.
Version 1.1: This is the first change on top of the base proj- ect I created. The changes are available in the v11 branch of the Continuous Data Migration project on GitHub. The changes I’ve committed in this version are as follows:
• Inserted a new IsEmployee column to [HumanResources].[Em- ployee] after the JobTitle column.
• Changed the [Person].[AddressType] name from Main Office to Office.
• Changed SPs (no need to include in migration project).
• New SPs (no need to include in migration project).
All these changes are made into the regular project Adven- tureWorks.Database as is, along with the new version row into internal.DatabaseVersion. This facilitates any fresh deployment to incorporate the latest changes. For any existing database with the base version to be upgraded to v1.1, I need to implement the same changes into the migration project. To do so, I segregate it into two sections: the schema change and the data change. Inserting a new column called IsEmployee is a schema change, while changing
CREATE TABLE [internal].[DatabaseVersion] (
[DatabaseVersionId] INT IDENTITY(1,1) NOT NULL,
[Major] INT
[Minor] INT
[Build] INT
[Revision] INT [CreatedBy] NVARCHAR (256)
NOT NULL, NOT NULL, NOT NULL, NOT NULL,
CONSTRAINT [DFDatabaseVersionCreatedBy] DEFAULT ('') NOT NULL, [CreatedOn] DATETIME
CONSTRAINT [DFDatabaseVersionCreatedOn] DEFAULT (GETUTCDATE()) NOT NULL, [ModifiedBy] NVARCHAR (256)
CONSTRAINT [DFDatabaseVersionModifiedBy] DEFAULT ('') NOT NULL, [ModifiedOn] DATETIME
CONSTRAINT [DFDatabaseVersionModifiedOn] DEFAULT (GETUTCDATE()) NOT NULL, CONSTRAINT [PKDatabaseVersion] PRIMARY KEY CLUSTERED ([DatabaseVersionId] ASC)
);GO
as the label for the change. If the current version is 1.0.0.0, and I introduce a migration that fixes the Gender flag issue by reversing the values, I will add appropriate scripts to make that change and add a new entry to the table with the version saying 1.1.0128.212.
Migration
As I already discussed, Visual Studio can do incremental deploy- ments, but not with breaking changes. So as I design the migration, I need to keep that in mind and work around the limitation.
The first step is to create a separate project to target the migra- tion. With respect to the sample in Figure 3, I create a new database project called AdventureWorks.Database.Migration. This migration project targets two types of scripts. The first is the data migration script, which needs to be run if any data movement or update occurs. The second script takes care of breaking schema changes that Visual Studio and SqlPackage.exe cannot handle. Both these scripts go into the project as a post-deployment script. There are no compilable scripts in this project.
To better understand the scenario, let’s discuss everything in relation to the AdventureWorks sample. I’ve uploaded the source code for this into my GIT repository at github.com/Jebarson/Continuous- DataMigration. The master branch incorporates the base project that I’ve imported and created from the database, as mentioned earlier.
Before I venture into the scenario, I’d like to explain how the migration works. As I discussed in the Versioning section, I’m versioning every single change released by adding a new row into internal.DatabaseVersion. Inside the AdventureWorks.Database.Mi- gration project, I write the logic to execute appropriate migration
AddressType from Main Office to Office is a data change.
The schema change is something Visual Studio can do. However, it can only append the column, and that’s something I don’t want. To overcome this limitation, I must generate a script to first drop all the dependencies (indexes, constraints, foreign keys and the like) of the table Employee, and then create a temporary table with the new column in the correct order with all the dependencies. Then, I can move the data from the Employee table to the temp table, drop the Employee table and finally rename the temp table to Employee. This script is available in the v11 branch of my Continuous Data Migration project on GitHub, in the file SchemaChangeScript.sql. The data change just alters a value of the record from Main Office to Office and, therefore, I can script an update query to accomplish this. Check out the DevOps
Start Migration
Get the Current Version of Database
Is Version < 1.1
No
No
End Migration
Is Version < 1.2
Yes
Migrate Database to 1.1
Yes
Migrate Database to 1.2
Figure 4 The Migration Logic 56 msdn magazine