In a data driven marketplace enterprises are retooling development and test for Continuous Integration and DevOps support. Windocks is focused on enabling SQL Server as part of a CI process, with SQL Server containers and database cloning. We recently added support database cloning with Cohesity storage, for use with SQL Server containers and instances. The combination delivers secure TB class databases in seconds, and is ideal for modern CI and DevOps support.
' Database clones are writable databases sourced from storage arrays, or Windows Virtual Hard Drives (VHDs) with full or differential backups. Windocks new support for Cohesity adds to earlier support for Pure Storage, NetApp, and other arrays. 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 with SQL containers. The data image is immutable and can scale to support scores of databases, with security policies such as data masking, encryption, user/group role based access. Database clones provide practical support of development and test with production databases up to 30 TB or more. Windocks supports SQL Server containers and instances for all editions of SQL Server 2008 onward (including SQL Linux containers).
Combining production database clones with upgrade and/or rollback scripts that are optionally applied on demand, yields an efficient SQL Server CI pipeline.
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_DOCKER_FILE_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, and 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.
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, or via Azure Service Fabric, or 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
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:
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 current approach involving building a dev database and then comparing to production. And, this approach is simple to implement and maintain, and dramatically less expensive than working with VMs. To explore this new approach using SQL Server containers download the free Windocks Community Edition.