There has been a lot of buzz about Docker and containers this year, and earlier this Fall Microsoft released container support in Windows Server 2016. WinDocks is a team of former Microsoft engineers, and we released an independent port of Docker’s open source in March of 2016. Full disclosure, I am a co-founder at WinDocks.
In this article we’ll take a look at the basics of containers, and the most popular use by SQL Server DBAs.
Docker, Containers, and the SQL Server
Containers originated in 2000, with the introduction of BSD UNIX “jails,” providing process and user isolation for application multi-tenancy. In the spring of 2013 Docker Inc introduced a new Linux design that won industry-wide support by Red Hat, Amazon AWS, Azure, and Google Cloud. In a move that would portend Microsoft’s embrace of Linux, the company announced plans to incorporate Docker support into Windows Server 2016. My firm, WinDocks, also organized to deliver a first-class solution for the SQL Server community.
Both IIS and SQL Server have had strong multi instance support for years, so it’s reasonable to ask “why use containers?” A SQL Server container is a named instance with a new level of speed, economy, and portability.
Speed: SQL Server containers are instantiated in seconds with data and specific configurations. Containers are well suited for short-lived instances needed by Developers and QA, and can be deleted and replaced on-demand.
Economy: development and QA teams use isolated containers on a shared VM, and typically reduce the number of VMs used by a factor of 5-10x. VM maintenance is simplified, and Microsoft license costs are dramatically reduced.
Portability: container images are immutable, and can be moved between systems or a public cloud.
SQL Server containers are also part of a rapidly growing industry-wide ecosystem of software and services, as Docker’s design is supported by Red Hat, Microsoft, AWS, Google, and other leading firms. It makes sense to explore use of SQL Server containers simply to be aligned with industry trends.
An Introduction to SQL Server Containers
Containers are defined by Dockerfiles which include a sequence of steps to build a container. In this example the Docker file specifies use of SQL Server 2014 standard, and a NetApp Snap Mount command mounts the LUN to the host with an environment variable for the ContainerID. The MOUNTDB command then mounts the database to the container. Finally, a SQL Server script is run to configure user accounts.
Databases can be managed using two methods. Databases can be copied and run “in container,” using an ADDDB command. Alternatively, databases are mounted using the MOUNTDB command. Mounts are preferred for large databases, to avoid copying data across the network and consuming server storage. Snapshots and clones support multiple containers, with one mount to each clone. Both methods deliver good performance. A 1 TB database can be mounted and delivered in less than 1 minute. In other cases, up to 30 databases totaling 400 GB can be run “in container” in 2 minutes.
Users are limited to operations on their container and its private file system. The example below shows a Docker client building a container with MSSQL-2014 and venture.mdf. This example includes SQL sa credentials, which is optional. A unique ContainerID and container port are provided, and the container behaves just like any SQL Server instance and is available to SQL Management Studio and all other standard tools.
Supporting Complex Application Environments on a Shared Server
While containers are great for delivery of SQL Server environments, they support the full development stack with .NET and a range of open source frameworks. Support of a globally distributed development team is achieved efficiently with a single shared server hosting both .NET and SQL Server environments.
Once the SQL Server container is available, the port and connection credentials can be edited in the .NET web.config file, as shown here:
In most cases a PowerShell script will be used to automate this process. The script generates the SQL Server container, parses the connection string details, pulls the latest code from Git hub, edits the webconfig file, and then builds and starts the .NET container. Each developer can run the script on demand, or the delivery of environments can be handled by a DevOps engineer. Upon completion the script can distribute environments to the team via email.
A shared VM can support up to 20 simultaneous running containerized environments, each provisioned in just 100 seconds. The front-end and back-end teams can replicate bugs quickly and validate tests by simply choosing the appropriate branch in the build script.
One user supports up to twenty SQL Server containers on an 8 core server with 96 GB of RAM, with each container supporting twenty five databases totaling 400 GB. Prior to adopting containers it took over an hour to build each VM. Now each container is provisioned in under two minutes. They benefit from a 20x reduction in VMs, a 5:1 reduction in CPU cores, and dramatic financial savings in MS license costs.
While SQL Server containers are most commonly used for support of Development and QA, other uses are emerging. Containers provide efficient support for DR Testing, and some are applying containers for support of back-end systems for legacy apps, such as SAP or MS Dynamics. Looking ahead, SQL Server containers can also be expected to be used in support of Continuous Integration and Delivery pipelines. AWS, Azure, and other leaders are investing heavily to popularize automated build and testing for software delivery, and SQL Server containers are particularly well suited for this emerging use case.
Conclusions
Containers are a dramatic step forward for support of developers and test, and the ability to support SQL Server in containers should be a boon for Windows based software development. SQL Server environments can be provided daily, with the latest production data, using simple automated processes. Developers should be happy with daily, on-demand access to fresh isolated environments. Management should be happy with added productivity, and cutting Microsoft license bill in half!
Methods used for working with SQL Server data, and use of file shares to support delivery of production databases with containers will be explained in the next article, How to use Windows hosted file shares to support SQL Server containers
References
- Download the WinDocks Community Edition here
- A guide to using SQL Server containers: Get Started with SQL Server containers
- A SQL Server DBA blogs on using containers: SQL Server & Containers – Part Four
He loves to write and is a frequent contributor to technical web sites. He has recently expanded his focus to enable SQL Server containers as part of a Continuous Integration and DevOps process.
View all posts by Paul Stanton
- How to use Windows hosted file shares to support SQL Server containers - February 23, 2017
- Automate Delivery of SQL Server Production Data Environments Using Containers - January 12, 2017