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

'

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

DevOps image 2 750 x 356

Database clones and 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_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.

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, 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 -e SA_PASSWORD=”password”

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, and support AD and Windows Auth.
  • SQL containers are created by cloning a local 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 significant economy. When implemented as a dev/test environment using SQL Azure backups, organizations save up to 80% compared to supporting dev/test on SQL Azure databases.

Summing up

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.

     RSS Feed