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.
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
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
[email protected]:/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"
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'
'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:
[email protected]:/home/deploy# docker exec -it sql2017 "bash" [email protected]:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'PutYourSuperSecurePasswordHere' 1>
3. Execute a SQL query
SELECT Name from sys.Databases and hit enter.
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>
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.
To connect to the SQL Server Docker image from windows
1. Launch Command Prompt
You should see a list of network addresses. Look out for
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
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:
Using Azure Studio
Using raw command line:
sqlcmd -S 10.0.75.1,8383 -U SA -P "PutYourSuperSecurePasswordHere"
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.