Page 62 - MSDN Magazine, November 2017

Figure 5 Migration Project Cheat List
mentioned “no need to include in migration project.” That’s because Visual Studio is able to deploy them incrementally. Which begs the question: What changes qualify for a migra- tion project and what changes don’t?
You can look at the handy cheat list in Figure 5, which will help you decide whether to script the migration or not. Note that there might be more items you come across that could be added to this list .
OK, enough about generating the migration scripts. Time to move on to the deployment. When deploying a fresh instance of the latest version of an existing database, there’s no need for migration. In the example I’ve been presenting, all you need to deploy is the AdventureWorks.Database. You can do this from either Visual Studio (via publish) or using SqlPackage.exe. Here’s the command
for deploying the database using SqlPackage.exe:
SqlPackage.exe /Action:Publish /SourceFile:"AdventureWorks.Database. dacpac" /tdn:<<DatabaseName>> /tsn:"<<SQL Instance>>"
3 Common Migration Problems and Fixes
Change
Triage
New table/view/stored procedure/ object
Leverage Visual Studio
Change in view/stored procedure/ function
Leverage Visual Studio
Change in user-defined type
Drop all related stored procedures of the UDT. This is a workaround for the bug described earlier.
Addition of new column to table
Script a migration from the existing table to a new table
with the correct column order (see github.com/Jebarson/ ContinuousDataMigration). This isn’t required if you’re adding a nullable column and the order of the column is immaterial.
Normalization or de-normalization of table
Script a migration to either split or merge based on the requirement. This is similar to the script created in v1.2.
Change in data
Script out the data change.
DataChangeScript.sql file in the v11 branch of the Continuous Data Migration project on GitHub.
When the migration project is run on top of the existing AdventureWorks.Database, the code from Script 1 will send the execution to a script that calls the schema and data change script, as shown in the following snippet, which I’ll call Script 2:
-- Script to migrate to v1.1
Version11:
:r .\\Scripts\\Migration\\V11\\SchemaChangeScript.sql :r .\\Scripts\\Migration\\V11\\DataChangeScript.sql
EXEC [internal].[CreateDatabaseVersion] @id = 2, @major = 1, @minor = 1, @build = 0128,
@revision = 212
Version 1.2: This is the latest change committed on top of v1.1. The same changes are available in the v12 branch of the project on GitHub. The changes in this version are as follows:
• Changed the IsEmployee in [HumanResources].[Employee] to EmployeeType, referring a new table to [HumanResourc- es].[EmployeeType].
• Changed SPs (no need to include in migration project).
• New table (no need to include in migration project). Similar to v1.1, I’ve also made the changes on the regular proj- ect AdventureWorks.Database along with a new entry into internal.DatabaseVersion. As you can see, IsEmployee is now changed to EmployeeType to accommodate more types of employees. To achieve this, I follow the same pattern I did in v1.1. However, I need to write the data migration for the new column based on the value of the earlier column. The schema change script is writ- ten in the file SchemaChangeScript.sql in the v12 branch of the
Continuous Data Migration project on GitHub.
Here’s the script I’ve included in the project to migrate to v1.2,
which I’ll call Script 3:
-- Script to migrate to v1.2
Version12:
:r .\\Scripts\\Migration\\V12\\SchemaChangeScript.sql
EXEC [internal].[CreateDatabaseVersion] @id = 3, @major = 1, @minor = 2, @build = 0414,
@revision = 096
As I mentioned earlier, Visual Studio is partially capable of driv- ing an incremental deployment, but I’m targeting only the items that Visual Studio lacks with the scripts I’ve generated up to this point. You may have noticed that for certain items in both v1.1 and v1.2, I
Continuous data migration can produce a lot of benefits, but it’s not without its challenges. Here are some com- mon issues you might expect to encounter while implementing this solution, and ways to address them.
Build server throws the invalid object name runtime error:
You might encounter this error in the migration project, when your recent version of a migration script has removed an object, but the object is referred in an earlier version script. The resolution is to write queries as sp_executesql ‘<<your migration script here>>.’ For example:
EXEC sp_executesql 'ALTER TABLE Employee ADD NewCol INT'
Out of control migration scripts and version overload:
It’s a good idea to always set a minimum target version for migration. Doing so limits the scope of your migration scripts and helps ensure that they don’t become too difficult to maintain.
Implementing with a production database:
In case you want to implement this solution in a database that’s already in production, include the definition of internal.Database­ Version and its version entries. Change the “Script 1” to see if the table internal.DatabaseVersion exists and if it doesn’t, direct the execution to the newer version label, which will do the migration and also create the table. For example:
DECLARE @currentDBVersion BIGINT = NULL;
-- Get the current version of the database.
IF NOT EXISTS(SELECT 1 FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLES]. [TABLE_NAME]='DatabaseVersion' AND [TABLES].[TABLE_SCHEMA]='internal') SELECT @currentDBVersion = 1
ELSE
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
58 msdn magazine
DevOps