Tag: Continuous Deployment

VSTS Sql Server Database Deployment Task

In this post I’ll be having a quick look at the VSTS Sql Server Database Deployment Task. You can find it in the marketplace as part of the IIS Web App Deployment Using WinRM package. It is a bit confusing that you have to look for it under the IIS section not the SQL section in the marketplace.

Take note this task is using WinRm, the instructions to enable it is on the marketplace page and includes a link to a PowerShell script you can run to configure WinRM.

Lets take it for a spin. Follow the installation process from the marketplace to install the extension on your account or download it for on-prem TFS servers. You will see the new WinRM – SQL Server DB Deployment tasks in the Deploy tasks menu.sqlsvsts

Add the task to your deployment and set a few properties, this was quick test I did. You will need a task before this one to copy the dacpac file to a location where the agent can reach it.

SampleConfig

I didn’t have ports open on my firewall for the VSTS hosted agent to reach my machine so I installed the VSTS agent locally and set the task to use localhost. You can specify multiple machines or even use Machine Groups, you will find it in the Test hub in VSTS.

The output from the task doesn’t show much about the actual database changes taking place but you will at least know if the deployment failed.

dacpacoutput

Side Note: Don’t put plain text passwords in your build tasks, use variables and mark them as secret by clicking the padlock icon next to the variable value.

Secret

You can find some more information in the readme file for the task, which they include as a link in error messages when a step fails, which is really helpful.

If you are looking for more information around  continuous deployment of databases have a look at these posts in my continuous deployment of databases series.

Part 1 covers the logic behind the deployment options.
Part 2 covers SqlPackage.exe.
Part 3 covers ReadyRoll.
Part 4 covers FlyWay.

Francois Delport
Latest posts by Francois Delport (see all)

Continuous Deployment Of Databases : Part 4 – Flyway

This is part 4 in my series on continuous deployment of databases. In this post I’ll be having a quick look at FlyWay.

Part 1 covers the logic behind the deployment options.
Part 2 covers SqlPackage.exe.
Part 3 covers ReadyRoll.
VSTS Sql Server Database Deployment Task can be found here.

In my two previous posts I briefly had a look at two tools for SQL Server Continuous Deployment, SqlPackage.exe and ReadyRoll. I wanted to see what is out there for Oracle, MySQL and other databases especially if you have a situation where your application supports multiple database and you want to do Continuous Deployment against all of them. There are plenty commercial applications for SQL and Oracle that integrate directly with build systems and IDEs but not that much for other databases based on my quick google search.

Flyway
I’m going to take a quick look at Flyway, a migration based tool, bearing in mind I work in .NET and some of the features are Java centric. It is an open source project and looking at the commit history on GitHub it has been around for a long time and is supported by an active development community. At the time of writing it supported 18 databases.

You create migrations using SQL script files or Java code based migrations which comes in handy for very complex data manipulation and handling blobs. It is based on convention over configuration so naming folders and files correctly is very important since the version number which controls the deployment sequence is part of the name. There are various ways to execute the migrations for example using the command line tool or calling the Java API directly, this opens up scenarios like including Flyway as part of your application to keep your database and application in sync. There are also plugins for Maven, Gradle and Ant but not much in terms of .NET.

I’ll quickly touch on the main operations used by the application:
Migrate: The meat of the application, it migrates a schema to the required version by determining the migrations that should be run.
Clean: Wipes the destination database clean, used during development and testing.
Info: Shows the migrations applied with execution time and state and which ones are pending.
Validate: Verify the applied migrations match the ones you have locally.
Baseline: Integrate Flyway with an existing database so only newer migrations will be applied.
Repair: Flyway uses a metadata table to keep track of applied migrations, this will the repair the metadata table.

Tooling
It doesn’t have direct integration with Visual Studio or VSTS but it doesn’t look to difficult to roll your own automated deployments with some scripting to call the command line tool. Similar to the SqlPackage.exe you can call the Flyway command line tool from your release management tool to update the target database directly or generate a report of the changes that will be applied in case a DBA has to scrutinise the changes first.

