Get Started with SQL Server Containers

One of the most popular aspects of WinDocks is how containers are used to support complex application environments. A .NET app with corresponding SQL Server containers, isolated and identical environments, provisioned in seconds on a shared VM is a good thing! A typical setup involves supporting a team of 8 or even 10 on a 4 core 8 GB VM.

This step-by-step guide uses the WinDocks Community Edition a port of the Docker daemon to Windows 8, Windows 10, as well as Windows Server 2012 and Windows Server 2016. The Community Edition is a free edition, with support for all editions of SQL Server 2008 through 2016. The same steps will work on VMs, whether on your network or on the AWS cloud. The WinDocks Community Edition AMI is offered through Amazon’s AWS Marketplace.

Before you begin, be sure you have the following resources:

• WinDocks Reference
• WinDocks Host IP Address: xx.xx.xx.xx
• WinDocks host RDP login credentials (if using a VM)
• Install the Docker client on your local machine (see the Installation Guide for links)

And, get your free WinDocks Community Edition

Working on the WinDocks Host or Remotely

The following exercises can be run remotely if you choose to copy the following items from c:\windocks\samples to your local machine. We recommend that you login to the WinDocks Host, and run through these exercises initially on the Host.

Note: the Docker command syntax differs when running on the WinDocks Host versus a remote connection:

Docker Client Command on the WinDocks Host: >docker ps

Docker Client on Remote Machine: >docker -H=tcp://{Windocks.Host.IP.Address}:2375 ps

To start the WinDocks daemon open a new command prompt as administrator and enter:

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

RDP to the WinDocks Host, open File Explorer, and navigate to \WinDocks\samples, and note the folders, databases, and scripts. These will be used in the following exercises, and can be copied to your client machine.

Testdotnet, TestADDDB, TestMOUNTDB, link.ps1, customerdata.mdf

The following exercises will be presented as a client running on the WinDocks host. The same operations can be performed remotely, but will require the above Test folders be local to the docker client.

Exercise 1: 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 DockerFiles with 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 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. 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. This window will be used as your Docker client.
Step 2: enter >docker build c:\Windocks\samples\TestADDB Note the containerID, port, and SQL sa password.
Step 3: >docker start (containerID) A subset of the containerID is sufficient.

The Docker commands and return output is shown below. Each container is a fully isolated SQL Server instance, with name space isolation. The container is fully accessible via SQL Management Studio and other tools, by referring to the host address and port.

The database schema 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.

Step 1: >docker stop
Step 2: >docker commit
Step 3: >docker run –d

This 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.

Exercise #2: Working with MOUNTDB

Using File Explorer, open \WinDocks\samples\TestMOUNTDB . The Docker file refers to a source image (MSSQL-2012), and the MOUNTDB . A Docker file can support multiple ADDDB and 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, and each user runs a Build to secure and mount a cloned db. In this case we’ll mount the local database:

Step 1: >docker build c:\Windocks\samples\TestMountDB
Step 2: >docker start

The successful Mount point is written to the client return string, and the database is accessible through SQL Server Management tools. Unlike the ADDDB example, where the database is attached and saved in the committed image, a mounted database is detached when the container is stopped and will not be preserved in a committed image. To work with mounted databases, changes in the data need to be saved to a new snapshot or new database, which is mounted through a new container build process as shown above.

Exercise #3: Build a custom .NET container with integrated SQL container

WinDocks supports integrating SQL Server containers with .NET and other containers. Open the testdotnet folder to see a simple .NET application. The folder includes a web.cfg file that 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.

Edit the Host address if needed, the Port, and SQL Server sa password with the details of a running SQL Server container. Save and close the file (be sure not to save the file as a .txt file). Run:

Step 1: >docker Build c:\windocks\samples\Testdotnet
Step 2: >docker start

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

Exercise #4: Use a local Docker client and Container Removal

If you completed the steps above working on the WinDocks host, copy the Test folders and the Link.ps1 PowerShell script to your local machine, saving them in the same folder as your Docker client. Repeat some of the steps used above to become comfortable with the remote Docker syntax. Now, let’s see how many containers are present, and do some container cleanup.

Step 1: >docker -H=tcp://:2375 ps
Step 2: >docker -H=tcp://:2375 rm

Exercise #5: Working with PowerShell Scripts

You should have copied the Link.ps1 PowerShell script to your local machine in Exercise #4. Open the Script with Notepad to edit the host address (“local host” or remote IP as shown):

The script provisions a new SQL Server container using the base SQL Server image, performs a string replace on the web.config file with the SQL Server port and sa credentials, and then builds the testdotnet folder with an updated web.config file. Finally, the script starts the .NET container, and returns of the combined container details.

Run the script using the following commands in PowerShell.

CD \
Set-Executionpolicy -Scope CurrentUser -ExecutionPolicy UnRestricted
confirm Y
.\link.ps1

The script automates the process of provisioning a complex application:

Congratulations and Next Steps!

This completes our step-by-step Introduction to use of SQL Server containers.

WinDocks is extensible to support Windows Services, executables, and applications using Docker Exec and CMD operations. Support for these operations is beyond the scope of this introduction. Please contact WinDocks to explore how WinDocks can be used to meet your particular needs.

WinDocks Community Edition Download