How to Run Microsoft SQL Server|MSSQL Express on Docker

SQL Server Express is a popular MSSQL Edition. The easiest way to install SQL server Express is to use a docker container. This means you use Docker to isolate MSSQL express in a disposable environment, and no need to install it locally.

Dive into this guide and learn how to run SQL Server Express Edition on a Docker container.

Step 1: Why Run SQL Server Express in Docker

Docker provides compatibility to SQL Server 2019 Express, SQL Server 2017, Express SQL Server 2019 Express, and so on.

• This means Docker is designed to let you choose the version of MSSQL Express based on your choice.
• Running your SQL Server Express in a Docker container creates an isolated and portable database environment.
• Docker has official images for SQL Servers like Express and you can use them to deploy instances quickly.

Step 2: What you Need to Run MSSQL Express with Docker

You are running MSSQL Express with Docker. Right away:

• Ensure you have installed Docker on your machine and it up and running.
• Port 1433 should be available on your machine.
• Download and install SQL Server Management Studio.

Related: Guide to TypeORM MSSQL with Nest.js and Typescript

Step 3: How to Run SQL Server Express Image on Docker

The simple to have SQL express on Docker in to use Docker compose. You create a docker-compose.yml file indicating how Docker will access the SQL Server DockerHub image and spin your Express as a container:

Ensure you have created a docker-compose.yml file and give Docker the following MSSQL Express Server instructions:

version: '3.9'
services:
  sql-server-express:
    image: mcr.microsoft.com/mssql/server:latest
    container_name: docker-sql-server-express
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=MyPass@word
      - MSSQL_PID=Express
    ports:
      - "1433:1433"

Based on this code sample:

• You will pull the official SQL Server Express Docker image
• Tell Docker to create a container named docker-sql-server-express
• Expose SQL Server Express on port 1433.
• Set the MSSQL server access details using environment.

Note: Port 1433 must be available. If not, this container won’t work as it may conflict with possible SQL instances running on your machine. But no worries. You can always change the container port to an available port number, for example:

    ports:
      - "1439:1433"

Step 4: Spinning SQL Express Server with Docker compose

To your SQL Express Server container, Docker compose has it specific command that will run the docker-compose.yml file and set up a container.

Run the following command from cmd or PowerShell:

docker-compose up --build -d

This should build the container as follows:

Go to Docker and confirm is SQL Express Server is running:

Step 5: Backing up SQL Express Server data on Docker

Based on what this container runs, and data saved to the server will be lost once the container stops running.

O Docker, you create volumes to persist data. This way, you can start, stop, and retry your MSSQL Express container without losing the previously saved data. Update your docker-compose.yml file as follows:

version: '3.9'
services:
  sql-server-express:
    image: mcr.microsoft.com/mssql/server:latest
    container_name: docker-sql-server-express
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=MyPass@word
      - MSSQL_PID=Express
    ports:
      - "1433:1433"
    volumes:
      - sql-data:/var/opt/mssql
volumes:
  sql-data:

Rerun Docker Compose:

docker-compose up --build -d

Step 6: Connecting SQL Express Server to SSMS on Docker

You need to access your Dockerized SQL Express Server. This example will use a SQL Server client tool SQL Server Management Studio to connect to the SQL Server Express instance running in the Docker container.

Open your SQL Server Management Studio:

• Add localhost,1433 as the server. If you used another port, for example, 1439, use localhost,1439.
• Select SQL Server Authentication.
• Login should be sa
• Password will be MyPass@word based on - SA_PASSWORD=MyPass@word.

Connect and access your SQL Express Server:

Now, proceed and use your MSSQL Express Server and execute your queries.

Conclusion

This guide taught you how to run SQL Server Express Edition in a Docker container. SQL Server Express provides a subset of the features available in the full versions of SQL Server. It is a fully functional database system. I hope you found every step helpful.