I tried to establish SQL Server in Docker because I wanted to learn SQL again.
Create a Dockerfile and run
It’s possible to run SQL Server in Docker container without Dockerfile. Following command is used in Docker hub.
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -e 'MSSQL_PID=Express' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest-ubuntu However, it’s command is long to repeat it. I created a Dockerfile for it. You can find the original file here.
FROM mcr.microsoft.com/mssql/server:2019-CU11-ubuntu-20.04
ENV ACCEPT_EULA=Y \
SA_PASSWORD=passworD142! \
MSSQL_PID=Developer
EXPOSE 1433:1433Then, run it.
$cd src/Docker/images/sql-server
docker build -t sqlserver:2019-cu11 .
#1 [internal] load build definition from Dockerfile
...
$ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
sqlserver 2019-cu11 8581ad6577d3 4 weeks ago 1.5GB
$ docker run -d --name sqlserver-2019 sqlserver:2019-cu11
e336c2e8753205ad52f9a7ffbb23b5e7b69318c2b042460ecb07428e01641f3d
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
e336c2e87532 sqlserver:2019-cu11 "/opt/mssql/bin/perm窶ｦ" 7 seconds ago Up 6 seconds 1433/tcp, 1433/tcp sqlserver-2019It downloaded the image from Docker Hub and build was successful. Let’s connect to it.
$ docker exec -it sqlserver-2019 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P passworD142!
the input device is not a TTY. If you are using mintty, try prefixing the command with 'winpty'
$ winpty docker exec -it sqlserver-2019 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P passworD142!
OCI runtime exec failed: exec failed: container_linux.go:380: starting container process caused: exec: "C:/Program Files/Git/opt/mssql-tools/bin/sqlcmd":
stat C:/Program Files/Git/opt/mssql-tools/bin/sqlcmd: no such file or directory: unknownI use Git-bash and it’s necessary to add winpty when -it option is specified. The reason why C:/Program Files/Git was added is because my PC is windows. To solve this problem we need to add additional slash //opt/mssql-tools/bin/sqlcmd.
$ winpty docker exec -it sqlserver-2019 //opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P passworD142!
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'..I found a mistake in the Dockerfile. SA_PASSWORD was A_PASSWORD (the contents above was already fixed). I didn’t try but if the password doesn’t match the requirement I think the same message is shown. Following is the requirement.
A strong system administrator (SA) password: At least 8 characters including uppercase, lowercase letters, base-10 digits and/or non-alphanumeric symbols.
https://hub.docker.com/_/microsoft-mssql-server
I got another error messages after rebuilding it.
$ docker run -d --name sqlserver-2019 sqlserver:2019-cu11
e3b18f0944686da0ca2f291430a9d30b6b8fbc7e8cbcf451f4ef22c4d6a98d61
$ winpty docker exec -it sqlserver-2019 //opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P passworD142!
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'..
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..But it worked when I tried it again. I guess the server hadn’t started up yet. Let’s login and check the existing tables.
$ winpty docker exec -it sqlserver-2019 //opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P passworD142!
1> select name from sysobjects where xtype = 'U';
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
trace_xe_action_map
trace_xe_event_map
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
MSreplication_options
(7 rows affected)Use compose file to mount a host directory
The data that we create will be deleted without Docker volume when the container is deleted. Let’s mount a host directory to make the data persistent. It’s possible to set them all on a command line but I don’t like long command. Therefore, I created docker-compose.yml file. The Dockerfile created above is actually unnecessary if using this compose file. Original file exists here.
version: "3.7"
services:
sql-server:
image: mcr.microsoft.com/mssql/server:2019-CU11-ubuntu-20.04
container_name: sql-server-2019
environment:
- ACCEPT_EULA=Y
- SA_PASSWORD=passworD142!
- MSSQL_PID=Developer
ports:
- "1433:1433"
volumes:
- ./data/data:/var/opt/mssql/data
- ./data/log:/var/opt/mssql/log
- ./data/secrets:/var/opt/mssql/secretsvolumes section in this file mounts host directory to the container. Those 3 directories need to be mounted.
| Local | Container |
|---|---|
| /data/data | /var/opt/mssql/data |
| /data/log | /var/opt/mssql/log |
| /data/secrets | /var/opt/mssql/secrets |
Connect with SQL Server Management Studio
It’s hard to do everything on command line. I downloaded and installed SSMS (SQL Server Management Studio).
Connect to the SQL server in Docker container
Open Microsoft SQL Server Management Studio 18 and input connection data. If SQL server is running on your host machine you may be able to connect to the server running in Docker. Stop the SQL server running on the host machine if you cannot connect even after you change the exposed port.
I couldn’t connect at first because the specified port number in compose file was 1443 instead of 1433. Be careful…
Create a Dabase
I didn’t change anything. All values are default except for database name.
Play with SQL
I tried to create 3 tables.
CREATE TABLE Product (
id int NOT NULL PRIMARY KEY,
name varchar(255) NOT NULL UNIQUE,
price int,
);
CREATE TABLE Shop (
id int NOT NULL PRIMARY KEY,
name varchar(255) NOT NULL UNIQUE,
address varchar(255) NOT NULL,
telephoneNumber varchar(255),
);
CREATE TABLE Stock (
shopId int FOREIGN KEY REFERENCES Shop(id),
productId int FOREIGN KEY REFERENCES Product(id),
price int,
);
ALTER TABLE Stock ADD count int DEFAULT 0;Then, data insert.
INSERT INTO Product
VALUES
(1, 'pen-black', 5),
(2, 'pen-white', 5),
(3, 'Super eraser', 7),
(4, 'Secret Notebook', 10),
(5, 'Sketch Book', 8);
INSERT INTO Shop
VALUES
(1, 'shop1', 'street 1', '11111'),
(2, 'shop2', 'street 2', '22222'),
(3, 'shop3', 'street 3', '3333333');
INSERT INTO Stock
VALUES
(1, 2, null, 12),
(1, 3, 20, 11),
(1, 4, null, 5),
(2, 2, null, 9),
(2, 4, null, 24),
(2, 5, 9, 30),
(3, 1, 7, 100),
(3, 2, 8, 78);Check the result.
SELECT * FROM Product;
SELECT * FROM Shop;
SELECT * FROM Stock;
End
It was very easy to establish SQL server. We can establish it as many servers as we want if we change the exposed port number. Furthermore, we can easily delete the whole data and establish the clean environment for test.
Comments