Database Development With Containers, Git, and Migration Scripts

I'm often asked, “What is the best practice for backing up developers' SQL Server containers?”

While containers support backups, a better process treats SQL containers and database clones as code with a source controlled workflow. Full disclosure, I am a principal at Windocks, where we focus on SQL Server containers. This article discusses the use of containers with Git and migration scripts. The same processes, however, will work for anyone working with databases with conventional VMs or instances with Git and migration scripts.

SQL Server Development as Code

SQL Server containers are combined with secure production database clones, Git or other source control system, through a series of steps.

A database clone is built with a SQL container and data masking and other scripts, and integrated with a Git repo (Step 1). Developers self-provision containers with a cloned database, with optional ports and SQL sa password assignment (Steps 2 and 3), with scripts committed to the source repo (Step 4). Developers stay in synch with new commits by provisioning new containers, which each including a clone of the repo. When the scripts are ready for Test, the dev branch is merged to trigger the build of the CI pipeline (Steps 5 and 6).

Containers With Git Cloning and Script Marshalling

Images are built with dockerfiles that define the SQL Server container (mssql-2016), and database clones, scripts, and integration with a Git repo. The example below includes a full backup of the customer's database and a cleanse data script.

At container run time, a Git clone copies the repo into a scripts folder. A Powershell script (combinescripts.ps1) is updated and committed to the repo to concatenate and order migration scripts into a combined “all.sql” script.

Combinescripts.ps1

Get-Content scripts\script1.sql, scripts\script2.sql | Set-Content all.sql

The Git clone and Powershell scripts are included in the dockerfile and applied at run time:

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/WinDocks/git-db-scripts-runtime.git scripts
RUN powershell.exe scripts\combinescripts.ps1

Developer Self-Service

Developers provision containers using the Windocks web UI, which includes a drop-down list of databases in each image to enable selection of a subset of databases. Each container is named, and a container port and SQL sa password can also be assigned. The image build tool is shown below.

Working With a Git Repo

Each new container includes a clone of the Git repo in the scripts folder and the ordered script all.sql. Developers can use standard client tools (SSMS, etc.) to clone the repo to their local workstation and apply the scripts, or they can use selected scripts at run time using the following docker command syntax.

>docker create -e RUN=”scripts/upgrade1.sql, scripts/upgrade2.sql” <imagename>

Conclusions

Developers can use these steps to protect work-in-process without backups and share their work.

Combining SQL Server containers with database clones and Git provides advantages over current practices of “building” databases from source control. Building databases from source requires time and does not identify performance or data-related issues in development. Secure production database clones provide a higher quality artifact for development and test and deliver higher quality software releases. This process also supports complex images that can scale to deliver multi-terabyte environments with scores of databases.