Skip to content

Setting up SQL Server database in a Docker Container

Introduction

Running SQL Server in a Docker container is best suited for development, testing, learning, or quick experimentation scenarios where you need a lightweight, isolated environment that can be spun up or torn down easily. It works well offline, and your can run it without any cost, and is perfect for local development workflows. However, it's not ideal for production use. for production environments you can use Azure SQL Database which is a fully managed platform-as-a-service (PaaS) offering that's optimized for production workloads. It provides built-in high availability, automated backups, scalability, and security, making it ideal for applications that need global reach, minimal maintenance, and enterprise-grade reliability.

In this lab, I will guide you through the process of creating Docker container for SQL Server database and run SQL Server database in the docker, and finally accessing the SQL Server database using SQL Server Management Studio (SSMS) and Azure Data Studio tools.

Objective

The objective is to establish a local development environment for the SQL Server database. To accomplish this, you will create a Dockerfile file, run them locally. All of these tasks we are doing here will be useful in later chapters when deploying to the Azure Kubernetes Service (AKS).

In this exercise, our objective is to accomplish and learn the following tasks:

  • Step-1: Setup Git Repository for SQL Server database.
  • Step-2: Create Folder Structure for SQL Server database.
  • Step-3: Add Dockerfiles to the Database Project
  • Step-3.1: Docker Build Locally
  • Step-3.2: Docker Run Locally
  • Step-4: Test the SQL Server database connection using SSMS
  • Step-5: Test the SQL Server database connection using Azure Data Studio
  • Step-6: Push Docker Container to ACR

By the end of this lab, you will have a SQL Server database running in a Docker container, managed through Azure DevOps, and ready for use in your development and production environments.

Prerequisites

Before starting this lab, ensure you have the following prerequisites in place:

  • Docker Desktop: - Docker Downloads.
  • Docker compose installed
  • SQL Server Management Studio installed - this will allow you to manage the SQL Server databases
  • Azure Data Studio installed - this will allow you to connect to SQL server databases
  • Basic understanding of Docker and SQL Server.
  • Access to an Azure Container Registry (ACR).

Verify the docker installation by running following commands:

docker version
# or
docker --version
# or
docker -v

Verify the docker compose by running following commands:

docker-compose version

Step-1: Setup Git Repository for SQL Server database

Setting up a Git repository for your SQL Server database project allows you to manage your code effectively, work in teams, and track the changes of your database codebase.

  • Create a new project in Azure DevOps for your database-related work.
  • Create a repository within the project to store your database scripts and Dockerfiles.

For example to clone an existing repository, run the following command:

git clone https://keesari.visualstudio.com/Microservices/_git/microservices

Step-2: Create Folder Structure for SQL Server database

In this step, we'll create a dedicated project or folder for our SQL Server database

Create a new database project:

Inside our Git repository, create a new directory or folder specifically for your SQL Server database. This folder will contain all the necessary files for SQL Server database, including databaseschema scripts, sample data scripts, docker compose & Dockerfile and other sql files.

Here's a suggestion for a folder structure for a SQL Server database project:

your-project-name/
│
├── sql/
│   ├── scripts/
│   │   ├── schema/
│   │   │   ├── tables/
│   │   │   │   ├── table1.sql
│   │   │   │   ├── table2.sql
│   │   │   │   └── ...
│   │   │   ├── views/
│   │   │   │   ├── view1.sql
│   │   │   │   ├── view2.sql
│   │   │   │   └── ...
│   │   │   ├── functions/
│   │   │   │   ├── function1.sql
│   │   │   │   ├── function2.sql
│   │   │   │   └── ...
│   │   │   └── procedures/
│   │   │       ├── procedure1.sql
│   │   │       ├── procedure2.sql
│   │   │       └── ...
│   │   └── data/
│   │       ├── seed_data.sql
│   │       └── ...
│   └── migrations/
│       ├── version1/
│       │   ├── up.sql
│       │   └── down.sql
│       ├── version2/
│       │   ├── up.sql
│       │   └── down.sql
│       └── ...
│
├── Dockerfile
└── README.md

Explanation:

  • sql/: This folder contains all SQL-related files for your project.
  • scripts/: Contains scripts for creating database objects like tables, views, functions, and stored procedures.
    • schema/: Contains subfolders for different types of database objects.
    • tables/, views/, functions/, procedures/: Each of these folders contains SQL scripts for the respective database objects.
  • data/: Contains data scripts such as seed data.
  • migrations/: Contains SQL migration scripts for managing database schema changes over time. Each migration version should have an up.sql script for applying the migration and a down.sql script for reverting it.
  • Dockerfile: The Dockerfile for building a Docker image for your SQL Server database.
  • README.md: Documentation for your project.

