Database Development with Containers, Git, and Migration Scripts

We are often asked: “what is the best practice for backing up developers SQL Server containers?”

While containers support backups, we recommend working with SQL containers and database clones as code with a source controlled workflow. This article describes the steps of this process.

SQL Server development as code

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

Step1: a database clone is built with a SQL container and data masking and other scripts, and integrated with a Git repo. Step 2, 3, and 4: Developers self-provision containers with the cloned database, with optional ports and SQL sa password assignment. Scripts are 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 considered ready for Test, the dev branch is merged to trigger the build of the CI pipeline (Steps 5 and 6).

Containers with Git clones and script marshalling

Images are built with dockerfiles, plain text configuration files 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 customers database, and a cleansedata 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 the 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 can selected scripts at run time using the following docker command syntax.

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

This process is also supported by Windocks with script variables included in an Azure DevOps pipeline.

Conclusions, support, and resources

This workflow allows developers to protect work-in-process without backups, and also supports sharing of environments between developers. "Joe, create a new container and run "myscript.sql."

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. In contrast, secure production database clones provide a higher quality artifact for development and test, and delivers higher quality software releases. This process also supports complex images that can scale to deliver multi-Terabyte environments with scores of databases.

Windocks now provides a hosted Azure DevOps lab with ready-for-use YAML files available here . Or, download both the YAML file and the free Windocks Community Edition..