PSS®X package for ubuntu based MSSQL ¶

What is Microsoft SQL Server?¶

Info

Microsoft SQL Server (often referred to as MSSQL) is a relational database management system (RDBMS) developed by Microsoft.
It is designed to store and retrieve data as requested by other software applications, whether those applications run on the same computer or on another computer across a network.

The respective trademarks mentioned in the offering are owned by the respective companies, and use of them does not imply any affiliation or endorsement

Featured tags¶

2022-jammy-amd64
docker pull registry.gitlab.com/pss-x/support/containers/mssql:2022-jammy-amd64

About this image¶

Customized official container images for Microsoft SQL Server on Linux for Docker Engine.

How to use this image¶

You can get started with the SQL Server 2022

docker run --name pssx-sql-server -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=yourStrong(!)Password" -p 1433:1433 -d registry.gitlab.com/pss-x/support/containers/mssql:2022-jammy-amd64

Warning: These quick setups are only intended for development environments. You are encouraged to change the insecure default credentials and check out the available configuration options in the Configuration section for a more secure deployment.

Configuration¶

Requirements¶

This image requires Docker Engine 1.8+ in any of their supported platforms
At least 3.25 GB of RAM. Make sure to assign enough memory to the Docker VM if you're running on Docker for Windows
Requires the following environment flags;

"ACCEPT_EULA=Y"
"MSSQL_SA_PASSWORD="
"MSSQL_PID= (default: Developer)"
A strong system administrator (SA) password: At least 8 characters including uppercase, lowercase letters, base-10 digits and/or non-alphanumeric symbols.

Environment variables¶

You can use environment variables to configure SQL Server on linux containers.

ACCEPT_EULA confirms your acceptance of the End-User Licensing Agreement.

MSSQL_SA_PASSWORD is the database system administrator (userid = 'sa') password used to connect to SQL Server once the container is running. Important note: This password needs to include at least 8 characters of at least three of these four categories: uppercase letters, lowercase letters, numbers and non-alphanumeric symbols.

MSSQL_SA_PASSWORD_FILE by setting an environment variable matching *_FILE to a file path, the prefixed environment variable will be overridden with the value specified in that file.

MSSQL_PID is the Product ID (PID) or Edition that the container will run with.

Acceptable values:

Developer: This will run the container using the Developer Edition (this is the default if no MSSQL_PID environment variable is supplied)
Express: This will run the container using the Express Edition
Standard: This will run the container using the Standard Edition
Enterprise: This will run the container using the Enterprise Edition
EnterpriseCore: This will run the container using the Enterprise Edition Core : This will run the container with the edition that is associated with the PID

For a complete list of environment variables that can be used, refer to the documentation here.

Using a Docker Compose file¶

Docker offers different networking modes when running containers.Depending on the mode you choose you would connect to your MSSQL database running on the docker host differently.

When not specified, Docker Compose automatically sets up a new network and attaches all deployed services to that network. Both the docker host and the docker containers have an IP address on that bridge.

services:
  sqlserver:
    container_name: odms-sql-server
    image: registry.gitlab.com/pss-x/support/containers/mssql:2022-jammy-amd64
    ports:
      - 1434:1433
    environment:
      - MSSQL_SA_PASSWORD=yourStrong(!)Password
      - ACCEPT_EULA=Y
      - MSSQL_PID=Developer
    networks:
      - pssx-network

networks:
  pssx-network:
    name: app-pssx-network
    driver: bridge

To access your MSSQL with in docker container network

odms-sql-server,1433

Configure YOUR local Microsoft SQL Server Express¶

How about the example where container application would like to connect external MsSQL server. In that case we need to enable TCP support of the MsSQL server.

SQL Server Express does not enable TCP support by default. To enable TCP/IP:

Open Computer Management.
In SQL Server Configuration Manager, expand the SQL Server Network Configuration > Protocols for SQLEXPRESS node.
Right-click the TCP/IP item on the right; then select Properties.
On the General tab, change Enabled to Yes.
Click OK.
Restart the Microsoft SQL Server Express service using either the standard service control panel or the SQL Express tools.

