mssql-server-samplesdb

Easily deploy a Docker instance with SQL Server and all Microsoft Sample databases. Choose between stateless or stateful deployment for your SQL Server needs. Perfect for developers and DBAs looking for a quick and reliable database setup.

Quick Start

Run the image instantly with make all. For more control use the following commands:

make prerequisites # setup
make build         # build the image
docker compose up  # run the image

Connect to your SQL Server instance at localhost,14330 using sa and PaSSw0rd (configurable in docker-compose.yml).

Table of Contents:

• mssql-server-samplesdb
  • Quick Start
  • Features
  • Installation
    • Prerequisites
  • Steps
  • How to run the image
    • Databases included
  • Enable all databases
  • Stateless deployment
  • Stateful deployment
    • Permissions
      • Setting Up Local Directories for Container Mounts
      • How to Use the Script
    • Force Attach (optional)
  • Customization
    • How to change the SQL Server base image
    • How to add new databases to the image
    • How to change the sa password
  • FAQ
    • How does it works?
      • Restoring databases
        • Entrypoint
      • Avoid container to stop after deploy

Features

• Easy Setup: Deploy SQL Server in Docker with a simple command.
• Multiple Databases: Includes popular databases like - Northwind, Pubs, and AdventureWorks.
• Customizable: Options for stateless and stateful deployment.
• Community Driven: Open for contributions and enhancements.

Installation

Prerequisites

• Docker
• Make (optional)

Steps

1. Clone the repository: git clone https://github.com/enriquecatala/mssql-server-samplesdb.
2. Navigate to the directory and run make prerequisites.
3. Build the image: make build.
4. Start the container: docker compose up.

How to run the image

You can run the image with just executing make all, but if you want more control, you can execute the following commands:

# Create the folder where the databases will be restored and download the databases
# into ./Backups folder
# 
make prerequisites

# Build the image
make build

# Run the image
docker compose up

Now you can open your favorite SQL Server client and connect to your local SQL Server instance. By default:

• Server localhost,14330
• user:sa
• Password: PaSSw0rd

NOTE: You can find the credentials in the docker-compose.yml file

    environment:
      MSSQL_SA_PASSWORD: "PaSSw0rd"      
    ports:
      - "14330:1433"  

Databases included

Databases included:

• Pubs
• Northwind
• WideWorldImporters
• WideWorldImportersDW
• AdventureWorks2017
• AdventureWorksDW2017*
• AdventureWorks2016*
• AdventureWorks2014*
• AdventureWorks2012*
• StackOverflow2010*

NOTE: Databases marked with * must be switched on during build

Enable all databases

Only common databases are deployed by default. To deploy ALL databases in your container, please edit the .env file and set the following variable to 1:

INCLUDE_ALL_DATABASES=1

# to make sure that all databases are deployed, you can execute
make clean
# to build the image and run it
make all

IMPORTANT: StackOverflow2010 database is huge and it will require a couple of minutes to initialize. Please be patient. You can work and play within the other databases while the StackOverflow database is being prepared

Stateless deployment

Edit the docker-compose.yml file and comment the following lines:

#volumes:
#      - ${LOCAL_MOUNTPOINT}:/var/opt/mssql/data

NOTE: Doing that, will disable mounting the local folder specified in the .env file

Then, you can create and run the image with the following command:

docker compose up --build

IMPORTANT: StackOverflow2010 database is huge and it will require a couple of minutes to initialize. Please be patient. You can work and play within the other databases while the StackOverflow database is being prepared

Stateful deployment

With the docker-compose.yml file you will deploy all databases in a persistent folder in the host (remind to configure the .env file with a valid local folder):

• LOCAL_MOUNTPOINT

  The folder must exist ( for example: /home/enrique/your/path/to/volume/)

• SHARED_FOLDER

  The folder must exists. This shared folder can be used for example, to deploy backups or easily copy-paste between container and host

IMPORTANT: There is some kind of bug with WSL2 and if you want to use stateful deployment, you need to start your container inside the wsl2 image. You cant execute docker-compose up from windows

Permissions

When working with Docker containers that mount local volumes, managing file and directory permissions is crucial. These permissions ensure that the container has the appropriate access rights to the data stored on these volumes. To simplify this process, we have a script named prerequisites.create_local_directories.sh that automatically sets up the necessary directories and permissions.

