In this guide, you’ll learn:
- What Docker is (briefly)
- Why you might want to use it for your database
- How to set it up
- How to get an SQL Server database running using docker – with screenshots and explanations
Let’s get into it.
What is Docker and Why Use it for a Database?
Docker is a tool that allows you to run applications in their own areas of an operating system (inside a “container”).
Using Docker is common for application development teams to run the applications in, from what I’ve seen. But running databases inside them is not as common.
It’s a similar concept to a virtual machine. For our purposes, for running the database, we just need to know how to set up the database inside the Docker container and how to connect to it from outside the Docker container.
Why Use Docker for a Database?
Why would we use Docker for our database? There are several reasons.
- Fast setup. It’s pretty fast to set up a new database using this Docker process, compared to installing a database on your operating system.
- Different versions. It’s easier to have different versions of the same database running in different containers (e.g. SQL Server 2017 and 2019) if you need to test different features.
- Runs on more operating systems. SQL Server is not available for Mac, If you have a Mac, then using Docker is a great way to set up SQL Server.
There are probably many more advantages related to deployments and automation which I won’t cover here.
The other thing you should know is that the data in the SQL Server database is not persisted. Unlike a Virtual Machine, containers can be stopped and started without the data being saved. So, be aware of this when working with your database.
It’s possible to persist data, and it’s possible that certain settings or commands here can show the data is persisted and I may be wrong, but it’s something to keep in mind.
Let’s get started with setting up the database.
At a high level, here are the steps we’ll take in this guide:
- Download the Docker application.
- Set up an account on Docker Hub
- Run Docker and download the SQL Server image
- Run the SQL Server image
- Connect to the database and run some SQL
The screenshots in this guide are taken on a Mac, but the same process should be possible on Windows too.
If you’d like to watch a video of this process, check it out here:
To get started, we need to download the Docker application. This will allow us to run the container which contains the database.
Step 1: Visit the Docker website at www.docker.com.
Step 2: Click on Get Started.
Step 3: Select the right version for your computer. This would be either of the two Mac versions or a Windows version.
The Docker setup file will then begin downloading to your computer. It’s about 580 MB so it may take a few minutes to download.
Step 4: Install the Docker application once it has downloaded. This can be done by opening the file on macOS and dragging it to the Applications folder, or following the installation steps in Windows.
It takes a couple of minutes to install.
Step 5: Run Docker. This will cause the Docker service to start.
You can run this from the Applications folder, or from the Spotlight Search, or from the terminal.
Now we’ve got Docker installed and runnings, it’s time to get our database files.
Set Up Account on Docker Hub
The files that Docker uses to run applications are called images. An image is a pre-built collection of files.
These images are available to download on a website called Docker Hub. There is a whole range of images there for different technologies. The one we’re looking for is for the SQL Server database.
We’ll need to visit the Docker Hub website, set up an account, find the image, and download it.
Step 1: Visit the Docker Hub website at hub.docker.com.
Step 2: Click on the Sign Up button. You’ll need an account to be able to download the images, but it’s free and easy to set up.
Step 3: Enter your details on the Sign Up page.
Remember your password here as you’ll need it later.
Step 4: On the Choose Your Plan page, select Free. This is enough for our purposes.
Step 5: Verify your email address by clicking the link in the email you receive.
You should end up at the Docker Hub homepage and should be logged in.
Find SQL Server Image
It’s time to find the image for the SQL Server database. We’re going to get the URL for the image, and use this URL as part of a command we’ll run on the command line.
Step 1: Enter the term “SQL Server” into the search bar at the top of the page and press Enter.
Step 2: Click on the Microsoft SQL Server entry in the search results.
This page shows the details of the SQL Server database image.
At the moment, both 2017 and 2019 versions are available. In this tutorial, we’ll use SQL Server 2017 but you can just as easily use SQL Server 2019.
Step 3: Copy the command from the example.
If you scroll down a little, you can see a few sample commands under the “How to use this image” section. Copy the command from the first example here, which is the CU8 version of SQL Server 2017.
There are a few things to note in this command.
- The “-e” stands for environment flags, and there are a couple of those on this command. The first one allows Docker to accept the EULA or End User License Agreement. The second one will set the password for the built-in “SA” account. You can change this password before you run it, which we’ll see shortly.
- The “-p” indicates the port numbers. This means that port 1433 is used both inside the container and outside the container for us to connect to the database.
- The “-d” means detached mode, which means the container runs in the background and we can use our terminal for other commands.
- The final part is the path to the image on docker hub, which will be downloaded.
Download SQL Server Image
We have an account on Docker Hub and have found the SQL Server Database image to download.
The next steps are to download the image. We’ll do this on the command line.
Step 1: Open the Terminal (MacOS) or Command Line (Windows).
Step 2: Enter “docker login” at the command line and press Enter.
MacBook-Pro:~ BB$ docker login Authenticating with existing credentials Login Succeeded
We need to login to Docker before downloading the image, and this is the command to do it.
Step 3: Enter your username and password, which is what you entered when you set up your Docker Hub account.
Step 4: Enter the docker run command from the example.
Now we have logged in, we can download the image. This can be done using the “docker pull” command, which is the command in the black box we saw on the Docker Hub page.
However, we can also use the “docker run” command, which will run the container if it exists on your computer already, and download it if it does not exist.
Here’s the command to run:
Before you press enter, you may want to change the password from “yourStrong(!)Password” to something else. It’s up to you. The password mentioned here will work.
You should also add a name for the container. This makes it easier to refer to in the future. To do this, add this to the end of the command:—name sqlserver. That’s two dashes, then name, then space, then the name you want to give it such as sqlserver.
So the final command is:
Step 5: Run this command.
MacBook-Pro:~ BB$ docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d --name sqlserver mcr.microsoft.com/mssql/server:2017-CU8-ubuntu
The image will then start downloading. It’s a few gigabytes so it may take some time.
If you get an error about the name already being used, then it’s likely because you have run this command in the past and the container name of “sqlserver” is already created. If so, you can start the existing container. To do this, type “docker start sqlserver”.
After the image is downloaded, it’s time to run it. Because you ran the “docker run” command earlier, this will run the image after it’s downloaded and the container will be created.
Step 6: Run docker ps to check the status.
We can check the status of the container by running the “docker ps” command.
MacBook-Pro:~ BB$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES af8a9a521797 mcr.microsoft.com/mssql/server:2017-CU8-ubuntu "/opt/mssql/bin..." 1 days ago Up 4 seconds 0.0.0.0:1433->1433/tcp, 5500/tcp sqlserver
This shows a list of all containers that are running.
In the Status column, you’ll see something like “starting” that indicates the container is starting up.
Run the command in a minute or two and you should see a status of “Up X seconds”. This means the container is running.
We now have a Docker container running with our SQL Server database!
Connect to Database and Run SQL
Now the container is running, we can connect to it.
Many tutorials online specify you can login to the container itself using the command line. But we want to use an IDE on our computer to connect, as it’s easier to do and easier to work with.
We’ll use Azure Data Studio to connect, which is Microsoft’s freely available IDE that works on Windows and MacOS. You can use whatever IDE you want. I’m not using SQL Server Management Studio in this demo, as it doesn’t run on a Mac, but the process should work with Management Studio. But you can use any IDE you like that can connect to SQL Server (see the full list here).
Step 1: Open your IDE, such as Azure Data Studio.
Step 2: Click Create New Connection.
You’ll see this tab here.
The connection details to use are available on the image page on Docker Hub, and are also in the command we ran. We’ll enter them here.
- Host: localhost, 1433
- Username: sa
- Password: your password from when you ran the command
Check the Remember Password box if you want the password to be stored with the connection (and not have to enter it each time).
Leave everything else as the default.
Step 3: Click Connect.
If everything has been set up correctly, the connection should be successful. If there are any errors, take a look at the description and comments where I’ll list a few ways to resolve them.
You should see a screen that looks like this.
In other IDEs, you may get taken directly to an SQL editor screen.
Step 4: Click New Query.
In Azure Data Studio, click on the New Query button. A new SQL tab will be displayed.
Step 5: Enter and run an SQL query.
Enter a simple query, such as:
This should show the version of SQL Server you’re running.
You’ll see the output on the screen. This is the result of the query on the SQL Server Database inside the container. It shows SQL Server 2017 which is the version we chose on Docker Hub.
Congratulations! You’ve successfully set up an SQL Server database inside a Docker container and connected to it!
If you have any errors, let me know in the comments below.
Stopping the Database
If you want to stop the database, you can use the docker stop command.
Step 1: Return to the terminal.
Step 2: Run the docker stop command.
This command will stop the container called sqlserver.
To start it again, use the docker run command we saw earlier.
Using Docker for an SQL Server database is useful for many reasons, especially if you use a Mac and want to get started with SQL Server. It’s fairly simple to set up and can be started and stopped pretty easily.