SQL Server containers can be used to “clone” production environments for development and test, delivering isolated SQL containers with production data, and instance configuration.
Windocks SQL containers are created by cloning a locally installed SQL Server instance, and combined with database cloning and T-SQL scripts can deliver a complete replicated “production” environment for dev/test support in seconds. Creating containers by cloning an installed instance yields many benefits, including simplified image maintenance, improved portability, scalability, and security. This article explains how SQL Server containers are combined with database clones and T-SQL scripts, to create images. Images, in turn, deliver complete environments in seconds.
The process outlined also supports database clone delivery to Microsoft’s Docker SQL Server containers (Windows and Linux), and conventional instances.
Building Custom Images and Containers
Images deliver database clones to conventional SQL instances or containers, and T-SQL scripts are used to configure the environment to reflect the production server being “cloned.”
Delivering a replica of a production server begins by building an image. An image begins with a new container, and a Windows Virtual Hard Drive is created and mounted to the container. The backups are restored to the VHD, and build-time scripts are run for data masking or other preparations. When the build-time scripts are complete, the VHD is saved as the custom image. Scripts slated to be applied at run-time are also saved, and applied to each container at run-time.
Dockerfiles are plain text configuration files that include the SQL Server image, databases, and scripts, and provides order to their use in the image build.
The dockerfile begins with a base image (FROM mssql-2014), followed by SETUPCLONING commands for the backups used to build the image. Scripts are copied to the image with COPY commands, and build-time and run-time scripts are determined by relative positioning to an environment variable ENV USE_DOCKERILE_TO_CREATE_CONTAINER=1. Scripts above the environment variable are RUN at build-time, and scripts listed below are RUN on each container as they are provisioned.
The image is built with a docker command: >docker build -t <imagename> c:\path\to\dockerfile. Alternatively, the dockerfile and scripts are selected and “built” using the Windocks web UI as shown below.
The special case of TDE Encryption
A special file extension, .sqlsys, is used to identify scripts that are run on the Master database of the container prior to mounting user databases (see the dockerfile above). This is used to enable TDE support for containers, where encryption certificates are regenerated to address a known “SQL Server error 15581.” In the example above note how the script is used at both build-time and again at run-time on each container.
Support for Sensitive Credentials
Scripts used in dockerfiles may involve sensitive credentials, which are protected with an encrypted secrets store. Once encrypted, .sql and .sqlsys scripts are referenced with .sqlrunas or .sqlsysrunas file extensions.
Creating an encrypted secret begins by ensuring the user login is included in the source instance Master database, or included in a script that adds the user to the SysAdmin group. To create the encrypted secret, navigate to the \Windocks\bin folder, and open a command prompt and enter “encrypt.” The program prompts for the password, and writes the encrypted output to the encrypted.txt file in the same folder. Open the encrypted.txt file in notepad and copy the complete encrypted string and paste into the \windocks\config\node.conf file as shown. Once the node.conf file is updated, restart the Windocks service.
SQLRUNAS_PASSWORD1=”paste encrypted password here”
Once encrypted, sensitive credentials can be used to run SQL scripts, as shown below.
FROM mssql-2016
SETUPCLONING FULL \path\to\backup.bak
COPY tderefresh.sqlrunas .
RUN tderefresh.sqlsysrunas ‘username’ SQLRUNAS_PASSWORD1
Importance of Containers and Instance support
The ability to apply scripts at build-time and run-time, combined with database cloning, allows for delivery of clones of production environments for dev/test support, and for clone delivery to any SQL Server environment, including Microsoft’s SQL containers (Linux and Windows), or conventional SQL Server instances.
But, neither Windocks nor Microsoft’s Windows SQL containers provide complete parity to conventional SQL Server instances. Windocks recently added SSRS support to SQL Server containers, with the database engine and SSRS running as a Windows service. But, replication is a service that is unlikely to be supportable by Windows SQL containers in the forseeable future. The ability to build images and deliver clones to both containers and conventional instances will continue to be important.
Containers are the future
Our industry is full of hyperbole, but containers will play an increasingly important role over time, and it’s clear that Microsoft is solidly behind SQL Server containers for the newest releases.
We invite you to get started exploring use of SQL Server containers and database clone delivery, by downloading the free Windocks Community Edition at https://windocks.com/community-docker-windows
