Connect to SQL Server running in Docker

Streamline your development workflow by using Docker to stand up and run SQL Server instances quickly and without fuss.

What is Docker?

For an introduction to Docker, please take a look at my last article: Deploy a .NET Core API with Docker.

In short however, Docker allows you to “spin-up” instances of applications, (e.g. SQL Server, Redis, etc.), really quickly without having to go through laborious, (and often confusing), installations. So from a developer perspective – it’s awesome – you can concentrate on coding, and not get side-tracking on installing an instance of Oracle on Linux, (for example).

Why SQL Server?

I picked SQL Server as the target app for this article for 2 reasons:

1. I like and use SQL Server all the time
2. It’s traditionally a “Windows-only” system, so proving you can spin up an instance on any platform running Docker I thought would be cool.

Ingredients

All you need for this tutorial is either a Mac, or Windows / Linux PC running Docker*.

*  Windows and Mac users will download “Docker Desktop”, while Linux users will download and run “Docker Community Edition (CE)”. Both of which are free.

Note: Install instructions for Docker can be found where you get the software: https://docs.docker.com/install/

Images Vs Containers

Again, I’d refer you to my last article: Deploy a .NET Core API with Docker for a more detailed discussion on this topic, but basically:

• Images are application “blue-prints” stored on an image repository (which is basically a library)
• When you download and run an image from a repository, it runs as a “container”. The container is effectively an instance on the application, which you can use.

Analogy with Object Oriented Development

As a quick analogy, with OO Software development:

• Docker Image == Class
• Docker Container == Object Instance

Run SQL Server In Docker

Ok, so open a command prompt, and type:

docker --version

This is just a simple check to make sure Docker is installed, you should see something similar to the following:

Now, (assuming you’ve installed Docker), type the following at the command line, (don’t worry I’ll take you through everything below):

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Pa$$w0rd2019' -e 'MSSQL_PID=Express' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest-ubuntu

Hit <return/enter> and if this is the first time you have attempted to run SQL Server in Docker you’ll see something like:

We’ll dissect the command you’ve just issued in a minute, but just before we do that, the result of running the command was:

1. We didn’t have a local copy of the SQL Server image, so Docker goes out and starts to pull one down from the Docker Hub repository. (Note: if you issue the same command subsequently Docker will use the cached local version of the SQL Server Image – making it much quicker to run on future occasions).
2. After downloading the image, (again it will only do this if it doesn’t have a recent local copy), Docker will run our instance of SQL Server as a container, (as denoted by the return of a container GUID).

Taking a Step Back

Before we go onto connecting into the SQL Server instance, let’s go back to the command you issued and pull it apart:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Pa$$w0rd2019' -e 'MSSQL_PID=Express' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest-ubuntu

Let’s go through each section:

docker run

Fairly self explanatory. All Docker commands will start with “docker”, and in this instance we issue the subsequent “run” argument. Not surprisingly this attempts to run the specified “image” as a container. See a simpler example below:

docker run hello-world

Where “hello-world” is a very simple test image. You can see with our SQL server example, the command is much more complex, so let’s keep going through it.

-e ‘ACCEPT_EULA=Y’

The “-e” flag is essentially an “Environment Flag”, which allows us to, (again not surprisingly), configure the Container Environment. In this particular case, we are specifying that we accept the End User Licensing Agreement, (EULA), for SQL Server by passing in a ‘Y’ value.

-e ‘SA_PASSWORD=Pa$$w0rd2019’

Another Environment Flag, this time we are setting up the Server Administrator, (SA), account for SQL Server. The SA account is, (as the name suggests), the local SQL Server Admin account – so be careful!

GOTCHA! You’ll need to provide a SA password that adheres to the SA Password Policy, otherwise the SQL Server instance will fail to run, (you’ll get a GUID returned, as the Container does run briefly, but will subsequently stop if a weak password is provided!).

-e ‘MSSQL_PID=Express’

Our final Environment flag, this one specifies the “flavour” of SQL Server, in this instance we’re passing in ‘Express’ as we only require the free version. Other possible values are detailed here.

-p 1433:1433

This is our “Port Mapping” flag, it maps the Containers “internal” port to an externally facing port on our local machine. Without this, we could not connect into our SQL Server Container instance. Here we are mapping the internal port 1433 to an external port of 1433, (note we could choose any unused “external” port). For more info on this, refer to Deploy a .NET Core API with Docker.

