Get Started with SQL Server Containers

There's a lot of buzz about Docker containers, and Microsoft will soon release support in Windows Server 2016. The notion of containers for supporting SQL Server is new, and many DBAs understandably ask, "Why use containers?" The reasons are many:

• Containers are fast, lightweight, ideally suited for destructive testing, and many can reduce their VM count by three to five times.

• Containers allow a DBA to provide custom SQL Server images to developers and testers who use container tooling to self-service integrated environments.

• Containers provide efficient support for integrated use of storage systems.

More efficient support for development and test, providing users self-service capability, all while reducing the number of VMs used, simplifying host maintenance, and delivering license savings are some of the reasons for the growing popularity of SQL Server containers. 

Interestingly, Microsoft has been quiet on plans to support SQL Server in Windows Server 2016 containers. Earlier this year Microsoft demonstrated SQL Server Docker support using the future Linux based SQL Server, strongly suggesting  that the SQL Server division will bypass Windows container support altogether. We understand why this would be the case, given some significant limitations in Microsoft's container design, and blogged about the design issues.

WinDocks installs on Windows 8.1, Windows 10, Windows Server 2012, and Windows Server2016, and supports all editions of SQL Server 2008 through SQL Server 2016.

Getting Started

Begin by installing the WinDocks Community Edition. It will detect and configure support for whatever SQL Server edition you have installed. To start the WinDocks daemon, open a Command Prompt as Administrator, and type: 

>docker  -H tcp://0.0.0.0:2375  -d

With the daemon running, open a second Command Prompt to use as the local client (Administrator rights are not needed for this), and enter:

>docker images

The following exercises will be presented as a client running on the WinDocks host.  The same operations can be performed remotely, but involves a more complex command syntax.

>docker  -H=tcp://(ip.address.of.host):2375 images

Build a Custom SQL Server Image

WinDocks delivers SQL Server containers in seconds, along with Docker commands to streamline the use of SQL Server databases. This exercise introduces the use of ADDDB and MOUNTDB commands.

ADDDB copies a SQL Server database (with support for mdf, ndf, and ldf files) into a container.  The database is attached when the container is started and detached when the container is stopped. Once the container is stopped, the container can be committed as a custom image for re-use. Once a custom image is created, it can be shared with a team and each member can quickly provision an instance for individual use.

MOUNTDB mounts a network hosted database (mdf, ndf, and ldf files). As with ADDDB, the database is attached when the container is started and detached when the container is stopped.   The mount point is released when the container is stopped. A large database can be shared through the use of database cloning.  

Using File Explorer open: c:\windocks\samples\TestADDDB folder to see a Dockerfile and the venture.mdf database. Open the Dockerfile using Notepad. Dockerfiles begin with a Source Image, followed by sequential instructions for copying, adding, and running commands. This Dockerfile uses the ADDDB command to copy the venture.mdf into the container.

Step 1: Open a new command prompt window:

>docker build c:\Windocks\samples\TestADDB   

The client return string includes the container ID, container port, and SQL Server sa password.  

>docker start <containerID>    

A subset of the container ID can be used .

The Docker commands and return output is shown below. Each container is a fully isolated SQL Server instance with name space isolation.

The database schema and design can be updated, the container stopped, and a new SQL Server image created. The new SQL Server image can then be used by an entire team on a shared WinDocks host.

>docker stop <containerid>

>docker commit <containerid>  <imagename>

>docker run –d <imagename>

Each SQL Server container is accessible via SQL Management Studio. This workflow illustrates the popularity of Docker and containers for rapid sharing of SQL Server instances in identical, isolated containers. A SQL Server change script can be exported to update the source database as needed.

Rather than relying on multiple VMs for development, a single host can typically support scores of container instances.

Working With MOUNTDB

Using File Explorer, open \WinDocks\samples\TestMOUNTDB and examine the Dockerfile. The Docker file refers to a source image (MSSQL-2014), and the MOUNTDB (dbname) (path). A Docker file can support multiple MOUNTDB commands and multiple secondary databases. SQL Server requires a local path defined by c:\ and otherwise requires a full UNC network path, ie.  \\path\dbname.

The MOUNTDB command creates a container with a mount point that is attached when the container is started and is detached and unmounted when the container is stopped. Where a custom SQL Image based on ADDDB is easily shared with a team, the method used for sharing of a MOUNTDB based container requires the use of cloned databases, each member running a Build to secure and mount a cloned database. 

>docker build c:\Windocks\samples\TestMountDB

>docker start <containerid>

The successful Mount point is written to the client return string, and the database is accessible through normal SQL Server Management tools. 

Build an Integrated .NET + SQL Server Environment

WinDocks also supports integrating SQL Server containers with .NET and other containers. Open the c:\windocks\samples\testdotnet folder to see a simple .NET application. The folder includes a web.cfg file, which is used to integrate a .NET application with a SQL Server instance. Open the web.cfg file using Notepad and note the "connection string" section and references to the host address, port, and SQL credentials. Update these with the Port and SQL sa password from the last exercise.  

Save and close the file (be sure not to save the file as a .txt file).  Run:

>docker Build c:\windocks\samples\Testdotnet

>docker start <container id>

Open a web browser and navigate to the host address and port to view the integrated application.

Congratulations and Next Steps

As you can see, the commands involved in working with SQL Server containers are simple, and each container takes only seconds to create. Containers are quickly gaining popularity for development and test, but also for  support of a broad range of legacy systems.  See our blog on the most common uses here.

Download your copy of the WinDocks Community Edition and start exploring SQL Server containers. Get yours here.