Good news is that SQL Server 2017 and up are now cross-platform supported. This means that SQL Server can now run on Linux and therefore inside Docker too.
Pre-requisites
- Docker Engine 1.8+
- At least 2GB of RAM
- Bash Shell (or use Powershell)
Launch SQL Server inside Docker container
Yep that is true! just execute one command and you will have SQL Server up and running. Open Bash terminal and run the following command:
docker run --name sql-container -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=tiger@99" -p 8000:1433 -d mcr.microsoft.com/mssql/server:2017-CU8-ubuntuThis command will pull the image from Docker Hub and have the container running in a couple of minutes. Lets break the command and see what each parameter is doing.
docker run – Runs a container from the specified image. The image name is specified later in the command.
–name sql-container – Gives a name to the container. In this case the container will be named as sql-container.
-e – takes the environment arguments that are passed to the container when it is started. Here we specified two such arguments:
“ACCEPT_EULA=Y” – EULA stands for End User License Agreement. Basically here we are accepting the License terms for the use of SQL Server from Microsoft.
“SA_PASSWORD=tiger@99” – Admin password needs to be set for the default SQL user ‘sa’ when setting up SQL Server.
-p 8000:1433 – Specifies the ports to be used. The left side port is what is exposed by the container. This port is used to receive requests from outside the container. The right side port is what is used internally in the container to run the SQL Server instance. This means that inside the container SQL Server will be listening on port 1433.
-d mcr.microsoft.com/mssql/server:2017-CU8-ubuntu – Specifies the name of the name of the image to run. The -d specifies that the container will be run as detached from the bash terminal that we are using, so that once the container is running, we can use bash container for running other commands.
[amazon_auto_links id=”529″]After the command has finished, we can check to see if our container is running by using the following command
docker container lsYou should see the containers that are currently running as
$ docker container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
4058a4075f15 mcr.microsoft.com/mssql/server:2017-CU8-ubuntu "/opt/mssql/bin/sqls…" 3 minutes ago Up 3 minutes 1433/tcp, 0.0.0.0:8000->1433/tcp sql-container
Congratulations! your SQL Server is now running inside Docker container. Now what?
It is not justice if we did not insert at least one row into our newly installed database. Now one thing to keep in mind here is that since our SQL Server instance is running inside a container, we cannot directly execute commands on it through the bash terminal. So first we have to get inside the container. Docker provides a command exec command to connect to a running container, let us try
docker exec -it sql-container "bash"
Important, you might receive an error here depending on your bash installation configuration.
If you do just add winpty at the starting of the command above and retry.
If successful you should see the terminal displays that it is currently connected to the root user with the container id as
root@4058a4075f15:/#
Great! now we are inside the container. Now we can connect to our newly installed SQL Server. We will connect to the default sql user with the password that we set earlier
root@4058a4075f15:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P tiger@99
1>
Now let try to create a new database. Run these 3 command and hit enter after each line. The output should look like this
SELECT root@4058a4075f15:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P tiger@99
1> CREATE DATABASE TestDB
2> SELECT Name from sys.Databases
3> GO
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
TestDB
(5 rows affected)
1>
The logs in bash terminal shows that out new database TestDB has been create. Lets try too create a table ‘Inventory’ and then insert data into it.
root@4058a4075f15:/# USE TestDB
bash: USE: command not found
root@4058a4075f15:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P tiger@99
1> USE TestDB
2> CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
3> INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
4> GO
Changed database context to 'TestDB'.
(1 rows affected)
(1 rows affected)
1>
And lastly let us try to perform a select query and read the data. Enter the following SQL commands
1> SELECT * FROM Inventory WHERE quantity > 152;
2> GO
id name quantity
----------- -------------------------------------------------- -----------
2 orange 154
(1 rows affected)
1>
Congratulations! you just finished running SQL Server inside docker and performed sql queries on it. Let us finish on a positive quote.
“Do the thing you think you cannot do.”
Eleanor Roosevelt
