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 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).
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
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.
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.
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.