In this article we look at new capabilities in Windocks 3.0, featuring database cloning for containers and conventional instances, images with run time applied Dockerfiles, user driven database selection from complex images, and encrypted credential support.
Windocks 3.0 introduces support for sourcing data from Storage arrays and Windows SQL Server clones, with delivery to any SQL Server container or instance (including Microsoft’s Docker SQL Server containers). In this article we’ll look at new support of database cloning for SQL Server instances.
Database clones enable delivery of Terabyte class data in seconds, with full read/write operation, while consuming minimal network and storage. Cloning is growing in popularity for greater speed, support for CI/CD pipelines, storage savings, and improved data governance.
Dockerfiles are used to build images and Windocks 3.0 allows Dockerfiles to be applied during run time. Run time Dockerfile image support simplifies use of network based resources (cloning storage volumes will always be a run time event), and simplifies end user support with a web UI. Each image can include scores of databases, with scripts for user/group permissions, data masking, and other needs.
Windocks 3.0 supports use of external storage arrays, or the Windows file system based database cloning. Windows based SQL Server clones are built with the Windows Storage Management API and Virtual Hard Disks (VHDs). Images are built with Full or Differential backups, or database files, with each being a full byte copy of the data. Clones of the parent image are delivered in seconds with full read/write support, and require less than 40 MB.
The Dockerfile below delivers cloned databases to a SQL Server instance on the LAN (or on the Windocks host). The process involves the use of two user accounts to address “double hop” permissions, by including a SQL user and domain user. Both accounts are included in the target SQL Server Sysadmin group. The Domain user is provided permission to the database clones via Windocks configuration (\windocks\config\node.conf), or via the Dockerfile as shown below.
Windocks samples (\Windocks\samples) includes a TestLazyBuildSetupCloningTarget. The image is built with a >docker build -t command.
The ENV USE_DOCKERFILE_TO_CREATE_CONTAINER ensures the Dockerfile is applied at run time for each container/environment. Delivery to SQL instances is accomplished with RUN TargetAttach_SQLWindows with the parameters shown. SQL Server clones are built with SETUPCLONING, with FULL, DIFF (differential) backups, or RAW database files.
Management of the databases on the instance is handled by Windocks. Users can simply delete the created container and Windocks will manage the database detach process, and clean up the clones and mount points.
Users can work with a subset of the databases from an image by using a run time environment variable: SQL_DB_NAME_OVERRIDES=”dbname1, dbname2”
>docker create -e SQL_DB_NAME_OVERRIDES=”dbname1, dbname2”
Updated databases can be added alongside older copies, as DB_NAME_OVERRIDES appends a date/time stamp to avoid name collisions. The web UI provides developers and other users a simple drop-down tool for this step:
Dockerfiles commonly require sensitive credentials, and represents a serious security concern. Windocks includes support for encrypting credentials using the Windows Data Protection API (DPAPI). 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 file and copy the text string into the Dockerfile. The example below is a Dockerfile that works with external storage arrays, where the Array Password provides access to production data.
ArrayPassword|1,0,0,0,208,140,157,223,1,21,209,17,140,122,0,192,79,194,151,235,1,0, . . .
Once the encrypted password is applied the Dockerfile can be saved and reused with safety. Note, once encrypted, references to the credential require use of the encrypted form.
The Windocks web UI provides an alternative to Docker commands. Open a Chrome or FireFox browser and point to the IP address of the Windocks server (local: 127.0.0.1). Images are displayed with required parameters, including the option to work with a subset of desired databases.
The resulting mix of data environments (containers and SQL Server instances) are listed below. When it is time to refresh the environment, simply delete the container previous environment, and replace it with an updated image.
The public Cloud is leading to an increasing variety of SQL Server services, with AWS RDS, Azure Managed SQL instances, and others. SQL Server containers provide an important service-like capability for on premise and private cloud use, and run on AWS, Azure, as well. Windocks now supports both SQL Server containers and instances with data sourced from storage arrays or Windows database cloning.
Regardless of the form of SQL Server you use, database cloning (either with storage arrays or natively on Windows hosts) should be part of the plan. The Windocks database cloning highlighted in this article enables complex environments to be delivered quickly, and efficiently for Dev/Test and for Reporting and BI.
Start to explore these exciting capabilities today by downloading the free Windocks Community Edition
In this post we look at how Windocks combination of containers with database clones yields enterprise data images that enhance governance and...
Migrations of SQL Server databases to a recent release is an ongoing challenge of SQL Server DBAs. In this article we take a look at how containers...
In this post we take a look at Windocks 2.0 with integrated database cloning and web UI. Database clones enable delivery of terabyte class...