Running MSSQL in Portainer stack using Docker Compose

Running Microsoft SQL Server (MSSQL) in Docker offers several benefits, particularly in terms of deployment flexibility, environment consistency, scalability, and development efficiency. Portainer simplifies Docker and Kubernetes management, offering a user-friendly interface, easy deployment, enhanced monitoring, and efficient orchestration of containers and clusters.

Combining these is a killer combo!

Some key advantages

Ease of Deployment

Rapid Setup: With Docker, you can deploy an instance of MSSQL quickly and consistently using pre-built images from Microsoft.

Simplified Configuration: Configuring MSSQL is easier with Docker, as environment variables and Dockerfile instructions can automate many setup steps.

Environment Consistency

Replicable Environments: Docker ensures that MSSQL runs the same way on any system, reducing the "it works on my machine" problem. This is critical for consistent development, testing, and production environments.

Version Control: Docker makes it easy to deploy specific versions of MSSQL, ensuring compatibility and consistency across different environments.

Resource Efficiency

Isolation: Running MSSQL in a container isolates it from the host system and other containers, reducing potential conflicts and improving security.

Resource Management: Docker allows fine-grained control over the resources (CPU, memory) allocated to MSSQL, improving the overall efficiency on the host machine.

Scalability and Flexibility

Easy Scaling: In container orchestration platforms like Kubernetes or Docker Swarm, scaling MSSQL instances to handle increased load can be more straightforward.

Portability: Containers can be moved easily across different cloud providers or on-premises environments, offering high flexibility in deployment.

Development and Testing

Rapid Iteration: Developers can quickly spin up or tear down MSSQL instances, making it easier to test database-related changes.

Integration Testing: Containers can be part of CI/CD pipelines, facilitating automated testing with a real database instance.

Data Persistence and Backup

Volume Management: Docker volumes can be used for data persistence, ensuring that the data remains intact even when the container is destroyed.

Snapshot and Backup: Docker volumes facilitate the process of taking snapshots and backing up database data.

Setting up a stack in Portainer

Running MSSQL (Microsoft SQL Server) in a Portainer stack using Docker Compose involves creating a docker-compose.yml file with the necessary configuration for MSSQL and then deploying this stack through Portainer. You may skip creating the yaml file using the editor in Portainer instead.

Create the compose file

Considerations

Security: Select a very secure SA_PASSWORD, especially if the database is accessible over a network. And while it is possible to use AD-users with MSSQL in Docker it is not very common. For more information click here.
Data Persistence: The volume mssql_data is used for data persistence. Make sure to back up this volume for data safety.
Resource Allocation: MSSQL can be resource-intensive. Ensure your Docker host has enough resources (CPU, memory) to run MSSQL effectively.
Networking: Adjust network settings if necessary, especially if you're running in a complex network environment or using Docker Swarm.
Edition: Use the edition of MSSQL according to your needs, see this page. There's another lightweight MSSQL edition called Azure SQL Edge that's available as a docker container.

Compose file

name: mssql
services: 
  server: 
    container_name: mssql 
    hostname: mssql 
    user: root
    environment: 
      - ACCEPT_EULA=Y 
      - MSSQL_SA_PASSWORD=YOURSAPASSWORD123! 
      - MSSQL_PID=Developer 
      - MSSQL_MEMORY_LIMIT_MB=8192
      - MSSQL_BACKUP_DIR=/var/opt/mssql/backup 
    ports: 
      - 1433:1433 
    volumes: 
      - mssql_data:/var/opt/mssql:rw 
    image: mcr.microsoft.com/mssql/server:latest 
    deploy:
      resources:
        limits:
          cpus: '1.00'
          memory: 8192M
        reservations:
          cpus: '0.1'
          memory: 50M
      restart_policy:
        condition: unless-stopped
        delay: 5s
        window: 120s

volumes:
  mssql_data:
    name: mssql_data
    external: false

In this config, I've set this MSSQL stack with some limits.

8 GB Ram
Max 1 Core
Auto start and restart on fail unless stopped
Developer edition
I'm relying on creating and binding a volume. If you remove the stack, this volume is NOT removed from Portainer, so your data is safe.

Deploying to Portainer

Log into Portainer: Open your web browser and navigate to your Portainer instance.
Navigate to Stacks: In the Portainer UI, go to the "Stacks" section.
Add a New Stack:
1. Click on "Add stack".
2. Enter a name for your stack (e.g., mssql).
3. Copy and paste the content of your docker-compose.yml file into the Web editor.
Deploy the Stack: Click on "Deploy the stack" to create and start the MSSQL service.

And that's it

Good luck!

Welcome to the navigation