Redgate ReadyRoll Development

ReadyRoll Development

This is the sixth part in the series: SQL Server and Continuous Integration. This post will illustrate the power of ReadyRoll when developing database projects. Topics covered so far in this series are:

For this post, I will be starting a new project that I have configured as explained in the previous post. ReadyRoll will create an empty database in which I will start my development. If you want to use ReadyRoll for an existing database, you need to baseline your database first. Detailed information on this process can be found here.

Setup

Once the project is configured, it’s time to create the new database. A ReadyRoll menu is available in the main toolbar at the top of your Visual Studio window. This menu has an item called ReadyRoll DBSync. Clicking this will open the main ReadyRoll screen. If you haven’t already connected your project to a SQL Server instance, you will be asked to do so now. Browse to your instance and enter the name of your new database in the last text box.

ReadyRoll Connect Database - Connection

 

If your connection was successful, you will see the screen below. Click deploy to create your new database.

ReadyRoll Deploy New Database

Once the database has been deployed, we need to connect to it and then import it with the screen below.

ReadyRoll Redgate Import Existing Database

 

The DBSync screen will show the button below. Clicking it will cause ReadyRoll to synchronise the shadow database with the new database.

readyroll preview pending changes

At this point, the DBSync screen only displays identical objects. This screen will be more interesting after we have made some changes.

readyroll redgate new database

Development

Let’s switch SSMS to add a table and a stored procedure.

CREATE TABLE Customers
(
  CustomerId INT IDENTITY(1,1),
  FirstName NVARCHAR(100),
  MiddleName NVARCHAR(100),
  LastName NVARCHAR(100),
  DOB DATE
);
GO

CREATE PROCEDURE GetCustomers
AS
BEGIN
  SELECT 
  c.CustomerId,
  c.FirstName,
  c.MiddleName,
  c.LastName,
  c.DOB	

  FROM	Customers c;
END;
GO

Hitting refresh on the DBSync screen reveals our changes.

Redgate ReadyRoll New Objects Detected

The tick boxes on the left allow us to stage our changes to only include certain objects in the next migration script. This is useful when you want to have a separate migration script for each object.

Right clicking the rows in the grid allows us to:

  • review the generated script
  • view revert scripts which can be used to reverse the changes
  • view the object differences

If you click “view differences”, be aware that ReadyRoll opens a tab in Visual Studio but doesn’t switch focus to it automatically. Clicking the “Import and Generate Script” button will apply the changes to our ReadyRoll project.

Redgate ReadyRoll Schema Model

We now have our first migration script, the Programmable Objects folder and the Schema-Model folder. This collection of objects will be used by our build server to build the database and run our tests against it.

Now we need to click “Refresh (Verify Scripts)” to apply our changes to the shadow database.

Redgate ReadyRoll Verify Scripts

The DBSync screen will now show that our database and project are the same. The process of applying our changes to the shadow database can catch issues in our migration scripts. Catching these issues now instead of on the build server will save a lot of time.

Deployment

All changes are logged to the _MigrationLog table in our database that is created and managed by ReadyRoll. It’s this table that allows the ReadyRoll deployment package to only deploy the required changes to each instance of our database.

Building the project in Visual Studio will generate the files below in our debug folder.

Redgate ReadyRoll Deployment Packages

I’m only interested in the bottom two. The .SQL script holds all of the scripts required to build the database and it’s objects. The PowerShell file is a wrapper that validates the parameters we want to pass to the .SQL script. I can use this deployment package to build a new instance or upgrade an existing instance of my database on multiple servers.

We have now used ReadyRoll to generate a migration script for our changes and a deployment package for the latest version of our database. It’s this package that will really allow us to work in a Continuous Integration manner with SQL Server. In the next post, GitLab for SQL Server Projects, I’ll explain how to configure GitLab to build the ReadyRoll project, build the database with the resulting deployment package and run all of our tests automatically.

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.

Leave a Reply

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