Redgate ReadyRoll

ReadyRoll for Database Deployments

This is the 4th post in the series named SQL Server and Continuous Integration. The topics in this series are:

  1. Git
  2. Visual Studio
  3. ReadyRoll
  4. GitLab
  5. tSQLt
  6. SQL Cover

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.

ReadyRoll Migrations folder uses semver to organise our migration scripts
Semver is used to organise scripts in the Migrations folder

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.

Readyroll installer finished

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

readyroll new project

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

 

About the author

James Anderson

I'm a freelance SQL Server DBA in Southampton, UK. Performance tuning code and improving development processes are what I love to do for work. Outside of work, I live to travel to as many places as possible with my wife Sarah.

2 thoughts on “ReadyRoll for Database Deployments”

  1. This is a great summary of the fundamentals of ReadyRoll. I can’t wait to see the next installment!
    Just one minor point of clarification: you mentioned that ReadyRoll can produce DACPACs, which is quite true. However I just wanted to worth clarify that, if these are used to actually deploy the database (e.g. using SqlPackage.exe), migrations won’t actually be used to run the deployment. Instead, the SSDT state-based method would be used.
    As an aside, the reason why you might want to produce a DACPAC during build is if you want to compare your ReadyRoll project to another environment (e.g. using the schema compare feature in Visual Studio).

    1. Thanks Dan! Yes I can see how my post may have led readers to believe that the DACPAC would perform migrations which is not the case. Thanks for clearing that up.

Leave a Reply

Your email address will not be published. Required fields are marked *

Sign Up

James Anderson SQL Server DBA

Hi, I'm James Anderson. When I'm not blogging about SQL Server, I do this.

The SQL People

DBA Stackexchange Profile