Establish SQL Server in Docker container

eye-catchOther techs
Sponsored links

I tried to establish SQL Server in Docker because I wanted to learn SQL again.

Sponsored links

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:1433

Then, 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-2019

It 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: unknown

I 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)
Sponsored links

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/secrets

volumes section in this file mounts host directory to the container. Those 3 directories need to be mounted.

LocalContainer
/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).

Download SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS)
Download the latest version of SQL Server Management Studio (SSMS).

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.

ssms-login-screen

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.

create-database

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

Copied title and URL