Docker MSSQL Container

Step 1: Create an Environment File

The first step is to create an environment file that will store our database password.

.env

DB_PASSWORD=Password1234

This file will be used to set the MSSQL_SA_PASSWORD environment variable in our Docker container.

Step 2: Create a Docker Compose File

Next, we'll create a Docker compose file that defines our MSSQL container.

mssql-docker-compose.yml

version: '3.9'

services:
  mssql-db:
    # Apple M1 Chip
    # platform: linux/amd64
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: mssql-db
    restart: always
    env_file:
      - .env
    environment:
      ACCEPT_EULA: Y
      MSSQL_SA_PASSWORD: $DB_PASSWORD
      MSSQL_PID: Developer
      MSSQL_AGENT_ENABLED: "true"
    ports:
      - 1435:1433
    volumes:     
      - mssql_datadir:/var/opt/mssql
    networks:
      - backend-network

networks:
  backend-network:
    driver: bridge

volumes:
  mssql_datadir:

This file defines a single service, mssql-db, which uses the official MSSQL Server 2022-latest image and maps port 1435 on your local machine to port 1433 in the container.

Step 3: Start the Container

Now that we have our environment file and Docker compose file set up, it's time to start the container! Run the following command:

docker-compose -f mssql-docker-compose.yml up -d

This will start the container in detached mode, meaning it will run in the background.

Step4: Test MSSQL Database Container

Once the container is running, we can test our database by downloading a database backup file. Run the following commands:

curl -L https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksLT2019.bak -o AdventureWorksLT2019.bak

docker-compose -f mssql-docker-compose.yml cp AdventureWorksLT2019.bak mssql-db:/AdventureWorksLT2019.bak

Let us now restore data from the backup.

Note: you should have MSSQL CLI Client installed locally.

db_password=Password1234

mssql-cli -S localhost -U sa -P ${db_password} -Q "RESTORE DATABASE [AdventureWorks] FROM DISK='/AdventureWorksLT2019.bak' WITH MOVE 'AdventureWorksLT2012_Data' TO '/var/opt/mssql/data/AdventureWorks.mdf', MOVE 'AdventureWorksLT2012_Log' TO '/var/opt/mssql/data/AdventureWorks_log.ldf'"
mssql-cli -S localhost -U sa -P ${db_password} -Q "USE adventureworks; EXEC sp_changedbowner 'sa'; EXEC sys.sp_cdc_enable_db;"
mssql-cli -S localhost -U sa -P ${db_password} -d adventureworks -Q "EXEC sys.sp_cdc_enable_table @source_schema = 'saleslt', @source_name = 'customer', @role_name = NULL, @supports_net_changes = 0;"

Test the database objects-

mssql-cli -S localhost -U sa -P ${db_password} -Q "SELECT * FROM saleslt.customer;"

Step 5: Stop the Container

Finally, we can stop the container by running the following command:

docker-compose -f mssql-docker-compose.yml down

This will stop the container and remove it from memory.

That's it! We've successfully created and configured a Docker MSSQL container.