Oracle Side Note
If you want to use Flyway against Oracle or just want to use Visual Studio in general for Oracle database development have a look at Oracle Developer Tools for Visual Studio. Oracle tools is very similar to Sql Server Data Tools and makes is easy to add your Oracle database to source control. You can also use it to compare Oracle schemas and generate change scripts which can be the base for your Flyway migration scripts.

Francois Delport
Latest posts by Francois Delport (see all)

Continuous Deployment Of Databases : Part 3 – ReadyRoll

This is part 3 in my series on continuous deployment of databases. In this post I’ll be having a quick look at ReadyRoll and using it in continuous database deployment.

Part 1 covers the logic behind the deployment options.
Part 2 covers SqlPackage.exe.
Part 4 covers FlyWay.
VSTS Sql Server Database Deployment Task can be found here.

ReadyRoll, is a tool from Redgate that is migration based, although it also allows creating individual migration scripts based on state comparison. It is pretty easy to use and I was able to get my demo working end to end and running a release from VSTS without ceremony.

Quick Intro To Creating Migration Scripts
Just like Sql Server Data Tools, ReadyRoll is integrated into Visual Studio. You can create the initial project by importing an existing database and adding it to source control. To create your own migration scripts you can add them manually to the Migrations folder in your project or you can make changes to a database using the Visual Studio Server Explorer and then click on the Update button and then Generate Script button to generate the migration script based on the changes made without updating the actual database.

You can also use the state method by making changes to the database directly using Visual Studio Server Explorer or Sql Server Management Studio and then importing the changes back into your project. Use the ReadyRoll DB sync option from the ReadyRoll menu and the Import and Generate Script button to generate a migration script that will be added your Migrations folder. Obviously if the changes to the database involved data manipulation that part of it will not show in the migration. The changes you made can be misunderstood for example renaming a column can show up as adding a new column and deleting the old one via a table recreate.

You can edit the migration scripts and add any steps needed to manipulate data or database objects which makes is better suited for changes that require data manipulation.

Database Baseline
ReadyRoll uses a table called __MigrationLog to keep track of the migrations applied to a database, when you import an existing database this table is created and the current state is used as the baseline for that database. Before you can deploy to a database you have to baseline it first, for example you import your Dev database into ReadyRoll but will be deploying to QA and Production as part of your release for the first time. Read the documentation here for instructions before you try to deploy or the deployment will fail.

Continuous Deployment In Visual Studio Team Services
 To use ReadyRoll in VSTS you have to install the extension from the marketplace, the official getting started guide for ReadyRoll in VSTS is over here. I’m just going to highlight a few things but this will not be a deep dive into getting it working. Continuous Deployment  with ReadyRoll is a two step process, first building and then releasing. The build step will build your ReadyRoll project while comparing it to the target database specified in your build step to generate a deployment package PowerShell file to run the migrations required on the target database. This PowerShell script will be part of the artifacts of the build. You can view the SQL script and a database diff report on your build result page in VSTS.

ReadyRollNewTabs

The next step is to release it, in your release definition add the Deploy ReadyRoll Database Package task and point it to the script created in your build and the target database.

AddReadyRollStep

Take note, in the Package To Deploy setting of the task, point it to the {YourProjectName}_DeployPackage.ps1 file from your artifacts, not the {YourProjectName_Package.sql} file. I hit a snag at this point since the DeployPackage.ps1 file did not exist for me, to fix it I had to go to the properties of the project in Visual Studio and select SQLCMD package file as the output type.

EnablePackage

When To Use State Or Migration
I talked bit about the differences in my first post but after all the research and demos I created I started to see how migrations seem better suited for a smaller number of changes that are complex and require data manipulation where as state based changes work great during development and for a large number of changes especially to the database structure not the data it self. Obviously this depends on your application and the churn in your database and data structure.