sql-server-protocol

You can enable and start the SQL Server Browser Service by using the SQL Server Configuration Manager that is installed with Microsoft SQL Server.

Open Computer Management.
In SQL Server Configuration Manager, in the navigation pane on the left, choose SQL Server Services.
Right-click SQL Server Browser, and then choose Properties.
On the Service tab of the SQL Server Browser dialog box, set the Start mode to Automatic.
Choose OK to return to the SQL Server Configuration Manager main page.
Right-click SQL Server Browser again, and then choose Start.

sql-server-browser

To access your local Microsoft SQL Server Express on docker host

host.docker.internal\SQLEXPRESS

Persisting your application¶

If you remove the container all your data will be lost, and the next time you run the image the database will be reinitialized. To avoid this loss of data, you should mount a volume that will persist even after the container is removed.

For persistence you should mount a directory at the /var/opt/mssql path. If the mounted directory is empty, it will be initialized on the first run.

services:
  sqlserver:
    ...
    volumes:
      - sql_data:/var/opt/mssql

volumes:
  sql_data:
    name: pssx_sqldata
    driver: local

Handling sensitive information¶

In Docker, environment variables with the _FILE suffix are a convention used to specify that the value of the environment variable should be read from a file.

Environment Variable with _FILE Suffix: When an environment variable ends with _FILE, it indicates that the actual value of the variable should be read from the file specified by the path.
Usage in Docker: This approach is commonly used in Docker to securely pass sensitive information to containers without hardcoding the values in the docker-compose.yml file or Dockerfile.

services:
  sqlserver:
    ...
    environment:
      - MSSQL_SA_PASSWORD_FILE=/run/secrets/MSSQL_SA_PASSWORD
      - ACCEPT_EULA=Y
      - MSSQL_PID=Developer
    secrets:
      - MSSQL_SA_PASSWORD
    ...

secrets:
  MSSQL_SA_PASSWORD:
    file: ./etc/secrets/mssql-sa.pwd
   ...

Health checks¶

The healthcheck section in a Docker Compose file allows you to define a command that Docker will run to check the health of a service. This helps ensure that your services are running correctly and can automatically handle failures.

services:
  sqlserver:
    ...
    environment:
      - MSSQL_SA_PASSWORD=yourStrong(!)Password
      - ACCEPT_EULA=Y
      - MSSQL_PID=Developer
    ...
    healthcheck:
      test: /opt/mssql-tools18/bin/sqlcmd -C -S pssx-sql-server -U sa -P yourStrong(!)Password -Q "SELECT 1" -b -o /dev/null
      interval: 10s
      timeout: 3s
      retries: 10
      start_period: 10s
    ...

Docker secrets can be used to conceal the password in the function used for health check.

services:
  sqlserver:
    ... 
    environment:
      - MSSQL_SA_PASSWORD_FILE=/run/secrets/MSSQL_SA_PASSWORD
      - ACCEPT_EULA=Y
      - MSSQL_PID=Developer
    secrets:
      - MSSQL_SA_PASSWORD
    ...
    healthcheck:
      test: /opt/mssql-tools18/bin/sqlcmd -C -S pssx-sql-server -U sa -P $(cat '/run/secrets/MSSQL_SA_PASSWORD') -Q "SELECT 1" -b -o /dev/null
    ...

secrets:
  MSSQL_SA_PASSWORD:
    file: ./etc/secrets/mssql-sa.pwd
    ...

License¶

Legal Notice: Container License Information

By passing the value "Y" to the environment variable "ACCEPT_EULA", you are expressing that you have a valid and existing license for the edition and version of SQL Server that you intend to use. You also agree that your use of SQL Server software running in a Docker container image will be governed by the terms of your SQL Server license.

To specify the edition, use the MSSQL_PID environment variable. Details can be found in the "Environment Variables" section below.

SQL Server Developer edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server. SQL Server Developer Edition cannot be used in a production environment. The SQL Server 2017 Developer Edition license terms are located here.

PSS®X package for ubuntu based MSSQL¶