We have just returned from presenting at the Pure Accelerate conference, where we demonstrated the industry's first Docker based data delivery platform, with support for delivery to SQL Server container, instances, and Kubernetes clusters.
Windocks 3.0 introduces support for sourcing, managing, and delivering SQL Server data from Pure Storage arrays to any SQL Server target environment. This article covers database delivery to SQL Server instances and containers. Windocks 3.0 includes Dockerfiles for delivery to SQL Server 2017 Linux containers as well.
Windocks runs on Windows 8.1 and 10 Pro and Enterprise editions, Windows Server 2012 R2, and Windows Server 2016. All editions of SQL Server 2008 forward are supported, and Windocks continues to support creation of Windows based database clones based on Virtual Hard Drives (VHDs).
Windocks 3.0 images can include Dockerfiles that define data sources and targets, and that are applied at container creation. This “run time” supported Dockerfile is ideal for support of workflows that involve cloning volumes on storage arrays.
This Dockerfile builds an image that clones a Pure array volume, with databases mounted to a SQL Server instance on the LAN, or locally on the Windocks host. Two user accounts are used to address SQL Server “double hop” permission needs, including a SQL user/password and a domain user, with both included in the target SQL Server instance Sysadmin group. Permissions to mount the databases are provided either in the \windocks\config\node.conf file, or in the Dockerfile as shown below (domain\user is Windocks\Support1).
Images are built with >docker build -t, with ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1 ensures the Dockerfile is applied at run time. RUN SourceClone_San_Pure identifies the source as a Pure Storage array, with the required parameters on separate lines for readability. RUN TargetAttach_SQLWindows identifies the target as a Windows based SQL Server instance, with the parameters needed. Images can include scores of databases, with each described with a separate MOUNTDB command and file path to the database files.
The Docker command and Dockerfile is included below. Refer to \windocks\samples\TestPureStoragetoSqlInstance for a ready-to-edit sample file.
Note: when delivering databases to instances on the Windocks host the Network Share name and path are not needed, and can be removed from the Dockerfile.
>docker build -t (imagename) c:\windocks\samples\TestPureStoragetoSqlInstance
FROM mssql-20XX
ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1
RUN SourceClone_San_Pure
SourceVolume|SQL-DATA01
DestinationVolume|$ContainerId
MountPoint|C:\windocks\$ContainerId
HostName|c220m4-server
ArrayIP|10.21.XXX.XX
ArrayUser|username
ArrayPassword|password
NetworkShareName|$ContainerId
NetworkSharePath|C:\windocks\$ContainerId\data
NetworkShareUsers|domain\User
RUN TargetAttach_SqlWindows
InstanceName|$SqlInstanceName
SqlUserName|$SqlInstanceUserName
SqlPassword|$SqlInstancePassword
MOUNTDB customers c:\windocks\$ContainerId\data\customerdata.mdf c:\windocks\$ContainerId\data\customerdata_log.ldf
MOUNTDB operations c:\windocks\$ContainerId\data\operations.mdf c:\windocks\$ContainerId\data\operations_log.ldf
Once the image is available, environments are delivered with >docker create. Each create produces an associated container which represents the data environment mounted to the instance. Databases should be detached prior to the environment being refreshed, to avoid database name collisions, and this can be done by deleting (removing) the docker container. The Docker command and runtime parameters for deliver is:
>docker create -e $SqlInstanceName="Windocks1\SQL2016" -e $SqlInstanceUserName="username" -e $SqlInstancePassword="SQLpassword" (imagename)
Windocks records the list of storage array volumes, snapshots, and mount points, and cleans up the resources on deletion of the “container.”
This Dockerfile clones a Pure array volume and mounts databases to a Windows SQL Server container. The target is local to the Windocks host, simplifying the Dockerfile and does not require RUN TargetAttach. The sample Dockerfile is located in \windocks\samples\TestPureStoragetoWindocksContainer folder.
>docker build -t (imagename) c:\windocks\samples\TestPureStoragetoWindocksContainer
FROM mssql-20XX
ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1
RUN SourceClone_San_Pure
SourceVolume|SQL-DATA01
DestinationVolume|$ContainerId
MountPoint|C:\windocks\$ContainerId
HostName|c220m4-server1
ArrayIP|10.21.XXX.XX
ArrayUser|pureuser
ArrayPassword|password
MOUNTDB customers C:\windocks\$ContainerId\data\customers.mdf C:\windocks\$ContainerId\data\customers_log.ldf
MOUNTDB operations C:\windocks\$ContainerId\data\operations.mdf C:\windocks\$ContainerId\data\operations_log.ldf
Once the image is available, environments are delivered with >docker create, followed by >docker start , or with >docker run -d .
Developers often choose to work with a subset of the databases from an image. This is supported with a run time environment variable as shown:
>docker run -d -e SQL_DB_NAME_OVERRIDES=”dbname1, dbname2”
When working with SQL Server instances users can mount updated databases alongside older copies. To avoid database name collisions databases delivered with SQL_DB_NAME_OVERRIDES have the date/time stamp appended to the database name. This behavior is not implemented for containers, as containers support a one-time image delivery. Additional databases can be mounted manually once the container is running, and databases being added can be named to avoid database name collisions.
Windocks requires the Pure Array password as included in the Dockerfile to be encrypted. To encrypt navigate to the \windocks\bin directory, open a command prompt and enter “encrypt.” Windocks encryption is based on the Windows Data Protection API (DPAPI), and prompts for the password. Once entered the encrypted password is written to an “encrypted.txt” file located in the same directory.
Open the encrypted.txt file, and copy the complete encrypted string into the Dockerfile for the ArrayPassword.
ArrayPassword|1,2,0,0,0,56, . . .
Dockerfiles with the encrypted password can be safely used without compromising security. The hashed string can also be decrypted by using the decrypt program, so only trusted users should be provided access to the Windocks server when working with sensitive credentials.
The Windocks team is excited to be working with Pure Storage, and enjoyed presenting at Pure's annual Accelerate conference. Windocks is the first and only solution available for complete management of cloning and data delivery from Pure arrays to all SQL server environments. This is particularly compelling for organizations interested in evaluating or planning migration of SQL Server 2017 on Linux containers.
Get started today by downloading the free Windocks Community Edition. Check out a short video that demonstrates these capabilities here.
In this article we look at how SQL Server database clones with TDE encryption are delivered to Windocks SQL containers and the official Docker SQL...
In this article we look at the new support in Windocks 3.0 for delivery of SQL Server database clones to the official Microsoft Docker SQL Server...
A step by step introduction to working with SQL Server containers using Docker commands and PowerShell scripts.