SQL Server

In a containerized deployment, the database is located on a SQL Server container which is run from a SQL Server image maintained by i2 Group on Docker Hub.

Running a SQL Server container

The SQL Server container uses the SQL Server image. In the docker run command, you can use -e to pass environment variables to the container. The environment variables are described in environment variables.

The container will run with a User ID and Group ID of 10001. All files in mounted directories will be created with these IDs. If files are manipulated externally these IDs must be retained or the container will not function correctly.

For more information about the command, see docker run reference.

Docker run command

The following docker run command runs a SQL Server container:

docker run -d \
   --name "sqlserver" \
   --network "eia" \
   --net-alias "sqlserver.eia" \
   -p "1433:1433" \
   -v "sqlserver_data:/var/opt/mssql" \
   -v "sqlserver_sqlbackup:/backup" \
   -v "/environment-secrets/simulated-secret-store/sqlserver:/run/secrets/" \
   -v "/home/<user-name>/analyze-deployment-tooling/prereqs/i2analyze/toolkit/examples/data:/var/i2a-data" \
   -e ACCEPT_EULA="Y" \
   -e MSSQL_AGENT_ENABLED=true \
   -e MSSQL_PID="Developer" \
   -e SA_PASSWORD_FILE="/run/secrets/SA_PASSWORD_FILE" \
   -e SERVER_SSL=true \
   -e SSL_PRIVATE_KEY_FILE="/run/secrets/server.key" \
   -e SSL_CERTIFICATE_FILE="/run/secrets/server.cer" \
   "i2group/i2eng-sqlserver:4.4.4"

For an example of the docker run command, see utils/server_functions.sh script. The run_sql_server does not take any arguments.

Storage

Named volumes are used to persist data and logs that are generated and used in the SQL Server container, as well as a separate volume for backups, outside of the container.

Note: It is good practice to have a separate volume for the backup from the database storage. For more information, see SQL Server Backup best practices.

To configure the SQL Server container to use these volumes, specify the -v option with the name of the volume and the path where the directory is mounted in the container. By setting -v option in the docker run command, a named volume is created. For SQL Server, the path to the directory that must be mounted is /var/opt/mssql. For example:

-v sqlserver_data:/var/opt/mssql 
-v sqlserver_sqlbackup:/backup
-v /environment-secrets/simulated-secret-store/sqlserver:/run/secrets

For more information, see Use Data Volume Containers.

Secrets:
A directory that contains all of the secrets that this tool requires. Specifically this includes credentials to access the database and certificates used in SSL.
The directory is mounted to /run/secrets inside the container. This can then be used by other environment variables such as SSL_PRIVATE_KEY_FILE to locate the secrets.
In a production environment, the orchestration environment can provide the secrets to the file system or the secrets can be passed in via environment variables. The mechanism that is used here simulates the orchestration system providing the secrets as files. This is achieved by using a bind mount. In production this would not be required.
Data:
The SQL Server container requires access to the data directory to run the ingestion scripts. To access the data, the data directory must be mounted into the container.

Environment variables

Environment Variable	Description
`ACCEPT_EULA`	Set to `Y` to confirm your acceptance of the End-User Licensing Agreement.
`MSSQL_AGENT_ENABLED`	For more information see Configure SQL Server settings with environment variables on Linux
`MSSQL_PID`	For more information see Configure SQL Server settings with environment variables on Linux
`SA_PASSWORD`	The administrator user's password.

The following environment variables enable you to use SSL:

Environment variable	Description
`SERVER_SSL`	See Secure Environment variables.
`SSL_PRIVATE_KEY_FILE`	See Secure Environment variables.
`SSL_CERTIFICATE_FILE`	See Secure Environment variables.

For more information about the SSL in SQLServer, see Specify TLS settings.