-d mcr.microsoft.com/mssql/server:2017-latest-ubuntu

The ‘-d’ flag tells Docker to run our Container in “detached mode”, so it kind of runs in the “background”, therefore we’ll get a prompt back at our command line.

The last part of this command is just the name of the image we want followed by the version we want, (image name and version are separated by a colon ‘:’).

Note: You’ll see that we are using the Ubuntu Linux image of SQL Server, (there are of course Windows versions available). I choose this version as Linux and Mac PC’s running Docker can only use “Linux Containers”. Only Windows PC’s can run both Windows and Linux Containers, so this was the more ubiquitous choice.

Windows Users: When installing Docker, (and indeed after you’ve installed it), you can switch between Windows and Linux Containers, but you cannot use both at the same time. My advice? Use Linux containers as they are far more widely used.

Check Our Container is Running

To check that our Container is indeed running, type the following at a command prompt:

docker ps

You’ll see something like:

Where:

1. Is the ID that Docker gives our Container instance, (as opposed to the ID SQL Server issued us when we stood up our instance)
2. The name of the image
3. Every Image has a “default” command, it’s essentially the start up command for the Container
4. Our Port Mapping

Connect In

Now the cool part – how to connect! There are a number of ways to do this…

Attach to the Containers Command Line

With this method, we basically use our local command line to “attach” to the command line of our running container. For those of you who have connected to remote hosts using something like SSH, it’s similar to that – so basically a “remote” command line.

One of the key benefits of this approach is that you do not require any additional software installed on your local host machine.

To attach in this way, you’ll need the “Container ID” of our running Container – you can get this by issuing the docker ps command and finding the Container ID (see above).

Tip: You can also supply the longer GUID returned when the SQL Server Container instance starts.

Once you have the Container ID, issue the following command:

docker exec -it 0ac7fcd0bf0d /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'Pa$$w0rd2019'

Note: Because I used the dollar sign ‘$’ in our password, I needed to encase our password in single quotes, (otherwise you’ll get an error).

You should see something like this:

Let’s just quickly step through the components of the command we just issued:

docker exec -it <Container ID>

The exec command allows us to “run a command in a running container”, we can then supply a number of options to this, in our case we have supplied ‘-it’. This means we can run commands “interactively”, which would be required if we’re issuing SQL commands etc.

More information on this command can be found on the Docker website.

Finally we have supplied our Running Container ID.

/opt/mssql-tools/bin/sqlcmd

This is the command we are actually running as part of the docker exec command. In this case it’s ‘sqlcmd’ which is the interactive SQL Command Line for SQL Server.

Again the beauty of “attaching” like this, is that you don’t need this tool set installed on your local machine, (in the next section I take you through connecting to the container if you do have local SQL tools installed).

-S localhost -U sa -P ‘Pa$$w0rd2019’

This is probably pretty obvious, but for completeness:

• -S is the server
• -U is the User ID
• -P is the password

These are actually arguments required of ‘sqlcmd’,  they allow us to authenticate and use the sqlcmd tool.

Try it out

This article isn’t about the SQL Server command line, but just to show you it working, type:

select name from sys.sysdatabases;

Hit return, you should see a ‘2>’:

At the ‘2>’ type GO, then enter, you should see:

The ‘GO’ command executes the SQL you’ve entered, in this case we’re just listing the system databases we have.

If you’d like more examples of how to use the SQL Command Line, check out my free book: The Complete ASP .NET Core API Tutorial.

To exit the command line, type ‘quit’, (no need to type ‘GO’), this will pull you out of the attached session.

Connecting from External End Points

The above method was great if you don’t want, or don’t have any “external tools” installed. But what about if you want to connect to the running instance if you do?

To be honest, this is really easy, and you just treat the running Docker Container as is it were any other instance of SQL server.

SQL Server Management Studio

For Windows users running SQL Server Management Studio on the same machine as Docker, you simply connect to the local host as follows:

So you supply:

• Server Name: 127.0.0.1,1443
• Authentication: SQL Server Authentication
• Login: SA
• Password: whatever password you passed in as an Environment flag.

Just note the comma, ‘,’ in between the server address, (127.0.0.1) and the exposed port (1433).

You can of course use the IP address or host name of the machine hosting Docker if you’re connecting in from another machine – easy!

Conclusion

As a developer, I don’t want to waste time standing up the infrastructure to support my coding endeavours – I just want to code! So for me Docker is an excellent tool I use to streamline my development workflow.