SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Getting Started with Database containers

By Steve Jones, (first published: 2019/02/04)

Containers are the hottest thing these days in software development. Many developers are excited by them and excited to begin using them in their work. I've had plenty of developers asking customers and clients if SQL Server is available in a container. It is and this is a short introduction to getting you up and running with containers on Windows.

This article will not go into the details of the architecture and internals of containers and images, but rather stick to getting SQL Server running in a container on your workstation. This is not a production level system, but one that you can use for development purposes.

Docker

The container technology we will use on Windows is Docker. Docker is a company, a server process, a client process, and some other software. Docker is the main, but not the only, company that is building and distributing container technology. Microsoft works with Docker containers and so we'll start there. 

The first step is to get Docker running. You can get information here: https://docs.microsoft.com/en-us/virtualization/windowscontainers/quick-start/quick-start-windows-10, but essentially you need v1607 or later and the Hyper-V technology enabled. If you haven't added Hyper-V, this link should help you.

For the version, verify you have an edition of Windows 10 that runs Docker. You can use Winver. Most of you should be above the minimum. Here is the version I have on my laptop:

Once you download the Docker installer, run it and complete the next, next, next sequence. When this finishes, you should have a Docker icon (the whale) in your task bar.

If you click the icon, you should see a menu similar to this. You might see "Switch to Linux containers" if you are running Windows. If you are running Linux containers, you'll see what I see.

At this point, the Docker daemon is running and it's time to start getting images.

Getting an Image

An image is a template of the container you want to run. Think of this as a template of a VM that is used to create the actual VM that you connect to and interact with. We need to download an image to our machine and then we can use that to create a container.

There is a registry at the Docker Hub with images for SQL Server. You can see the Linux images for SQL Server here: https://hub.docker.com/_/microsoft-mssql-server. If you are running Windows containers, the hub registry URL is: https://hub.docker.com/r/microsoft/mssql-server-windows-developer/tags.

If you go to the hub, you will see a list of different images that you can download to your machine. For example, in the Linux SQL Server hub, there are tags for SQL 2019 and SQL 2017. Each of these can be pulled as a separate image. 

The "latest" tag is a convention for the latest stable build of the software in an image. Above we see that there is a latest for SQL Server 2017, as this is a released version of software as of this writing. SQL Server 2019 is in beta, so there is no latest here.

To get an image, we use the "docker pull" command. The format is to use docker pull with the image location URL, a colon, and then the tag. The page lists the URL, which is mcr.microsoft.com/mssql/server. In this case, I am pulling the 2019-CTP-2.2-Ubuntu tagged version.

Here's an example of my pulling a SQL Server 2019 CTP image down:

The image is built from a series of layers. Each of the lines with a hash value on the left represents a different layer. I have some of these layesr as I already had the CTP 2.1 image, and they likely share some layers. Each layer is downloaded separately.

Once the layers are downloaded, the "docker image ls" command will list the images. On my machine, I have a number of images already:

The image above shows that I have Microsoft SQL Server images, an image with the node.js software, and a redis image. Let's ignore the non SQL Server images and get our database server running.

Starting the Container

The image we show above is a template of sorts. It is really a read-only version of the file system and applications that we want to run in the container. When we start the container, we essentially create an instance of the image with a writeable section for anything the container needs. This is almost like a separate VM, but it's a much more lightweight process.

To start a container, we will use the docker container commands. For SQL Server, we need to provide some parameters that will allow us to work with the SQL Server instance software. I will show the command and then explain the various parameters.

docker container run --name SQLServer2017 -p 1433:1433 -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Demo1234$" -d microsoft/mssql-server-linux

From a command line, we use the "docker container run" to let the Docker daemon know what we want. The container is optional, but I like to use this to be clear I'm working with a container and not an image.

The "--name" parameter is used to give this particular container a name. This makes it easier to work with than the hash that is randomly assigned to the container. In this case, we'll call the container "SQLServer2017".

