Introduction
On the 2nd of October Microsoft SQL Server 2017 became available for purchasing and downloading. For the first time, it is possible to run SQL Server on Linux, Docker Enterprise Edition containers (and on the CE Edition of Docker, although this is not supported by Microsoft) and Windows Server. Why would you run SQL in a container? The Docker container of SQL Server has some very interesting use cases in particular, like:
- Quickly create and start a set of SQL Server instances for development or testing.
- Maximize density in test or production environments, especially in microservice architectures.
- Isolate and control applications in a multi-tenant infrastructure.
This blog explains how to run and use SQL Server on Docker. Since the developer edition is free for non-production use, you can try out this new feature at no cost in your development environment.
Prerequisites
The following prerequisites must be met before you can install SQL Server on Docker:
- Docker Engine 1.8+ on any supported Linux or Windows distribution
- Minimum of 4 GB of disk space
- Minimum of 4 GB of RAM
Run SQL Server in Docker
To run a SQL Server in Docker, enter the following commands in the Docker host (depending on your system and user configuration, you might need to preface each docker command with sudo):
docker pull microsoft/mssql-server-linux:2017-latest docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<sa password>' -e 'MSSQL_PID=Developer' -p 1433:1433 -v mssqldata:/var/opt/mssql --name sql1 -d microsoft/mssql-server-linux:2017-latest
The following table provides a description of the parameters in the previous docker run command:
| Parameter | Description |
| -e ‘ACCEPT_EULA=Y’ | Set the ACCEPT_EULA variable to ‘Y’ to confirm your acceptance of the End-User Licensing Agreement. |
| -e ‘MSSQL_SA_PASSWORD=<sa password>’ | Specify the strong password that is at least 8 characters and that meets the SQL Server’s password requirements. |
| -e ‘MSSQL_PID=Developer’ | Specify the edition or product key. |
| -p 1433:1433 | Map a TCP port on the host environment (first value) with a TCP port in the container (second value). |
| -v mssqldata:/var/opt/mssql | Map the data folder on the container to a volume with the name mssqldata on the Docker host (optional; if you don’t use this option, all data will be lost if you delete the container) |
| –name sql1 | Specify a custom name for the container rather than a randomly generated one. |
| microsoft/mssql-server-linux:2017-latest | The SQL Server 2017 Linux container image. In case you use Windows Docker, use the Windows container image. |
If you want to run more than one SQL Server container on the same Docker host, make sure that the TCP port on the host environment (-p <TCP port host environment:1433), the volume name (-v <volume name>:/var/opt/mssql) and the name (–name) are unique.
Use of Microsoft SQL 2017 on Docker
You can use the Microsoft SQL 2017 server in several different ways. Below an overview is given of the four most common ways to do so.
Firstly, you can use the SQL Server command-line tool, sqlcmd, inside the container to connect to SQL Server as followed:
Use the command below to start an interactive bash shell inside your running container:
docker exec -it sql1 "bash"
Connect locally with sqlcmd:
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<sa password>'
If the connection is successful, you should get the sqlcmd command prompt 1> and you can start to enter transact-SQL commands (for example to create a database: CREATE DATABASE TEST)
Secondly, you can also use sqlcmd on a computer where you installed the sqlcmd tool to connect to SQL Server running in the container. First, find the IP address of your Docker host. Then connect to the SQL server:
sqlcmd –S <ip address Docker host>,<port> -u sa –p '<sa password>'
The third method is using SQL Server Management Studio (SSMS) on Windows. You can connect to the SQL Server running in the container in the same way as a non-container SQL installation.
Lastly, you can also use a .NET application with a connection string to connect to SQL Server running in the container.
Conclusion
The conclusion is simple: you can use a SQL Server running in the container in exactly the same way as a non-container SQL installation! Happy containering!