SQL Server Docker Container

This article is a basic introduction to SQL Server Docker Container. I will define and demo how to deploy SQL Server docker container locally and also provide link to configure it on windows server environments. If you are a SQL Server DBA then this post will help you understanding the requirements to setup and deploy SQL Server Container 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, creating a free Docker account if you don’t have one already. For more details, see the Docker documentation.
  2. Click here to access the list available SQL Server Docker images for Linux.
  3. Download VSCode for Text editor and to use Powershell 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 stop with the demo.

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

# For this demo I will use mcr.microsoft.com/mssql/server:2019-CU4-ubuntu-16.04 as Docker image
docker pull mcr.microsoft.com/mssql/server:2019-CU4-ubuntu-16.04

Step 2: is to run below command to create a container out the image you just pulled from Docker Hub
Notes: SQL Server Docker Container Requires the following environment flags
ACCEPT_EULA=Y
SA_PASSWORD=<your_strong_password>
MSSQL_PID=<your_product_id | edition_name> (default: Developer)

docker run --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 to get and copy the Docker Container ID or Name from SQL Server Docker Container we just deployed.

# Type below command to get all containers existing containers within your environment
docker ps -a

Past the Container Id or Name you just copied from above script to below script and let’ us connect to SQL Server Docker Container we just deployed.

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

Now, let’s query SQL Server Docker Container using T-SQL.

SELECT @@SERVERNAME,
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
    SERVERPROPERTY('MachineName'),
    SERVERPROPERTY('ServerName')
GO

Query SQL Server Docker Container using Powershell.

$SQLContainerPort = '<port#>'
$Password = '<Password>'
$SQLVersionQuery = @"
SELECT @@SERVERNAME,
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
    SERVERPROPERTY('MachineName'),
    SERVERPROPERTY('ServerName')
"@
Invoke-Sqlcmd -ServerInstance "localhost,$SQLContainerPort" `
-Database master `
-Query "$SQLVersionQuery" `
-Username 'SA' `
-Password "$Password"
%d bloggers like this: