This is part 5 in the series named SQL Server and Continuous Integration. This post will cover the steps I follow to setup a ReadyRoll project. Topics covered so far in this series are:

• Git
• Visual Studio
• ReadyRoll Installation

I’m using ReadyRoll in my Continuous Integration setup to generate migration scripts that will intelligently update existing instances of my database project no matter which version they are running. These are deployed to GitLab when I make a commit, which then automatically builds my project, runs my unit tests and produces deployment artefacts. These artefacts include deployment packages, unit test reports, code coverage reports and schema drift reports. These artefacts and GitLab will be covered later in the series.

To get started, open Visual Studio (I’m using the 2015 community edition) and create a new ReadyRoll project.

Make sure to tick Add to Source Control. This will create a Git repository in the new directory for this project.

Project Settings

I’ve found it very important to get the project settings right before starting any work. Right click the ReadyRoll project in the solution explorer and select properties.

I set the target platform to be the oldest version of SQL Server that I will be deploying my project to. I’m using SQLCMD packages which are one big script that includes the pre-deployment, post-deployment and the migration scripts. This gives me some flexibility when deploying to isolated environments that can’t be linked to deployment tools. I could also use DACPACs for my manual deployments.

Programmable objects include our views, functions, procedures, and basically anything other than a table. I want my migration scripts to only contain the potentially complicated changes to stateful objects (tables). Changes to a stateless (programmable) object are simple and do not need to be performed in a particular order, we just apply the latest version. For this reason, I set Programmable Objects to import into separate files. This setting also makes it easy to review the changes to programmable objects over time with the use of Git.

Clicking Import all Programmable Objects will create a single file for each programmable object with its CREATE statement. It’s possible to build your own folder structure and include the scripts in those folders as part of the deployment, but I would recommend letting ReadyRoll create the folder structure for your scripts.

The Offline Schema Model is a collection of CREATE scripts for our tables that resemble the Programmable Objects folder. It’s this feature that makes ReadyRoll a hybrid system, it’s both state and migration based. This allows the migration scripts to carefully deploy changes to stateful objects in the order required. While the Offline Schema Model makes reviewing the history of each object in Git a breeze.

As time goes by, we could end up with a lot of migration scripts for all the changes made to the project. Selecting Semantic Versioning for the organisation of the migration scripts generates a sensible folder structure to store them in.

I’m using a folder with version 1.0.0 as this is a new project but this can match your current version number if connecting to an existing DB. See my previous post for more about Semantic Versioning.

SQLCMD Variables

The Pre-Deployment folder has a script that creates the database if it does not exist. This script is very basic and does not cover some of the settings I want when deploying the database to a new instance.

IF (DB_ID(N'$(DatabaseName)') IS NULL)
BEGIN
  PRINT N'Creating $(DatabaseName)...';
END
GO
IF (DB_ID(N'$(DatabaseName)') IS NULL)
BEGIN
  CREATE DATABASE [$(DatabaseName)]; 
END

I want to improve this script to set the compatibility level and file sizes. I could hard code the values in the create database script but I want them to be configurable so as to suit each environment. $(DatabaseName) is a useful builtin variable, but I will need to add some new ones for my settings. This can be done in the SQLCMD Variables section of the properties window for our ReadyRoll project. See here for more on SQLCMD variables.

Hidden settings

Close Visual Studio and navigate to the ReadyRoll project folder in the solution directory. Right click the Visual Studio SQL Server Project file and open it in your editor of choice (I’m using Microsoft’s Visual Studio Code editor). This file has many settings that are not available in the UI. Some of the most useful are:

• IgnoreExtendedProperties
• IgnoreColumnsOrder
• IgnoretSQLt
• IgnoreIndexSortOrder
• IgnoreFillFactor
• IgnoreStatistics
• IgnoreDataCompression
• IgnoreDataspaces – This retains the table to file group mappings.

There is also a section to set filters for each object type, useful if you have procedures in your development database that you don’t want to deploy to production.

In the next post, ReadyRoll Development, we can start developing our database in Visual Studio or SSMS.

The post Starting a ReadyRoll Project appeared first on The Database Avenger.