Add Databases to CI With Production Database Clones

DevOps involves source control and automated application builds with binaries as immutable artifacts for a repeatable DevOps process. Updating SQL Server databases, however, is not accomplished so simply and must be upgraded in place. Current SQL DevOps practices involve building a development database with schema and objects and static data for functional testing. The “development database” is succeeded by a “production like” database for later stages and ultimately, upgraded scripts are applied to production. This approach is complex and breaks the principle of working with unchanging artifacts throughout the DevOps process.

Fortunately, a simpler approach with production database clones and SQL Server containers delivers simplicity, horizontal scalability, and economy. Production database clones are delivered in seconds with immutable writable replicas of production for higher fidelity releases. Full disclosure: I am a principal at Windocks where we focus on SQL Server containers and database cloning, but these same approaches should work with storage array cloning with Bash or other scripting. 

Production Database Clones

Database clones are writable databases sourced from storage arrays or Windows Virtual Hard Drives (VHDs) with full or differential backups. Clones are delivered in seconds, occupy < 40 MB on delivery, and up to 40 clones can be run on a single 4 core, 32 GB server using containers. The data image is immutable and can scale from one to scores of databases, with security policies such as data masking, encryption, user/group role-based access and other security policies. Database clones provide practical support of development and test with production databases up to 30 TB or more. Windocks supports Cohesity, Pure Storage, NetApp, and EMC, along with Windows VHDs with SQL Server containers for all editions of SQL Server 2008 onward.  

Combining production database clones with upgrade and/or rollback scripts that are optionally applied on demand and yields an efficient SQL Server CI pipeline.  

Database Clones with Git

The relational database CI workflow combines database clones with SQL Server containers and Git pulled scripts. Clones are delivered from an image, which is built from a plain text dockerfile, and are tagged to projects and user-defined stages (Dev, Test, UAT, etc.). The dockerfile below uses a backup and applies a data masking script to the production data and pulls scripts from the Git repo into each container. The image is built with a standard Docker client or web UI, using >docker build -t imagename path/to/dockerfile

    FROM mssql-2016                                                                                                                                        SETUPCLONING FULL dbName path/to/backup/of/production/database                                        COPY datamask.sql .                                                                                                                                                    RUN datamask.sql                                                                                                                                                        ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1                                                                                      RUN /path/to/git.exe clone path/to/scripts/repo path/to/container

The resulting image delivers a writable cloned database mounted to a SQL Server container with scripts pulled from the Git repo and with the data masking script applied. A developer can commit scripts to the Git repo and create new containers following each commit or have Azure DevOps automate the creation and testing following each commit. Containers are created with a Docker command that specifies the scripts to be applied to the database. 

>docker create –e RUN=“path/in/container/to/upgrade-script.sql” imagename

Once the upgrade and rollback scripts are completed, the test team begins their work. The command below creates a container with a terabyte database with upgrade and rollback scripts applied and is delivered in 30 seconds.  

>docker create -e RUN=”path/in/container/to/upgrade-script.sql, path/in/container/to/rollback-script.sql” imagename

A tester can now create three containers, one with the upgrade script, a second with the upgrade and rollback script applied, and a third with simply the database. Work progresses with user controlled and automated testing with scripts applied as outlined.  

Multi-Tier App Support

SQL Server containers are delivered as conventional named instances and are easily incorporated into a multi-tier application with .NET or Java front-end and middle tier applications running on the same host using containers, via Azure Service Fabric, AKS, or other platforms such as OpenShift. Connection strings for each SQL Server container can be controlled with assigned ports and SQL sa passwords (or Windows Authentication). Standard docker client commands are used to assign ports and credentials:

>docker run -d -p <port> -e SA_PASSWORD=”password” <image>

Security

A DevOps process with production data must be secure, and the DevOps process outlined includes a complete set of security provisions and is in use today by enterprises around the world:  \

• SQL Server containers are delivered as named instances, support AD, and Windows Auth.
• SQL containers are created by cloning a locally installed instance and avoid security concerns associated with public image repos. A local installed and configured instance reflects enterprise security policies with proven SQL Server namespace isolation.
• Containers support the full range of SQL Server encryption with External Key Managers and database data masking, user/group role-based access, linked servers, etc.
• Dockerfiles support encrypted credentials to avoid plain text credentials being exposed.
• Remote calls are protected with TLS token and session encryption.
• SQL containers also reduce the attack surface when compared to VMs, with an average 10:1 reduction in VMs used. This also drives a significant economy. When implemented as a dev/test environment using SQL Azure backups, organizations save up to 80 percent compared to supporting dev/test on SQL Azure databases.

Summary

The combination of writable database clones with stateful containers allows delivery of secure TB class environments in seconds and provides the highest fidelity dev/test outcomes. Performance and issues with data will surface and be resolved more reliably than the current approach involving building a dev database and then comparing to production. And this approach is simple to implement and maintain and is dramatically less expensive than working with VMs. To explore this new approach using SQL Server containers download the free Windocks Community Edition  http://windocks.com/community-docker-windows