DevOps and Continuous Integration are dominant themes today, and Microsoft’s launch of Azure DevOps underscores their importance.  SQL Server DevOps is also contributing to expanded adoption of database source control systems, with the goal of managing SQL Server as code.  The growth in SQL Server DevOps, in turn, has led to increased focus on T-SQL migration scripts for improved peer code reviews and documentation.    Ocotpus Deploy, Red Gate Ready Roll, and other tools help manage use of migration scripts with ordering via timestamp and other mechanisms.

For those working without migration supporting tools, this article outlines a simple method of ordering migration scripts with Git and PowerShell.    This article looks at a SQL Server container workflow, but the same approach works equally well with PowerShell for SQL Server instances.

Ordering Migration scripts

Working with T-SQL migration scripts often involves different projects in a shared repo (Feature A and B), and It’s common to follow naming conventions.   In the example below scripts are ordered with explicit file names.

• Feature_A_script1.sql
• Feature _A_script2.sql
• Feature_B_script1.sql
• Feature_B_script2.sql
• Feature_A_script3.sql

With scripts appropriately named, it is easy to order their application.   But, how can the order of the scripts be handled when a new script needs to be inserted into the implied order, or when their order needs to be changed in some way?

Marshalling Migration Scripts “as code”

A PowerShell script can define the order of migration scripts, and create a new consolidated “ordered” script.   The ordering script is created, updated, and committed to the repo whenever a new migration script is committed, with Get-Content piped to Set-Content to create a migration script.  The following script concatenates script1, 2, and 3, to create an ordered script (Feature_A).

Get-Content Feature_A_script1.sql, Feature_A_script2.sql, Feature_A_script3.sql | Set-Content Feature_A.sql

Updating and committing the ordering script ensures that script ordering is kept in sync with each new commit, and can be run when the repo is pulled or cloned.

SQL Server containers are delivered according to an image defined by a Dockerfile, that includes a Git clone of the repo into a \scripts folder in each container.  Note that the scriptorder.ps1 is committed and cloned from the repo, and thus does not need a COPY command in the Dockerfile.  The ordering script (scriptorder.ps1) is executed during container run time, to create Feature_A.sql.

FROM mssql-2017
SETUPCLONING FULL customers C:\windocks\dbbackups\customerdatafull.bak
COPY cleanseData.sql .
RUN cleanseData.sql
ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1
RUN "C:\Program Files\Git\cmd\git.exe" clone https://github.com/repo-url.git scripts
RUN scriptorder.ps1  

The result is a clone of the repo, along with the Feature_A.sql script in each container.  The same process works equally well for working with conventional SQL Server instances.

Development and Testing with Cloned T-SQL scripts

Cloning the repo and creating an ordered Feature_A.sql script provides developers and testers a complete set of scripts for their work.   They can navigate to the container \scripts folder to execute scripts, or can apply selected scripts during container creation.  Selective application of scripts with SQL Server containers is accomplished with the following syntax:

>docker create -e RUN=”scripts\Feature_A” <imagename>

Combinations of scripts can also be applied:

>docker create -e RUN=”scripts\Feature_A_script1.sql, scripts\Feature_A_script2.sql” <imagename>

Scripts can also be itemized and included in Azure DevOps pipelines to test specific features, and upgrade and rollback scripts.

DevOps:  a Growing Opportunity for SQL Server Professionals

SQL containers are ideal for working with databases as code, with Git and other source control systems.   Containers are also ideal for DevOps and continuous integration, as they are provisioned in seconds for short-lived testing.   Up to 50 containers are supported by a 4 core machine, providing a dynamic and economical resource pool.   Database cloning contributes further with a 99eduction in storage for databases.

If you’re interested to explore Windows SQL Server containers, download the free Windocks Community edition at https://windocks.com/community-docker-windows