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:

• Git
• Visual Studio
• ReadyRoll Installation
• ReadyRoll Configuration

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.

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

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

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

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

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
  FROMCustomers c;
END;
GO

Hitting refresh on the DBSync screen reveals our changes.

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.

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.

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.

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.

The post ReadyRoll Development appeared first on The Database Avenger.