This is the 4th post in the series named SQL Server and Continuous Integration. The first 3 posts covered the need for Continuous Integration (CI), the central part that Git (my version control system of choice) plays in the whole process and an easy way to use Git in Visual Studio. Now that we know how to use our tools, it’s time to use them with ReadyRoll.

Why ReadyRoll and not SSDT?

Both plugins generate an off-line schema model that has a single script file for each object in our database. Each time we make a change to our development database the off-line schema model is updated. It’s this collection of scripts that Git will use to track changes to individual database objects. Once in Git, these scripts can be used to build deployment packages.

But what about upgrading old deployments to the latest version?

There are two lines of thought on this, state based and migration based. This is a big topic see here and here but personally I prefer a migration based approach. This requires migration scripts and this is where ReadyRoll comes into it’s own.

Migration Scripts

ReadyRoll automatically generates and adds migration scripts to our project in Visual Studio. This means almost all the manual work of writing (or generating with compare tools) migration scripts is done by ReadyRoll. ReadyRoll can also organise these scripts, using semantic versioning, into a logical folder structure within our project.

We could also add migration scripts to a SSDT project but we would need to generate or hand write each one and manage the order in which they are executed. Now that our migration scripts are in our project, we can easily source control them.

Deployment Testing

Using the scripts in our project, ReadyRoll creates a copy of our development database (minus the data) known as the “shadow database”. By default this database is deployed to the same instance as our development database, but it can be deployed elsewhere. ReadyRoll uses compare tools to look for differences between our development database and the shadow database. If differences are seen (we made some changes) then migration scripts are generated to replicate the changes in to the shadow database. If the shadow database is successfully brought into line with the development database then the scripts are added to our project.

Deployment Packages

The project can be deployed with DACPACs, Octopus Deploy NuGet packages or SQLCMD files that are generated by ReadyRoll. In my setup with GitLab, I have moved the ReadyRoll MSBuild files from my machine to my build agents. This enables GitLab to build the deployment packages and drive my Continuous Integration processes, plus more which I will discuss in a future post in this series.

These deployment packages can be deployed against all of our production environments, even if they are on different versions. This is possible because of the __MigrationLog table which is included in all ReadyRoll developed databases. This table keeps a record of all the migration scripts that have been executed against this particular instance of the database. When the deployment package starts the upgrade process, it checks this table to see which migration scripts have been executed. It then only runs the migration scripts that have not already been executed on this instance of the database. This means if we accidentally ran the deployment twice it wouldn’t matter as no scripts would be executed on the second run.

Installation

So now we know what it can do let’s install it.

Download the installer from here (Free trial signup required). Close all instances of Visual Studio and run the installer. Accept the terms, click through the rest and you should end up with a successful install.

Click Run ReadyRoll and Visual Studio will open with the ReadyRoll start screen.

This is where we will start in the next post, ReadyRoll Configuration.

The post ReadyRoll for Database Deployments appeared first on The Database Avenger.