Database Cloning for Docker SQL 2017 Linux Containers

DZone 's Guide to

Database Cloning for Docker SQL 2017 Linux Containers

In this article, we look at how database clones are delivered to SQL Server 2017 on Linux containers.

· Database Zone ·
Free Resource

Windocks has evolved over the past two years from an independent port of Docker’s open-source project to Windows, to providing a complete SQL Server data delivery solution. Windocks supports the creation of cloned data environments based on SQL Server backups or storage arrays, with delivery to Microsoft’s SQL Server containers, instances, and Kubernetes clusters. In this article, we look at how database clones are delivered to SQL Server 2017 on Linux containers.

SQL Server 2017 in Linux containers is drawing attention for the speed and agility of containers, as well as database performance. The approach outlined for here should be particularly useful for upgrade testing, as well as general dev/test use. Clones enable delivery of Terabyte databases in seconds, with full read/write operation, while consuming minimal network and storage.   

Building SQL Server Images With Dockerfiles

Dockerfiles are plain text configuration files that define the SQL Server container, and in Windocks 3.0, can be applied at runtime. Each image can include scores of databases with scripts applied for user/group permissions, data masking, and other needs. 

Windocks 3.0 supports external storage arrays and Windows file system database cloning. Windows-based SQL Server images are built with Full or Differential backups, or database files, with each being a full byte copy of the data. Once created, an image supports creation and delivery of clones in seconds with full read/write support, with each requiring less than 40 MB on delivery.

This Dockerfile defines an image that delivers cloned databases to a SQL Server 2017 Linux container.

Image title

ENVUSE_DOCKERFILE_TO_CREATE_CONTAINER ensures the Dockerfile is applied at runtime for each container/environment. Delivery to SQL Server 2017 Linux containers is accomplished with RUN TargetAttach_MSContainerSqlLinux with the parameters shown. SQL Server clones are built with SETUPCLONINGFULL, DIFF (differential) backups, or RAW database files.  

Support for the delivery of database clones over the network is based on SMB, with a file share created on the Windocks host mapped to the Linux host (c:\windocks\data to /windocks/dataexternal as shown above). The Linux setup involves installing SAMBA, and the Docker daemon configured to allow for remote commands.  

A one-time build creates an image that supports an unlimited number of clones. The example below shows the build, followed by a command, to deliver the clone to a new SQL 2017 container. Most of the parameters involved, including the host IP address and target SQL Server image, are included in the image. Only two parameters are required for container creation, including the target port and SQL SA password. 

Image titleManagement of the combined environment is handled by the Windocks container. When it’s time to refresh the environment, the removal of the Windocks container removes the Linux container and associated mounts.    

Working With Sensitive Credentials

Windocks 3.0 introduces encrypted credential support for Windocks images and containers. The workflow described above involves clear text SQL SA passwords, which is the current practice in the use of SQL Server 2017 on Linux. When working with the Windocks SQL Server containers, credentials can be secured using the following methods:   

  • Windocks containers support Windows authentication.
  • Windocks Windows SQL Server containers are created by cloning a SQL Server instance that is configured for use by the Windocks service. Each container inherits SQL logins configured on the parent instance, enabling users with these accounts.
  • Windocks also includes configurable SQL SA credentials for each created SQL container, including an option for no SA passwords, encrypted SA passwords, or passwords in clear text. The three options are configured in the Windocks config folder, node file. SHOW_SA_PASSWORD=”0” or 1, or 2, for no password, encrypted, or clear text, respectively. Restart the Windocks Service following changes to the Windocks configuration.

Windocks encryption is based on the Windows Data Protection API (DPAPI). To encrypt a password, navigate to \Windocks\bin and open a command prompt and enter “encrypt.” The program prompts for a credential string and writes the hashed result to encrypted.txt in the same directory. Open the text file and copy the string into the Dockerfile, in this example we reference the password for an ArrayPassword parameter:

ArrayPassword|1,0,0,0,208,140,157,223,1,21,209,17,140,122,0,192,79,194,1,0, . . .

By incorporating encrypted passwords, either directly in the Dockerfile, the Dockerfiles can be saved and used securely. Once a credential is encrypted the hashed result or environment variable needs to be used in any references to that credential. 

When configured to deliver encrypted credentials, Windocks SQL container SA passwords are delivered in standard Docker client return strings (image below). To unencrypt the credential copy the complete string and save as an encrypted.txt file. RDP to the Windocks server, and copy the encrypted.txt into the \windocks\bin directory. Open a command prompt and enter “decrypt.” 

Image title

The program decrypts the text file and presents the password:

Image title

Working With a Subset of Databases

Users can work with a subset of the databases from an image by using a runtime environment variable: SQL_DB_NAME_OVERRIDES=”dbname1, dbname2”

>docker create -e SQL_DB_NAME_OVERRIDES=”dbname1, dbname2” <imagename>

Working With a Web UI

The Windocks web UI simplifies use for developers and other users. Open a Chrome or Firefox browser and point to the IP address of the Windocks server (local: Images are displayed with required parameters, including the option to work with a subset of desired databases. The image targeting Linux SQL containers only requires user input on the target port and SQL SA password and includes a drop-down selector for working with a subset of the databases in the image.

Image title

Database Cloning for SQL Server Containers and Instances

SQL Server 2017 Linux containers are drawing a lot of attention in a world that is increasingly embracing Linux and open-source technologies. Regardless of the form of SQL Server you use, database cloning is key to enabling an efficient workflow for development and test. Windocks database cloning enables efficient upgrade testing and work with large and complex data environments on the new SQL Server 2017 Linux containers.

You can start exploring these capabilities today by downloading the free Windocks Community Edition http://www.windocks.com/community-docker-windows

database ,devops ,docker ,linux ,linux containers ,sql server ,sql server 2017 ,windocks

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}