As I’m learning more about Docker, the more I’m starting to love the versility of it. In this post, I will walk through how to launch SQL Server 2017 on a Linux machine using Docker where I’ll be hosting it on DigitalOcean.

It’s worth mentioning that Sql Server 2017 does have some pre-requisites:

  • Minimum of 2 GB of disk space.
  • Minimum of 2 GB of RAM.

Now, with this mind, the cheapest DigitalOcean droplet that has 2GB of RAM comes in at $10 a month, so thats what I will be using.

If you have not come across DigitalOcean, its a great hosting alternative and much cheaper than Azure, AWS and Googe, especially when you’re starting out.

Connect to a Linux or Windows machine

Linux or Windows, either will work, so long as Docker is installed. Just make sure that on Windows, Docker is using Linux containers. This can be switched by right-cliking the Docker Desktop task tray and selecting Switch to Linux Containers…

If you’re working side projects like myself and need to host a SQL Server instance on the cheap, feel free to try DigialOcean and checkout my earlier post on how to setup a linux machine with docker on DigitalOcean

Create a SQL Server Docker Compose file

To start things off, we need to create a docker compose file.

1. Create a docker compose file Create a docker compose file such as docker-compose-sql.yml in your editor of choice. As I’m using Linux, I’ll be using nano.

nano /home/deploy/docker-compose-sql.yml

2. Specify the SQL Docker image and a volume Create a docker compose file as shown below:

version: '3'

services:

  sqlserver2017:
    image: mcr.microsoft.com/mssql/server:2017-latest
    container_name: sql2017
    ports:
        - "8383:1433"
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=PutYourSuperSecurePasswordHere
      - MSSQL_PID=Developer
    volumes:
      - sqlserver-data:/var/opt/mssql
    logging:
      options:
        max-size: "10m"
        max-file: "10" 
        
volumes:
  sqlserver-data:

Be sure to change the password PutYourSuperSecurePasswordHere for a strong password with at least 8 characters, as SQL server has some specific password policies in place. If you’re struggling with a generating a password, you can use 1Password’s password generator or Lastpass’s password generator.

Notice that I have specified a volume called sqlserver-data. This is mapped to the folder /var/opt/mssql within the SQL docker image. This means that these files are mapped to the host so that if the docker container is restarted, stopped or the machine is rebooted, when it is resumed, the database and data are not lost.

I have also changed the host port to 8383, rather than keeping the default 1433 to make things more explicit and also not to interfere with any other SQL server instances that may have that default port.

To save the changes to the file, hit Ctrl+X and Y when prompted to save changes.

3. Start the Docker container

docker-compose -f /home/deploy/docker-compose.sql.yml up -d

This may take a few minutes depending on the internet connection. Also, it has to pull the Sql Server 2017 image from Docker Hub which is about 1.5GB.

4. Confirm its running Once complete, you can confirm that the container is running by running docker ps

root@sql01:/home/deploy# docker ps
CONTAINER ID        IMAGE                                        COMMAND                  CREATED             STATUS              PORTS                    NAMES
df2561f10b5c        mcr.microsoft.com/mssql/server:2017-latest   "/opt/mssql/bin/sqls…"   15 minutes ago      Up 2 seconds        0.0.0.0:1433->1433/tcp   sql2017

N.B If there are any issues, you can run docker logs sql2017 to diagnose any issues.

Confirm SQL Server is running

To verify SQL Server is actually running we can connect to the docker container directly. 1. Run Docker interactive mode

docker exec -it sql2017 "bash"

Where sql2017 is the name of the container.

2. Connect to SQL Server Once connected, we can run the sqlcmd tool to connect to the instance.

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'PutYourSuperSecurePasswordHere'

N.B 'PutYourSuperSecurePasswordHere' is the password that was set in the docker compose file earlier. You should see a 1> prompt once succesfully connected. Here’s the full output from the above steps:

root@sql01:/home/deploy# docker exec -it sql2017 "bash"
root@df2561f10b5c:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'PutYourSuperSecurePasswordHere'
1>

3. Execute a SQL query Now type SELECT Name from sys.Databases and hit enter. Now type GO and hit enter. You should now get something like this:

1> SELECT Name from sys.Databases
2> go
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb

(4 rows affected)
1>

4. Exit Now type exit to exit sqlcmd and exit again to exit bash. Great, now we have a fully operational SQL Server instance in docker!

Connect to SQL Server from outside of the container

Now that SQL server is up and running, it would be useful if we can actually connect to it from outside of the Docker container. Since I’m using DigitalOcean, it will just simply be the public ip address that was assigned to the droplet and the port that was specified in the docker compose file. I can either connect using SQL Server Management Studio, or Azure Data Studio which is cross platform.

If, however, you are running this locally on either Windows or Mac, we need to obtain the ip address. Generally, localhost will work fine, however when I was testing on windows, I had to run ipconfig on windows.

Windows

To connect to the SQL Server Docker image from windows

1. Launch Command Prompt 2. Run ipconfig You should see a list of network addresses. Look out for DockerNat

Ethernet adapter vEthernet (DockerNAT):

   Connection-specific DNS Suffix  . :
   IPv4 Address. . . . . . . . . . . : 10.0.75.1
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . :

The ip address listed in this example is 10.0.75.1. So in order to connect, the server will be 10.0.75.1:8383 where 8383 is the port. As mentioned before this is the port I am using for this demonstration and used in the docker compose file earlier. The username will be sa and the password will be what was defined in the docker compose file.

Using Sql Management Studio: docker-sql-management-studio-connect-demo

Using Azure Studio docker-azure-studio-connect-demo

Using raw command line:

sqlcmd -S 10.0.75.1,8383 -U SA -P "PutYourSuperSecurePasswordHere"

Summary

Im this post I demonstrated how to setup and run SQL Server on Linux with Docker which can be hosted for a very reasonable price with just a minimal Docker compose file. This is great for side projects if you’re on a budget, where you can run multiple databases for just $10 a month.

Also, its great for local, dev or test environments where you can spin up a SQL server relatively quickly and perhaps run some integration tests.

References: Quickstart: Run SQL Server container images with Docker