SQL Server in Containers (Docker)

Running SQL Server in Docker containers offers a streamlined and efficient approach to deploying and managing database instances. This method is particularly beneficial for development, testing, and continuous integration (CI) environments. By encapsulating SQL Server within a container, developers can ensure consistency across different stages of the application lifecycle.

🧱 1. Introduction to SQL Server in Docker

Docker provides a platform for developing, shipping, and running applications inside lightweight containers. These containers package an application and its dependencies, ensuring that it runs consistently across various environments. When SQL Server is containerized, it allows for rapid provisioning, isolation, and scalability of database instances.

⚙️ 2. Setting Up SQL Server in a Docker Container

2.1 Prerequisites

Before setting up SQL Server in Docker, ensure the following:

Docker Installation: Docker Engine 1.8 or later should be installed on your system. For installation instructions, refer to the official Docker documentation. (Docker: Install Containers for SQL Server on Linux – Learn Microsoft)
System Requirements: At least 2 GB of RAM and 2 GB of disk space are recommended. (Docker: Install Containers for SQL Server on Linux – Learn Microsoft)
SQL Server Image: Microsoft provides official SQL Server images for Linux. These images can be pulled from the Microsoft Container Registry. (Installing SQL Server in a Container – YouTube, Docker: Install Containers for SQL Server on Linux – Learn Microsoft)

2.2 Pulling the SQL Server Docker Image

To pull the latest SQL Server 2022 image, use the following command: (Docker: Install Containers for SQL Server on Linux – Learn Microsoft)

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

This command downloads the SQL Server image to your local machine, making it ready for containerization.

2.3 Running the SQL Server Container

Once the image is pulled, you can run the SQL Server container with the following command:

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=YourPassword123' -p 1433:1433 --name sql_server_container -d mcr.microsoft.com/mssql/server:2022-latest

Explanation of the parameters: (Docker: Install Containers for SQL Server on Linux – Learn Microsoft)

-e 'ACCEPT_EULA=Y': Accepts the End User License Agreement.
-e 'MSSQL_SA_PASSWORD=YourPassword123': Sets the password for the sa (System Administrator) account.
-p 1433:1433: Maps port 1433 of the container to port 1433 on the host, allowing external connections. (Docker: Install Containers for SQL Server on Linux – Learn Microsoft)
--name sql_server_container: Assigns a name to the container for easier reference.
-d: Runs the container in detached mode (in the background).

2.4 Connecting to the SQL Server Instance

After the container is running, you can connect to the SQL Server instance using tools like SQL Server Management Studio (SSMS) or Azure Data Studio. Use the following connection details: (How to Deploy & Connect an SQL Server Docker Container)

Server Name: localhost,1433
Authentication: SQL Server Authentication (How to Deploy & Connect an SQL Server Docker Container)
Login: sa
Password: The password you set during container creation (YourPassword123).

🛠️ 3. Configuring and Customizing SQL Server Containers

3.1 Data Persistence

By default, data stored within a Docker container is ephemeral. To ensure data persists across container restarts, you can mount a volume:

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=YourPassword123' -p 1433:1433 --name sql_server_container -v /path/to/host/data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2022-latest

This command mounts the host directory /path/to/host/data to the container’s data directory /var/opt/mssql, ensuring that database files are stored persistently.

3.2 Custom Configuration

You can customize the SQL Server instance by modifying the mssql.conf file located at /var/opt/mssql/mssql.conf within the container. To edit this file: (How to have docker compose init a SQL Server database)

docker exec -it sql_server_container bash
nano /var/opt/mssql/mssql.conf

After making changes, restart the SQL Server service:

/opt/mssql-tools/bin/sqlservr restart

3.3 Initializing Databases

To initialize databases upon container startup, you can use initialization scripts. Place your SQL scripts in a directory and mount it to the container: (How to have docker compose init a SQL Server database)

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=YourPassword123' -p 1433:1433 --name sql_server_container -v /path/to/sql/scripts:/usr/config/sql-scripts -d mcr.microsoft.com/mssql/server:2022-latest

Ensure that your SQL scripts are placed in the /path/to/sql/scripts directory on the host machine.

🔄 4. Managing Multiple SQL Server Containers

Docker allows you to run multiple containers simultaneously. To manage multiple SQL Server instances:

4.1 Running Multiple Containers

You can run multiple containers by specifying different names and ports:

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=YourPassword123' -p 1434:1433 --name sql_server_container_2 -d mcr.microsoft.com/mssql/server:2022-latest

This command runs a second SQL Server container with the name sql_server_container_2 and maps port 1434 on the host to port 1433 in the container.

4.2 Docker Compose for Multi-Container Environments

For complex setups, Docker Compose allows you to define and manage multi-container applications. Create a docker-compose.yml file:

version: '3.8'
services:
  sql_server_1:
    image: mcr.microsoft.com/mssql/server:2022-latest
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=YourPassword123
    ports:
      - "1433:1433"
  sql_server_2:
    image: mcr.microsoft.com/mssql/server:2022-latest
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=YourPassword123
    ports:
      - "1434:1433"

Run the containers with: (Docker: Install Containers for SQL Server on Linux – Learn Microsoft)

docker-compose up -d

This setup defines two SQL Server instances running on different ports.

🧪 5. Using SQL Server in Docker for Development and Testing

Containerized SQL Server instances are ideal for development and testing scenarios:

Rapid Provisioning: Quickly spin up new database instances for testing.
Isolation: Run multiple versions of SQL Server without conflicts.
Consistency: Ensure that the development environment matches production.

Developers can use tools like Azure Data Studio or SSMS to connect to these containers and perform database operations.

🔐 6. Security Considerations

When running SQL Server in Docker containers:

Use Strong Passwords: Always set a strong password for the sa account.
Limit Port Exposure: Expose only necessary ports to the host machine.
Regular Updates: Regularly pull the latest SQL Server images to ensure security patches are applied. (Docker: Install Containers for SQL Server on Linux – Learn Microsoft)
Data Encryption: Consider encrypting sensitive data stored within the database.

🛠️ 7. Troubleshooting and Maintenance

Viewing Logs: To view the logs of a running container:

  docker logs sql_server_container

Accessing the Container: To access the container’s shell:

  docker exec -it sql_server_container bash

Stopping and Restarting Containers: To stop a container:

  docker stop sql_server_container

To restart a container:

  docker start sql_server_container

Removing Containers: