Get Started with SQL Server containers


Logo

Windocks supports containers with SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016 on Windows Server 2012 R2, Windows Server 2016, 2019, and 2022. Prior to starting, have the following resources available:

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

Working with Windocks on the 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://:2375 ps

To start the Windocks daemon, right click on the Windocks.bat file on the desktop and Run As Administrator. Or 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. Also, one minor edit to the host location in the link.ps1 PowerShell script (which is addressed in Exercise #5)

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 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. An example of this process is illustrated with the use of NetApp FlexClone support.

Using File Explorer: open \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.

  • Step1: Open a new command prompt window
  • Step 2: Enter docker build c:\Windocks\samples\TestADDB -- The Windocks client return string includes the container ID, container port, and SQL Server sa password.
  • Step 3: docker start <containerID> -- A subset of the container ID can be used to Docker operations.

The Docker commands and return output is shown below, along with a view of the SQL Management Studio Object Explorer. Each container is a fully isolated SQL Server instance, with name space isolation.

SQL containers

SQL containers

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>

SQL containers

Each SQL Server container is accessible via SQL Management Studio as shown above. 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.

Exercise #2: Working with MOUNTDB

Using File Explorer, open \Windocks\samples\TestMOUNTDB and examine the Dockerfile. The Docker file refers to a source image (MSSQL-2012), and the MOUNTDB . 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, and each member running a Build to secure and mount a cloned db. This process is illustrated at http://www.windocks.com/lps/learnmore

docker build c:\Windocks\samples\TestMountDB

docker start <containerid>

SQL containers

SQL containers

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

Windocks supports a rich set of options for working with SQL Server, and also 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.

SQL containers

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:

docker Build c:\windocks\samples\Testdotnet

docker start <containerID>

SQL containers

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

SQL containers

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.



docker-1.7.0 -H=tcp://:2375 ps

docker-1.7.0 -H=tcp://<Windocks.IP.Host.Address>:2375 rm <containerid> <containerid2>

Unused containers will consume system resources, port, memory, and cpu. Containers that aren’t being used can be removed (the rm command will stop the container prior to deleting it).

SQL containers

Exercise #5: Working with PowerShell Scripts

You should have copied the Link.ps1 PowerShell script to your local machine in Exercise #4. If you didn’t, RDP to the Host and copy the script (or run it locally on the Host). Open the Script with Notepad to edit the host address (“local host” or remote IP as shown):

SQL containers

Study the script. Notice that it 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 to the user. Run the script using the following commands in PowerShell.

CD \

Set-Executionpolicy -Scope CurrentUser -ExecutionPolicy UnRestricted



.\link.ps1

SQL containers

The script automates the process of provisioning a complex application:

SQL containers

Next steps

This completes your Introduction to Windocks and use of SQL Server containers. If you are evaluating Windocks on a private network, you can explore the use of MOUNTDB with remote network hosted databases. ADDDB support is currently limited to databases located in the same folder as the Dockerfile (this will change in the next Windocks update). Other planned enhancements include use of SQL Server backups. 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.

Community Support

Join the community to share tips, or solve problems at our Linkedin group

For suggestions for new features or support, contact us by email at support@windocks.com