Detailed steps for deploying Microsoft Sql Server with Docker

1 Background

Starting with SQL Server 2019 CU3, Ubuntu 18.04 is supported.

Starting with SQL Server 2019 CU10, Ubuntu 20.04 is supported.

Docker Engine 1.8+

At least 2 GB of disk space At least 2 GB of RAM

Blog host machine:

Welcome to Ubuntu 20.04.3 LTS (GNU/Linux 5.11.0-37-generic x86_64)

2 Create a container

Pull the image

docker pull mcr.microsoft.com/mssql/server:2019-latest

View Mirror

docker images 
root@dev-virtual-machine:/home/dev# docker images 
REPOSITORY TAG IMAGE ID CREATED SIZE
mcr.microsoft.com/mssql/server 2019-latest 6db3c5ebc331 3 weeks ago 1.55GB

Running the container

docker run 
-e "ACCEPT_EULA=Y" 
-e "SA_PASSWORD=Pass@w0rd"    
-p 51433:1433 
--name mssql 
-h mssql    
-d mcr.microsoft.com/mssql/server:2019-latest 

View Container

root@dev-virtual-machine:/home/dev# docker ps 
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
6498220c95f6 mcr.microsoft.com/mssql/server:2019-latest "/opt/mssql/bin/perm…" 24 minutes ago Up 23 minutes 0.0.0.0:51433->1433/tcp, :::51433->1433/tcp mssql

Setting -h and --name to the same value is a good way to easily identify the target container.

3 Change SA password

Microsoft officially recommends changing the SA password

The SA account is a system administrator that is created on the SQL Server instance during installation. After you create the SQL Server container, you can discover the specified SA_PASSWORD environment variable by running echo $SA_PASSWORD in the container.

Choose a strong password to use for the SA user.

Use docker exec to run sqlcmd to change the password using Transact-SQL. The following example will read the old and new passwords from user input.

sudo docker exec -it mssql /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA \
 -P "$(read -sp "Enter current SA password: "; echo "${REPLY}")" \
 -Q "ALTER LOGIN SA WITH PASSWORD=\"$(read -sp "Enter new SA password: "; echo "${REPLY}")\""

4 Linking mssql

The following steps use the SQL Server command line tool sqlcmd to connect to SQL Server from within the container.

Use the docker exec -it command to start an interactive Bash shell inside a running container. In the following example, mssql is the name specified by the --name parameter when creating the container.

sudo docker exec -it mssql "bash"

Use sqlcmd inside the container to connect locally. By default, sqlcmd is not in your path, so the full path needs to be specified.

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "Passw0rd"

If successful, the sqlcmd command prompt should appear: 1>

5. Link mssql outside the container

1. Find the IP address of the computer hosting the container. On Linux, use ifconfig or ip addr. On Windows, use ipconfig.

2. For this example, install the sqlcmd tool on the client computer

3. Run sqlcmd, specifying the IP address and the port that maps to port 1433 in the container. In this case, that's port 51433 on the host, so use it here. You will also need to open the appropriate inbound ports on your firewall to allow the connection.

4. Run Transact-SQL commands. When you are finished, type QUIT.

Navicat Link Notes

When using Navicat to connect to the Sqlserver database, if the port number is not the default port number, you need to use a comma plus the port number after the host IP.

For Navicat, after the above settings, you need to install the SQL Server Native Client 10.0 driver. The installation program is sqlncli_x64.msi in the root directory of the Navicat software. The 32-bit version is also available. After the installation is complete, the Native Client driver in the Advanced tab will have a drop-down option to choose from when creating a Sqlserver connection.

Summarize

This is the end of this article about the detailed steps of deploying Microsoft Sql Server with Docker. For more information about deploying Microsoft Sql Server with Docker, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!