Deploy MSSQL on MedStack Control

Microsoft SQL Server, commonly referred to as MSSQL, is a popular database technology used by Windows and Linux-based applications. MSSQL can be deployed to MedStack Control clusters as a Docker service in just a few minutes.

You can read more about using MSSQL in Docker with a Linux host on Microsoft's official documentation.

Limits

• As with all stateful services that run on MedStack Control, it is strongly recommended to mount Docker volumes at paths that contain important data, such as the default data directory for a database service. If a volume is not mounted to a database's data directory, the data will not be backed up by MedStack Control's backup agent.
• MSSQL as a Docker service requires at least 2000 MB of Memory to operate. As such, we recommend deploying a dedicated Medium node, or larger, to run MSSQL containers as a dedicated database server. This can be done using node labels and service placement constraints.
• As with all Docker services that operate in a MedStack Control cluster, the service is only accessible to other services and endpoints within the internal cluster network. This means that you cannot mount or shell into the MSSQL database service from a local machine, for example.

Example deploying MSSQL

Prerequisites: You have a cluster running a Manager node and at least one (1) Medium size Worker node or larger that is ready to be managed and can handle tasks.

Step 1 – Add a label to the Medium size or larger Worker node with the name "db" and value "mssql". This can be done by clicking for a node Actions > Label and adding the appropriate fields.

Step 2 – Create a Docker volume to ensure the data directory of the MSSQL service can be backed up by MedStack Control's backup agent. To set this up, navigate to Manage Docker > Volumes and add a new volume. In this example, we'll use "mssql-data" as the volume name. In Step 5, we'll mount the volume to the default data directory for the MSSQL service.

Step 3 – Create a service called "mssql-server" and specify the official MSSQL image on Docker Hub in the image field, which can be input as "mcr.microsoft.com/mssql/server:2019-latest". In this example, we'll use the "2019-latest" image tag.

Step 4 – Add the minimum required environment variables to ensure the container can start up at runtime. Those are (1) "ACCEPT_EULA=Y" and (2) "SA_PASSWORD=<YourStrongPassword>". In this example, we are using a randomly generated strong password.

For production environments, we strongly encourage users to mount sensitive parameters like "SA_PASSWORD" to a file using Docker Secrets.

Step 5 – Specify the data target path and Docker volume to ensure that data written to the default MSSQL data directory is backed up by the MedStack Control backup agent. The default data target path for MSSQL, unless otherwise specified or changed, is "/Software/Microsoft/MSSQLServer/MSSQLServer/DefaultData". Input this value in the target path field, and select the "mssql-data" from the list of available volumes.

Step 6 – Finally, add a placement constraint to ensure this service only operates containers on the node with the label "db=mssql" as this node will act as a single database server for mssql. The placement constraint will be "node.labels.db == mssql"

Note, in a practical environment that runs multiple services in addition to the mssql service, you may wish to configure all other services with a placement constraint such as "nodel.labels.db != mssql" to ensure other services do not run on the dedicated database server node.

Done!

When the service is running, you'll be able to SSH into a container and interact with the service via CLI through the MedStack Control web shell.