Francois Delport
Latest posts by Francois Delport (see all)

Continuous Deployment Of Databases : Part 2 – SqlPackage.exe

This is part 2 in my series on continuous deployment of databases. In this post I’m going to have a quick look at SqlPackage.exe and using it in continuous database deployment.

Part 1 covers the logic behind the deployment options.
Part 3 covers ReadyRoll.
Part 4 covers FlyWay.

Update: The VSTS Sql Server Database Deployment Task using SqlPackage.exe has since been released, you can read more about in this post. If you are using SqlPackage.exe outside VSTS the information in this post will still be relevant.

SqlPackage.exe is a free command line tool from Microsoft that you can use against SQL Server and Azure SQL Server to compare, update, import, export databases to name but a few scenarios. This post is only scratching the surface, the official documentation is here, please read it to get a better understanding of the tool.

SqlPackage.exe is a state based database tool, if you are not familiar with the term please read part 1 of the series here for an explanation. For continuous deployments I will be using a dacpac file as the source state for my database, read more on dacpac files here. The easiest way to generate dacpac files is using Sql Server Data Tools. SSDT is integrated with Visual Studio, when you build the SSDT project the output will be a dacpac file. This also fits in nicely with adding your database to source control and building the proper version of your database along with your other application components. You can read more about SSDT here.

Making Backups
As everyone knows you always make a backup of your database before deploying changes. You can instruct SqlPackage.exe to backup your database by passing the /p:BackupDatabaseBeforeChanges parameter. I assume it will create a backup using the default settings and location of your server but I prefer to rather do it myself. Here is a sample PowerShell script that will backup a database using the SQLPS module and SQL Server Management Objects SMO. Side Note: keep your PowerShell scripts in source control instead of lumping large blocks of script into text boxes in your build or deployment tool.

Now for the fun part, I’m going to explore 3 scenarios that I came across so far and I guess most other developers will as well.

Scenario 1: Automatically update your target database
If you are deploying to a development database or have an extremely informal (but not recommended) production environment, you can use SqlPackage.exe to update your target database by comparing it to your dacpac file and applying the changes on the fly. The syntax for this is:

/Action:Publish /SourceFile:$dacpac_path /TargetConnectionString:$constr

Scenario 2: Generate a script with the changes that will be applied
The more likely choice for production environments would be generating a script that your DBA can scrutinise before applying it to the production database. The syntax for this would be:

/Action:Script /SourceFile:$dacpac_path} `  /TargetConnectionString:$constr /OutputPath:$path_to_scriptfile

For the actual deployment you have a few options, you can pull in the generated script as an artifact of the build or email it to the DBA or drop it in a file location etc and they can apply it manually using their preferred procedure. Alternatively you can still keep the process semi automated and inside your release management tool by adding a deployment step that requires approval from the DBA, this step will use the generated script artifact to deploy the changes to a database.

Scenario 3: Generate a difference report between database
This is usually a post deployment task that will form part of your post deployment testing or sanity check to confirm the database is in the correct state. The SqlPackage.exe tool should always do its job properly but database are precious pets and everyone wants to make 100% sure everything is correct. The syntax is:

/Action:DeployReport /SourceFile:$dacpac-path ` /TargetConnectionString:$constr /OutputPath:$reportoutputfilepath

I combined all the samples into one PowerShell script here.

Populating lookup tables
In your SSDT project you can add pre and post deployment scripts. I use the post deployment scripts with the SQL Merge statement to populate lookup tables, full example from MSDN here. There is a way to add the lookup table(s) as dependent objects but last I read the tables couldn’t contain foreign key relations to tables outside the dependent dacpac.

VSTS SQL Deployment Task
At the time of writing this blog post the official SQL Deployment task for VSTS was in preview and should be released very soon but in the mean time I used these examples scripts in VSTS and they worked so if you are in hurry they will do the job.

Francois Delport
Latest posts by Francois Delport (see all)

Continuous Deployment Of Databases : Part 1

