How to Deploy & Connect an SQL Server Docker Container

SQL Server has established itself as an easy-to-use, reliable, and efficient system. It provides a wide range of functionality as well. It works on SQL, and like any other DBMS, under a wide range of scenarios, the system doesn’t provide the level of efficiency you require.

Docker is a comprehensive suite of Platform as a Service (PaaS) products that leverage OS-level virtualization to provide software in packages also known as Containers. This blog talks in detail about how you can connect to SQL Server from Docker container for your business requirements seamlessly. It also talks about ways to check the Docker SQL Version, run and execute multiple SQL Server Docker Containers, and the process to upgrade a SQL Server Docker Container.

What is SQL Server?

SQL Server is a database system that stores structured, semi-structured, and unstructured data. It supports languages like Python and helps you extract data in different formats from different sources, sync it, and maintain consistency. The database provides controlled access to the data, making SQL Server more secure and ensuring regulatory compliance. See how SQL server migration can be done easily.

What is a Docker Image?

A Docker Image is described as a file that you can leverage to carry out the code within a Docker Container. This read-only template offers a convenient way to package up applications and preconfigured server environments. You can use them for your private use or share them publicly with other Docker users. Beginners can leverage Docker images to better understand the Docker platform.  Read about setting up MySQL server in Docker for development & deployment.

A Docker image consists of a collection of files that package together with all the essentials like dependencies, installations, and application code. You can generate a Docker image by using one of the following methods:

• Interactive: In this method, you can run a container from an existing Docker image, manually changing the container environment through a series of live steps and saving the resultant state as a new image.
• Dockerfile: By developing a plain-text file, also known as a Dockerfile, which provides the specifications for generating a Docker image.

How to Set up & Deploy SQL Server on Docker?

Here are the steps you can follow to set up and deploy a SQL Server Docker Container seamlessly:

• SQL Server Docker Setup: Install Docker on your System
• SQL Server Docker Setup: Execute and Run Docker
• SQL Server Docker Setup: Pull & Run the Docker Image for SQL Server
• SQL Server Docker Setup: Connect to your SQL Server Docker Instance

Install Docker on your System

• Step 1: Download Docker to your machine.

• Step 2: Next, download the DMG file on your Mac and install the package. Once it is installed, you can view the Docker icon being available on the status bar as given below. Click on the icon and select Preferences.

• Step 3: In the Preferences section choose Resources. Here, you need to increase the default allocated memory of the Docker Engine to 12 GB for the SQL Server to run smoothly.

• Step 4: Once you’ve allocated the memory as needed, you can go ahead and click on the Apply & Restart button. This restarts the Docker Engine with the new memory configurations.

Execute and Run Docker

• Step 1: Now that you’ve installed Docker, you can open up the terminal and supply the following command to check if Docker has been successfully installed on your device:

docker -v

Pull & Run the Docker Image for SQL Server

• Step 1: With Docker up and running, the next step is to pull the official SQL Server Docker image from Docker Hub and get down to brass tacks.
• Step 2: Next, you will be creating a directory for this exercise and opening the terminal in that directory. Once in the directory, run the command given below to pull the docker image from the repository to your local device:

docker pull mcr.microsoft.com/mssql/server

• Step 3: Once the SQL Server Docker Image has been pulled and extracted, you need to start the SQL Server Docker Container for this image. A Container in Docker is a running instance of the Docker image that can be started by performing the command as follows:

docker run -e ‘ACCEPT_EULA=Y’ -e ‘SA_PASSWORD=Str0ngPa$$w0rd’ -p 1433:1433 -d mcr.microsoft.com/mssql/server

• Step 4: As visible from the figure above, on running the command as mentioned in the previous step, an ID is returned. This is the unique ID of the SQL Server Docker Container that is currently running the SQL Server Instance. You can also verify the containers that currently running by leveraging the command as follows:

docker container ls

Connect to your SQL Server Docker Instance

• Step 1: Now that your SQL Server Docker Container is up and running, you can connect to the SQL Server instance through Azure Data Studio. Open up Azure Data Studio and use the credentials to access it:

• Step 2: As soon as it gets connected, you can look at the version information of the SQL Server instance as follows:

• Step 3: Now that you’ve connected to Azure Data Studio, you can run SQL commands with it. But first, you need to check if all the databases are present there by default. You can run the SQL command given below to check all the installed databases within a SQL Server instance:

SELECT * FROM sys.databases;

• Step 4: As evident from the figure above, you have all the four default databases installed and ready to use. On top of this, since the SQL Server instance is now running via an SQL Server in Docker Container, it might be pivotal to check the location of the data and the log files for administrative purposes. You can use the following command to list the path of the log files and the data for the databases that are installed in the instance:

SELECT [name], [physical_name] FROM sys.masterfiles;

• Step 5: As you can see from the above image, you have the entire path of the data and the log files, which can be useful for later administration. You can even create new databases here and use this as an equivalent SQL Server instance on Windows.

How to Check the SQL Server Version for your Docker Container?

If you wish to know the version of SQL Server within a running SQL Server Docker container, you can run the following command to showcase it.

For the following command, you can simply replace the <Container name or ID> with the name of the target container name or ID. You also need to replace <YourStrong!Password> with the SQL Server password for the SA login.

docker exec -it <Container ID or name> /opt/mssql-tools/bin/sqlcmd `
-S localhost -U SA -P "<YourStrong!Passw0rd>" `
-Q "SELECT @@VERSION"

