Intro To SQL Server Containers

This article is a basic introduction to SQL Server Docker Container. I will define and demo how to deploy SQL Server docker containers locally and also provide link to configure it on windows server environments. The purpose of this article is to demonstrate how easy it is to start using SQL Server docker containers within your environments.

I will cover below points:

• Define Docker.
• How to setup and deploy SQL Server Docker Container.
• How to interact with SQL Server Docker Container.
• Basic common syntax.

What is Docker Container

Docker is a set of platform as a service products that uses OS-level virtualization to deliver software in packages called containers. Containers are isolated from one another and bundle their own software, libraries and configuration files; they can communicate with each other through well-defined channels.

How to deploy SQL Server Docker Container?
For this demo, we can use Windows 10 Professional and Enterprise editions however if you want to setup your Docker environment for Windows Server click here.

1. Download and install Docker Desktop. Create a free Docker account if you did not have one already. For more details, refer to Docker documentation.
2. Click here to access the list available SQL Server Docker images for Linux.
3. Download VSCode for Text editor, then use Powershell within VSCode Terminal to execute the scripts.
4. Deploying SQL Server Docker Container.

Now, assumed that you already downloaded  Docker Desktop and selected the version of the image that you wanted to deploy. Let’ us start with the demo.

Step 1: is to download the image from Docker Hub.

docker pull mcr.microsoft.com/mssql/server:2019-CU4-ubuntu-16.04

Step 2: is to run below command to create a container out of the image you just pulled from Docker Hub.
Notes: SQL Server Docker Container Requires the following environment flags. Also, remember to change the container name and password.
1) ACCEPT_EULA=Y
2) SA_PASSWORD=<your_strong_password>
3) port = <sql server port you want to expose>

docker run --rm --name <container_name> -d `
-e 'ACCEPT_EULA=Y' `
-e 'SA_PASSWORD=yourStrong(!)Password' `
-p 1430:1433 `
mcr.microsoft.com/mssql/server:2019-CU4-ubuntu-16.04

Run below script, from the output copy the Docker Container ID or Name of SQL Server Docker Container we just deployed.

docker ps -a

Past the Container Id or Name you just copied from above output script to below script, and use it to connect to SQL Server Docker Container we just deployed.
After you executed below script, you will see that every single fields returned SQL Server docker container id you just deployed.

docker exec -it demo /opt/mssql-tools/bin/sqlcmd `
   -S localhost -U SA -P "yourStrong(!)Password" `
   -Q "SELECT CAST(@@SERVERNAME AS VARCHAR(50)),
    CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(50)),
    CAST(SERVERPROPERTY('MachineName') AS VARCHAR(50)),
    CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50))"

Let’s create database Demo_db , and table try_sql_docker then insert data into the table then query it.

docker exec -it demo /opt/mssql-tools/bin/sqlcmd `
   -S localhost -U SA -P "yourStrong(!)Password" `
   -Q "SET NOCOUNT ON;
       CREATE DATABASE Demo_db;
       USE Demo_db;
       CREATE TABLE try_sql_docker(test VARCHAR(50));
       INSERT INTO try_docker
       SELECT 'It works!!!';
       SELECT * FROM try_docker
      "

Please use VSCode Terminal or any other kind of Terminal or command line to execute below in order for you to interact with SQL Server docker container using command line.

docker exec -it <container_id|container_name> /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <your_password>

Conclusion:
Containers enable you to deploy simultaneous running SQL Server environments, each provisioned in seconds. Front-end and back-end teams can replicate bugs quickly and validate tests by simply choosing the appropriate branch in the build script.