This is part 1 in my series on continuous deployment of databases. In this post I’ll be covering the logic behind the different deployment options.

Part 2 covers SqlPackage.exe.
Part 3 covers ReadyRoll.
Part 4 covers FlyWay.
VSTS Sql Server Database Deployment Task can be found here.

Continuous deployment of databases is a lot more complicated than applications so before we can look at continuous deployment of databases we have to look at managing database changes first. I’m going to give a high level overview of the problem space and methods to deal with database changes before looking at practical examples of continuous deployment of databases in the next post.

The Problem Space
A few of the challenges we face with database changes are:

  • Rolling back: Rolling back files is easy, just restore the previous version from source control, with databases you have to explicitly back them up before hand and restore them if needed and it can take a while for large databases and no one can work while you are restoring.
  • Down Time: It is easy to swap servers in and out of your load balancer to update them but it is not so simple with a database, even in a cluster. It is not impossible with databases just more work, there are solutions like sharding for instance but that is a post on its own.
  • State: Your application files do not contain state, you can recreate them easily while your database contains precious business data. You have to make changes to the structure and existing data without loosing anything. This leads to some teams hand crafting the change scripts for each release which is error prone.
  • Control: In large organisations DBAs are often the only people allowed to make changes to a database and they scrutinise the change scripts provided by developers before implementing them manually. You can’t have automatic migrations or automatically generated scripts executing against the database.
  • Drift: It is not unheard of that databases in production do not match the version developers are using the create the change scripts, especially with applications that are installed at the client premises.

Keeping Track Of Database Changes
The most important step to keep track of your database is adding it to source control, whether you are using specialised database tools or not. This will enable tracking the changes made to your database at specific points in time. Even just having the database create scripts committed each time a change is made will be better than nothing. If you are using the state/declarative method it will also help to solve conflicting changes, for instance two developers renaming the same column in a table at the same time will lead to a merge conflict in your source control system. You can also branch the database just like you do with application code to fix a serious production bug and then later merge the changes back into trunk.

Approaches To Implement Database Changes
Currently there are two ways to handle database changes, state/declarative and migrations, each one with its own strengths and weaknesses:

State/Declarative Method:
The state method works by comparing the target database to a source database and then generating a script to bring the target database in sync with the source database. This is best accomplished using a database schema comparison tool.
Pro: Any number of changes made in any sequence by team members will be included since the current state of the source database in source control is the source of truth not the steps taken to reach that state. Works great for databases during the development phase when you can tolerate dropping and recreating tables or columns and large teams making multiple changes at the same time. Less error prone since the scripts are generated by a tool instead of hand crafting them.
Cons: Cannot handle data migrations since the tool works according to the current state, it has no way of knowing how that state was achieved. For example if a new column was created by manipulating data from two existing columns.

Migrations Method:
This method applies a sequence of changes to a database to migrate it to the new state. With this method the migration scripts are the source of truth, the scripts are created by hand (there are exceptions see the comparison of tools later in the post) and keeping the correct sequence is very important.
Pro: Handles data migrations very well since developers can include any data manipulation required in the scripts.
Cons: Most of the time the scripts will be created by hand which is cumbersome and error prone. You have to take all the change scripts created during development and scrutinise them to weed out conflicting changes and you have to keep the correct sequence. For example developer A renames a column in table X and checks in the alter script, later developer B also renames the same column to something different without getting the changes from developer A and checks in his alter script. When you execute all the migrations the change from developer B will fail since the column exists under the new name. It is also cumbersome for all developers to keep their development databases in sync with the changes.

As you can see there is no clear cut solution to the problem and most tools will fix some of the problems but not all, for example Sql Server Data Tools will handle the state based method very well and Entity Framework Code First will handle migrations very well. Some tools like ReadyRoll try to combine both by generating a sequentially numbered migration script based on the current state of your database when you check-in a change.

Francois Delport
Latest posts by Francois Delport (see all)