NOTE: This is automatically done when you execute make prerequisites

Setting Up Local Directories for Container Mounts

The prerequisites.create_local_directories.sh script is designed to create local directories and configure their permissions to match the requirements of the Docker container. By running this script, you avoid the manual process of setting up these directories and permissions.

To understand what the script does, here's an overview of the steps involved:

1. Create Local Directories: The script creates directories on your host system that will be mounted into the Docker container. This includes data and shared folders.

   mkdir -p ./local_mountpoint/data/
   mkdir -p ./local_mountpoint/shared_folder/

2. Set Ownership: It changes the ownership of these directories to the user ID (UID) and group ID (GID) that the SQL Server in the Docker container runs as. This is typically UID 10001 and GID 0.

   sudo chown 10001:0 ./local_mountpoint/data/
   sudo chown 10001:0 ./local_mountpoint/shared_folder/

3. Adjust Permissions: The script sets the necessary read, write, and execute permissions on these directories to ensure that the container can access and modify the data as required.

   sudo chmod +rwx ./local_mountpoint/data/
   sudo chmod +rwx ./local_mountpoint/shared_folder/

How to Use the Script

NOTE: This is automatically done when you execute make prerequisites

Simply run the prerequisites.create_local_directories.sh script to automatically set up the directories and permissions:

./prerequisites.create_local_directories.sh

This approach streamlines the setup process and ensures consistency in the permissions, allowing your Docker container to function correctly with the mounted volumes.

And now, in the docker-compose.yml, you can reference that path, for example

    volumes:
       - ${LOCAL_MOUNTPOINT}:/var/opt/mssql/data

Now, when you start the container, you will see how the files are deployed locally

mssql-server-samplesdb | 2020-05-25 16:23:11.74 Server      Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2020-05-25 16:23:12.05 Server      Did not find an existing master data file /var/opt/mssql/data/master.mdf, copying the missing default master and other system database files. If you have moved the database location, but not moved the database files, startup may fail. To repair: shutdown SQL Server, move the master database to configured location, and restart.
2020-05-25 16:23:12.11 Server      Setup step is copying system data file 'C:\templatedata\mastlog.ldf' to '/var/opt/mssql/data/mastlog.ldf'.
2020-05-25 16:23:12.15 Server      Setup step is copying system data file 'C:\templatedata\model.mdf' to '/var/opt/mssql/data/model.mdf'.
....

Force Attach (optional)

NOTE: This is a hack for anyone who is still using Windows10 with WSL2 (win11 is fixed)

• FORCE_ATTACH_IF_MDF_EXISTS

  1 -> if you don´t want to "restore" and the files exists, you can attach those databases 0 -> if you did´nt executed docker-compose down, you can still "up" your container with previously restored databases

You can create and run the image with the following command:

docker compose up --build

Customization

How to change the SQL Server base image

The Dockerfile specifies which base SQL Server Instance you want to use for your image.

In case you want to change the version of the SQL Server used, please go edit the first line of the Dockerfile and select your prefered version. For example

Change

FROM mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

To

FROM mcr.microsoft.com/mssql/server:2017-latest-ubuntu

To get the latest SQL Server 2017 version with applied CU

NOTE: To see which SQL Server versions, please go here and select your "tag"

How to add new databases to the image

It´s as easy as modifying the Dockerfile, and adding the new backups you want to restore, and modifying the setup.sql file with the RESTORE command.

How to change the sa password

The password for the "sa" account is specified at the docker-compose.yml file.

FAQ

How does it works?

NOTE: If you want me to make a translation of this video to english, please show me a little of your support! and when I reach 150€ I´ll do it!

As you can see, its a little tricky but when you find how it works, its very simple and stable:

Dockerfile makes 3 mayor steps

Restoring databases

This is the tricky part since involves 2 scripts and the final command to keep alive the image

Entrypoint

COPY setup.* ./
COPY entrypoint.sh ./

RUN chmod +x setup.sh
RUN chmod +x entrypoint.sh

# This entrypoint start sql server, restores data and waits infinitely
ENTRYPOINT ["./entrypoint.sh"]

Avoid container to stop after deploy

To avoid the container to stop after first run, you need to ensure that is waiting for something. the best solution is to add a sleep infinity...as simple as it sounds :)

CMD ["sleep infinity"]