You can even identify the SQL Server version and Build Number for a target SQL Server Docker Container image. The command mentioned below will show the SQL Server version and build information for the mcr.microsoft.com/mssql/server:2017-latest image.

It can execute this by running a new container with an environment variable PAL_PROGRAM_INFO=1. As a result, the container instantaneously exits and gets removed by the docker rm command.

docker run -e PAL_PROGRAM_INFO=1 --name sqlver `
-ti mcr.microsoft.com/mssql/server:2019-latest; `
docker rm sqlver

The aforementioned commands depict the version information similar to the following output:

sqlservr
  Version 15.0.4063.15
  Build ID 8a3bb4cca325e1d0b3071b3a193f6a1d74b440fbd95d2fb18881651a5b9ec8e8
  Build Type release
  Git Version 0335c462
  Built at Fri Aug 28 04:50:27 GMT 2020

PAL
  Build ID cc5ceea1b3d294f7d0166f99932f98c7eacfaaa81bcd7cf23c6a89f785829b63
  Build Type release
  Git Version ae9d66dff
  Built at Fri Aug 28 04:46:48 GMT 2020

Packages
  system.security                         6.2.9200.10,unset,
  system.certificates                     6.2.9200.10,unset,
  secforwarderxplat                       15.0.4063.15
  sqlservr                                15.0.4063.15
  system.common                           10.0.17134.1246.202005133
  system.netfx                            4.7.2.461814
  system                                  6.2.9200.10,unset,
  sqlagent                                15.0.4063.15

How to Run & Execute Multiple SQL Server Containers?

With Docker, you can also run multiple SQL Server Containers on the same host machine. You can use this approach for situations that need multiple instances of SQL Server on the same host. Every container needs to expose itself to a different port.

In the example below, you will be creating two SQL Server 2019 Containers and mapping them to ports 1402 and 1401 on the host machine:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" -p 1401:1433 -d mcr.microsoft.com/mssql/server:2019-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>" -p 1402:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Due to the aforementioned code snippet, there are two instances of SQL Server running in different containers. Clients can easily connect to each instance by leveraging the port number and the IP address of the Docker host container:

sqlcmd -S 10.3.2.4,1401 -U SA -P "<YourPassword>"
sqlcmd -S 10.3.2.4,1402 -U SA -P "<YourPassword>"

Ways to Run a Particular Container Image for SQL Server

Here are a couple of ways that you can use to run an MSSQL Docker Image:

• SQL Server Docker Container Image: Running Production Container Images
• SQL Server Docker Container Image: Running RHEL-based Container Images

Running Production Container Images

Here are a few considerations you need to keep in mind before you go about running Production Container Images:

• You can only leverage SQL Server in a production environment if you have a valid license. You can access SQL Server Standard and Enterprise Edition licenses through Microsoft Volume Licensing.
• You can configure the Developer Container image to run the production editions. You can use the following steps to run the production editions:

• Step 1: Review the requirements and run procedures within the quickstart. You also need to specify your production edition with the MSSQL_PID environment variable. Here’s the example that depicts how you can run the latest SQL Server 2017 Container image for the Enterprise Edition:

docker run --name sqlenterprise `
-e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<YourStrong!Passw0rd>" `
-e "MSSQL_PID=Enterprise" -p 1433:1433 `
-d "mcr.microsoft.com/mssql/server:2019-latest"

Running RHEL-based Container Images

The documentation for SQL Server Linux Container images points to Ubuntu-based Containers. Starting with SQL Server 2019, you can leverage the Containers based on Red Hat Enterprise Linux (RHEL). An example for the RHEL image will look something like mcr.microsoft.com/mssql/rhel/server:2019-CU1-rhel-8. For instance, the command mentioned below pulls the Cumulative Update 1 for SQL Server 2019 Container that leverages RHEL 8:

docker pull mcr.microsoft.com/mssql/rhel/server:2019-CU1-rhel-8

How to Upgrade SQL Server in Your Docker Container?

If you want to upgrade SQL Server within your Docker Container, you first need to identify the tag for the release for your upgrade. You can then pull this version from the registry with the docker pull command:

docker pull mcr.microsoft.com/mssql/server:<image_tag>

This updates the SQL Server image for any containers that you create subsequently, however, it does not update SQL Server in any running containers. To carry this out, you need to create a new container with the latest SQL Server Container image and move your data to that new container:

• Step 1: You need to make sure that you are using one of the Data Persistence techniques for your existing SQL Server Container. This allows you to start a new container with the same data.
• Step 2: Next, you need to stop the SQL Server Container with the docker stop command.
• Step 3: After that, you need to create a new docker run SQL Server container and mention either a data volume container or a mapped host directory. You also need to make sure that you use the specific tag for your SQL Server upgrade. The new container can now use a new version of SQL Server alongside your existing SQL Server data. 
• Step 4: Next, you need to verify your data and databases within the new container.
• Step 5: For the final SQL Server on Docker connection step, you can remove the old container by using docker rm. However, this step is optional. 

Conclusion

This blog talks about the different steps you can follow to set up a SQL Server Docker Container seamlessly. It also gives a brief introduction to SQL Server and Docker before diving into the setup process. Find out how to set up Snowflake with Docker for improved data orchestration and container-based solutions.

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications into your Data Warehouse or a tool like SQL Server to be visualized.

Want to take Hevo for a spin? SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

FAQs