You can adjust this structure based on the specific needs of your project. For instance, if you have additional folders or files, you can add them accordingly.

Alt text

Step-3: Add Dockerfiles to the Database Project

To build a Docker image for SQL Server, create a Dockerfile in your project's root directory:

# Use the official SQL Server 2019 image from Microsoft
FROM mcr.microsoft.com/mssql/server:2019-latest

# Set the environment variables for SQL Server
ENV ACCEPT_EULA=Y
ENV SA_PASSWORD=Strong@Passw0rd
# ENV MSSQL_PID=Developer
# ENV MSSQL_TCP_PORT=1433

# Create a directory inside the container to copy your SQL scripts
WORKDIR /src

# Copy your SQL scripts into the container [optional]
COPY scripts.sql ./scripts.sql

# Set permissions for the SQL scripts
# RUN chmod +x ./scripts.sql

# RUN SQL SERVER and Access SQL CLI on localhost with given credentials
# Then run SQL Script - scripts.sql
RUN (/opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" &&  /opt/mssql-tools/bin/sqlcmd -S127.0.0.1 -Usa -PStrong@Passw0rd -i scripts.sql

In this Dockerfile:

  • We start with the official SQL Server 2019 image provided by Microsoft.
  • Set environment variables ACCEPT_EULA to 'Y' and SA_PASSWORD to the desired strong password for the 'sa' account.
  • Create a directory inside the container to copy your SQL scripts (/src in this case).
  • Copy your SQL scripts into the container (assuming you have them in the same directory as your Dockerfile).
  • Set permissions for the SQL scripts (if needed).
  • Finally, specify the command to start SQL Server when the container starts.

You would replace "./scripts.sql" with the path to your actual SQL script file.

USE master;
GO

-- Create SampleDB
CREATE DATABASE SampleDB;
GO

USE SampleDB;
GO

-- Create Users table
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username NVARCHAR(50),
    Email NVARCHAR(100)
);
GO

-- Insert some sample data into Users table
INSERT INTO Users (UserID, Username, Email) VALUES (1, 'user1', 'user1@example.com');
INSERT INTO Users (UserID, Username, Email) VALUES (2, 'user2', 'user2@example.com');
INSERT INTO Users (UserID, Username, Email) VALUES (3, 'user3', 'user3@example.com');
GO

Step-3.1: Docker Build Locally

To build the Docker image, navigate to the directory containing the Dockerfile and your SQL script, then run:

docker build -t my-sqlserver-image .

Docker desktop > Image

Alt text

Step-3.2: Docker Run Locally

To run your SQL Server container locally for testing and development, use the following command:

docker run -d --name my-sqlserver-container -p 5432:5432 my-sqlserver-image

This command creates a container named my-sqlserver-container and maps port 5432 from the container to the host.

Docker desktop > Container

Alt text

Step-4: Test the SQL Server database connection using SSMS

Testing the SQL Server database connection using SQL Server Management Studio (SSMS) ensures that the database server is accessible and that users can connect to it successfully.

Launch SQL Server Management Studio (SSMS) and provide the necessary credentials to connect to the SQL Server instance.

SSMS > Login Page

Alt text

SSMS > After Login

Alt text

Step-5: Test the SQL server database connection using Azure Data Studio

Azure Data Studio is a cross-platform database tool that offers features similar to SQL Server Management Studio (SSMS) but with additional support for Azure services and extensions.

Launch Azure Data Studio and provide the necessary credentials to connect to the SQL Server instance.

SSMS > Login Page

Alt text

SSMS > After Login

Alt text

Step-6: Push Docker Container to ACR

Push your SQL Server container image to Azure Container Registry (ACR) for use in AKS. Follow these steps:

Log in to your Azure account using the Azure CLI:

az login

Authenticate to your ACR:

az acr login --name myacr

Replace myacr with your ACR name.

Tag your local Docker image with the ACR login server:

docker tag my-sqlserver-image myacr.azurecr.io/my-sqlserver-image:v1

Push the Docker image to ACR:

docker push myacr.azurecr.io/my-sqlserver-image:v1

Replace myacr and v1 with your ACR name and desired image version.

Now, your SQL Server container image is stored in Azure Container Registry and can be easily pulled and deployed from AKS to Azure Database for SQL Server - Flexible Server.

Conclusion

You have successfully created a Docker container for SQL Server database, container created as part of this task will be used in the future labs in AKS.

References