Tips on how to build your first SQL Server container

Source:- techtarget.com

Navigating the new world of containers might seem daunting, but the examples outlined here will walk you through the process of creating and managing containers for SQL Server.
Support for containers is one of the most important new features in Windows Server 2016 and, in turn, SQL Server.
And if you’re looking to virtualize your databases, there are good reasons to put them in a SQL Server container.
Containers virtualize at the operating system level, making them far more lightweight than a virtual machine (VM). Because of that, you can run many more containers than VMs on a single host server.

In addition, containers are stateless, and can be quickly created, cloned and deleted. The data in a container is deleted along with it. That makes containers very useful for development and test scenarios where you may want to quickly spin up a SQL Server instance without waiting for lengthy installation processes. They’re also a good fit for other uses, such as maximizing database density and isolating applications in a multi-tenant architecture for better management control.

I’ll show you how to use Microsoft’s prebuilt SQL Server Express image to run SQL Server in a Docker container, and how to attach databases to the container and manage the SQL Server instance.

An image is now also available for the Developer Edition of SQL Server 2016, and there’s one for the public preview of the next SQL Server release, which adds Linux support. You can also build your own SQL Server containers for the 2016 Standard and Enterprise Editions.

Enabling containers in Windows Server

To run SQL Server in a container, you must first enable container support in Windows Server 2016. To do this, open Server Manager, use the Manage option and select the Add Roles and Features option. Click Next on the Add Roles and Features Wizard until you get to the Features page, which you can see in Figure 1.

On the Features page, check the Containers feature and click Next. Then, click Install to complete the process. The installation takes a couple of minutes; then, you must restart the Windows Server system.

Next, you need to install Docker. To do so, open an elevated PowerShell prompt and run the following commands:

Install-Module -Name DockerMsftProvider -Repository PSGallery -Force
Install-Package -Name docker -ProviderName DockerMsftProvider

After installing Docker, you will again need to reboot the system. You can then check your Docker installation by opening a command prompt and running the Docker version command.

Installing the SQL Server Express image

Once Docker is installed, you can go ahead and download the public SQL Server Express image.

A container is a running instance of an image that contains a file system and code to use at runtime. You can run the following Docker Pull command to download the SQL Server 2016 Express image:

docker pull microsoft/mssql-server-windows-express

Downloading this image will take some time, as it contains the Windows 2016 Server Core operating system and SQL Server 2016 Express. After the Docker Pull command completes, you can check the available images by running the Docker Images command, as you can see below:

PS C:\Users\Administrator> docker images

REPOSITORY                       TAG              IMAGE ID

microsoft/dotnet-             dotnetapp
samples                             -nanoserver      a331d851e765

microsoft/mssql-
server-windows                  latest                6fb5a1dbd3c8

microsoft/aspnet                latest                 e761eca2f8df

microsoft/
windowsservercore            latest                4d83c32ad497

microsoft/mssql-
server-windows-express    latest                 d5cc84b603b4

Running, stopping a SQL Server container

Now you’re ready to start running the SQL Server container by using the Docker Run command, as in the following example:

docker run -d -p 1433:1433 -e sa_password= TempPa550wd# -e ACCEPT_EULA=Y microsoft/mssql-server-windows-express

The -d parameter is shorthand for detach — it causes the container to run in the background. The -p parameter specifies the TCP port that will be opened on the host and inside the container. This example binds port 1433 on the host to port 1433 on the container. Any traffic coming into the host on port 1433 will be forwarded to container port 1433.

The -e sa_password parameter provides the sa, or system administrator, password for the containerized SQL Server instance — you need to be sure this passes Windows password complexity requirements.

Finally, the microsoft/mssql-server-windows-express value specifies the repository image to use to run the container.

At this point, the SQL Server container will be running. To be sure, you can use the Docker PS command to generate a list of running containers:

PS C:\Users\Administrator> docker ps

CONTAINER ID         IMAGE                    COMMANDs            CREATED
98c9cb874f27    microsoft/mssql-   “cmd /S /C ‘powers…”  43 minutes ago
server-windows-
express

If you decide later that you no longer need the container, you can use the Docker Stop, Docker Kill or Docker rm (short for remove) commands to dispose of it. The Docker Stop command attempts to end the process naturally, but Docker will forcibly stop it if it doesn’t comply within 10 seconds. The Docker Kill command immediately deletes the container without giving it an opportunity to exit gracefully. The Docker rm command is typically used to remove a container that has already been stopped, but use of the -f flag will cause it to first kill a container before removing it.

To end a SQL Server container’s operations using the Docker Stop command, enter the first three characters of the container ID, which serve as a unique identifier:

docker Stop 98c

If the command works properly, the stopped container will still exist. To remove it, you can then run the following:

docker rm 98c

Attaching databases to a container

Starting a container is all well and good, but containers don’t have persistent storage, and what good is SQL Server without databases? Fortunately, Docker has commands that can mount a volume to the container to store your database files. The volume can reside on the container host or in a file share, or on shared storage where the container host can access it.

The following example shows you how to use the Docker Run command to start a SQL Server container, as well as to attach a database where the data and log files are stored in the host’s c:\temp directory:

docker run -d -p 1433:1433 -e sa_password=TempPa550wd# -e ACCEPT_EULA=Y -v C:/temp/:C:/temp/
-e attach_dbs=”[{‘dbName’:’EditorialDb’,’dbFiles’:[‘C:\\temp\\editorialdb.mdf’,’C:\\temp\\ editorialdb_log. ldf’]}]”
microsoft/mssql-server-windows-express

In this example, the -v parameter is used to mount the host volume c:\temp to the container. You might note the use of the Linux-style, forward-slash naming convention.

Then, the -e (for environment) switch is used in conjunction with the attach_dbs parameter to specify the database that will be attached and its data (.mdf) and log (.ldf) files. As you can see above, attach_dbs uses a JSON format to specify the database and files.

Connecting to the container

Next, you can use the Docker Exec command to launch the SQLCMD utility from within the new container, using the first three characters of its ID:

docker exec -it 5e9 sqlcmd -S.

Here, SQLCMD will use Windows authentication to connect to the SQL Server instance. The -it parameter creates an interactive command prompt for the SQLCMD utility, as you can see in Figure 2. Once connected, you can run any supported T-SQL command.

You can also connect to the containerized instance of SQL Server using SQL Server Management Studio (SSMS). To connect to the SQL Server container from the host via SSMS, you’ll need to know its TCP/IP address, as well as the port that SQL Server uses, even if you’re using the default 1433 port. You can use the Docker Inspect command to retrieve the container’s TCP/IP address — again, you need to include the shorthand ID for the container.

docker inspect -format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}’ 5e9

You also need to use SQL Server authentication when you open a database connection from SSMS.

I found that the sa account was disabled in the SQL Server Express image, and it needed to be enabled using SQLCMD before I could connect. You can see the required SSMS database connection dialog in Figure 3.

You can see SSMS connected to the containerized SQL Server 2016 Express instance in Figure 4.

To connect to the SQL Server container from another networked system, you’ll need to specify the IP address of the host server, the SQL Server port number specified in the Docker Run command and the SQL Server system administrator account credentials.

At this point, using and managing the SQL Server container is just like working with any other SQL Server instance. When you eventually dispose of the container, all of the changes made to the attached database files will be persisted. But any changes made within the container itself — for example, enabling accounts, changing passwords or modifying configuration settings — will be discarded.