Page 58 - MSDN Magazine, November 2017

DEVOPS
Continuous Data
Migration Using Visual
Studio and TFS
Jebarson Jebamony
Data migration is often a requirement in an application development effort, whether it’s a greenfield project or an appli- cation redesign. Despite this, very little attention gets paid to data migration during the design and development phase, with activity often pushed back to the latter part of the project. While this approach might allow an undivided focus on migration, it comes with a few risks, not the least of which is that teams lack the time and resources to properly test the migration and surrounding code.
The concept of continuous data migration is founded on the idea of developing migration scripts during the development of an application and maintaining them through versions just like the application code. A continuous migration approach allows you to test your migrations alongside code development, ensuring your data and code assets stay in sync.
In this article, I describe a solution that leverages Visual Studio and Team Foundation Server to achieve continuous data migration. Keep in mind there are third-party tools like Red Gate Ready Roll that are capable of doing this partially, but they come at a huge cost and lack the capability of continuous data migration.
Challenge and Solution
Visual Studio can perform an incremental publish of a database with the help of SqlPackage.exe, but the tool is lacking in many ways. For instance, SqlPackage.exe falls short when inserting a new col- umn between the columns of a table, changing the seed data, and normalizing and de-normalizing tables, among other examples.
Also, versioning changes is very important when you need to do targeted fixes and deployment. For example, you may need to increment the value of a column by 10 when you migrate from v1.2 to v1.3, but not in any other flow. This can only be achieved with the use of versioning; however, SQL lacks this capability.
I want to approach the challenge by designing a solution that takes full advantage of what Visual Studio and SqlPackage.exe can offer, while resolving the previously mentioned shortfalls.
A typical database project has two types of scripts—compiled and non-compiled. All of the objects such as schema, tables, views, stored procedures and the like are generally written as a compiled script. The seed script and any runtime queries will generally be placed into the post-deployment script, which is non-compiled.
Let’s start with an example. Figure 1 shows the Adventure- Works.Database sample database project (imported from the
This article discusses:
• Introduction of continuous data migration as a process to software development
• Implementing versioning in a database
• Implementing continuous data migration in SQL using
Visual Studio and Team Foundation Server
Technologies discussed:
Visual Studio, Team Foundation Server, SQL Server
Code download available at:
github.com/Jebarson/ContinuousDataMigration
54 msdn magazine