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.
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.
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.
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.