The -p parmeter is for port mapping. Many of us know that the default instance is listening on TCP 1433, so we map that from the host. Remember, the process inside the container has it's own view of the world. In this case we map the local 1433 to the container 1433. The format is host:container for ports. If you have a default instance of SQL Server running, then choose another host port, such as 51433 or 61433. In that case you'd use: "-p 51433:1433" for the parameter.

The -e parameter is used to set an environment variable. In this case, SQL Server in the container is looking for EULA acceptance with the ACCEPT_EULA environment varialbe. Setting this to Y will allow the SQL Server process to start. Microsoft requires this and if this is not set, the container will start and exit.

The second -E parameter is needed to set the sa password. By default this image has no user account and no logins other than sa, so you need a way to get started here. 

The next parameter, -d, is used to start this as a detached process. If you don't include this, the container will start and take over your shell, just as if you ran sqlsrvr.exe from the command line. If you do this, CTRL+C should exit you back to your command line.

The last parameter is the image name that is the template. In this case, I am using the microsoft/mssql-server-linux image with the latest tag. At the time I pulled this image, it was CU7. You can see the various tags available at https://hub.docker.com/r/microsoft/mssql-server.

When we run this command, we will see this result:

The container has started and it's hash starts with 9f1abe.

We can now interact with the SQL Server as we would with any other instance. I can do that by opening SSMS and connecting to the local IP on port 1433. This is the default, so my connection is:

I can also run a basic query for the version, server, and databases, which returns this:

Not terribly exciting, but this does get me started working with containers. I can do anything with this instance that I can do with other instances, including creating databases, adding tables and data, and more.

If I want to stop this container, I issue:

A connection to this instance right now would fail, just as if I stopped my default SQL Server service process. I can restart this with the "docker container start" command, and it will restart just as if I restarted a VM or host machine. There also is a "pause" and an "unpause" command that can be used as well.

If I am done with this container, and perhaps want to reset from the base image, I need to remove this container in order to reuse the name. To do that, use "docker container rm" with the name. I do need to stop the container first.

Troubleshooting

There are lots of things that could do wrong when you start working with containers. While I can't give you a complete list of possible issues and solutions, there are a couple things to do. First, read the error message carefully. Often the docker executable returns a straughtforward reason for things not working. I often mistype or send a parameter incorrectly, which is easy to fix.

There is also a "docker container logs" command that proves useful. The image has a process to start when it runs, which is specified when that image is created. This is a shell process and it records output from the process, again, just as if we started the process from the command line. For SQL Server, this is the sqlsrvr.exe process. If I run the command, I'll get normal output.

The two common mistakes I see are not including the EULA acceptance or choosing a poor password. For example, if I do not accept terms, I see this:

The container appeared to start, but a minute later when I queried for running containers, nothing is shown. I need to check the logs, as shown here:

That's easy to solve, put in the correct parameter. For a password that doesn't meet the policy, you'll see a message from the SQL Server error log that is similar. The container appears to start, and this time runs longer, but eventually stops.

Checking the logs shows me the error near the bottom.

If you run into other issues, post a note and myself or someone else will try to help.

Conclusion

This is a very short and sparse look at containers. There are many more commands and options, and much more to learn about images and how to work with containers. I've given you a short look at how to get started and get a container running on Windows 10.

If there is more you'd like to know, leave a note in the discussion. In future articles I'll try to explain a bit more about other options and how you might use containers in your daily work.

 
Total article views: 1504 | Views in the last 30 days: 1504
 
Related Articles
BLOG

SQL Server on Docker

Started working on Docker and contains Install Docker on the system – please make a note that for wi...

BLOG

Persisting data in docker containers – Part Two

Last week in Part One I went through how to mount directories from the host server into a docker con...

BLOG

Understanding Data Sharing in SQL Server Docker Containers

This article talks about shared volumes and the preferred mechanism for data persistence using the D...

BLOG

Introduction to Docker and Docker Toolbox

The article gives some knowledge about getting started and utilizing Docker containers. The focus is...

BLOG

Automatically restarting Docker containers

One of the problems that I’ve encountered since moving my Dev/QA departments to using SQL Server wit...

Tags
containers    
 
Contribute