未加星标

Cloning SQL Server Instances with Containers

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2019 | 作者 红领巾 ] 0人收藏点击收藏

SQL Server containers can be used to “clone” production environments for development and test, delivering isolated SQL containers with production data, and instance configuration.

Windocks SQL containers are created by cloning a locally installed SQL Server instance, and combined with database cloning and T-SQL scripts can deliver a complete replicated “production” environment for dev/test support in seconds. Creating containers by cloning an installed instance yields many benefits, including simplified image maintenance, improved portability, scalability, and security. This article explains how SQL Server containers are combined with database clones and T-SQL scripts, to create images. Images, in turn, deliver complete environments in seconds.

The process outlined also supports database clone delivery to Microsoft’s Docker SQL Server containers (windows and linux), and conventional instances.

Building Custom Images and Containers

Images deliver database clones to conventional SQL instances or containers, and T-SQL scripts are used to configure the environment to reflect the production server being “cloned.”


Cloning SQL Server Instances with Containers

Delivering a replica of a production server begins by building an image. An image begins with a new container, and a Windows Virtual Hard Drive is created and mounted to the container. The backups are restored to the VHD, and build-time scripts are run for data masking or other preparations. When the build-time scripts are complete, the VHD is saved as the custom image. Scripts slated to be applied at run-time are also saved, and applied to each container at run-time.

Dockerfiles are plain text configuration files that include the SQL Server image, databases, and scripts, and provides order to their use in the image build.


Cloning SQL Server Instances with Containers

The dockerfile begins with a base image ( FROM mssql- 2014 ), followed by SETUPCLONING commands for the backups used to build the image. Scripts are copied to the image with COPY commands, and build-time and run-time scripts are determined by relative positioning to an environment variable ENV USE_DOCKERILE_TO_CREATE_CONTAINER=1 . Scripts above the environment variable are RUN at build-time, and scripts listed below are RUN on each container as they are provisioned.

The image is built with a docker command: >docker build -t <imagen ame> c:\path\to\dockerfile . Alternatively, the dockerfile and scripts are selected and “built” using the Windocks web UI as shown below.


Cloning SQL Server Instances with Containers
The special case of TDE Encryption

A special file extension, .sqlsys , is used to identify scripts that are run on the Master database of the container prior to mounting user databases (see the dockerfile above). This is used to enable TDE support for containers, where encryption certificates are regenerated to address a known “SQL Server error 15581.” In the example above note how the script is used at both build-time and again at run-time on each container.

Support for Sensitive Credentials

Scripts used in dockerfiles may involve sensitive credentials, which are protected with an encrypted secrets store. Once encrypted, .sql and .sqlsys scripts are referenced with .sqlrunas or .sqlsysrunas file extensions.

Creating an encrypted secret begins by ensuring the user login is included in the source instance Master database, or included in a script that adds the user to the SysAdmin group. To create the encrypted secret, navigate to the \Windocks\bin folder, and open a command prompt and enter “encrypt.” The program prompts for the password, and writes the encrypted output to the encrypted.txt file in the same folder. Open the encrypted.txt file in notepad and copy the complete encrypted string and paste into the \windocks\config\node.conf file as shown. Once the node.conf file is updated, restart the Windocks service.

SQLRUNAS_PASSWORD1=”paste encrypted password here”

Once encrypted, sensitive credentials can be used to run SQL scripts, as shown below.

FROM mssql-2016

SETUPCLONING FULL \path\to\backup.bak

COPY tderefresh.sqlrunas .

RUN tderefresh.sqlsysrunas ‘username’ SQLRUNAS_PASSWORD1

Importance of Containers and Instance support

The ability to apply scripts at build-time and run-time, combined with database cloning, allows for delivery of clones of production environments for dev/test support, and for clone delivery to any SQL Server environment, including Microsoft’s SQL containers (Linux and Windows), or conventional SQL Server instances.

But, neither Windocks nor Microsoft’s Windows SQL containers provide complete parity to conventional SQL Server instances. Windocks recently added SSRS support to SQL Server containers, with the database engine and SSRS running as a Windows service. But, replication is a service that is unlikely to be supportable by Windows SQL containers in the forseeable future. The ability to build images and deliver clones to both containers and conventional instances will continue to be important.

Containers are the future

Our industry is full of hyperbole, but containers will play an increasingly important role over time, and it’s clear that Microsoft is solidly behind SQL Server containers for the newest releases.

We invite you to get started exploring use of SQL Server containers and database clone delivery, by downloading the free Windocks Community Edition at https://windocks.com/community-docker-windows

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Cloning SQL Server Instances with Containers
本站链接:https://www.codesec.net/view/627747.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(360)