Introduction

On the contrary to MySql and Postgres that execute scripts in the /docker-entrypoint-initdb.d/ directory after initialization, SQL Server container image doesn’t have an option for running scripts at startup.

In this post, I’ll show how to create a custom container image that waits for SQL Server startup and runs a pre-configured script. In addition, I’ll show how to use this custom image to run SQL Server in Testcontainers.

How to run scripts on SQL Server initialization

SQL Server’s Docker image documentation points to this code as an example of how to run scripts on its initialization, but the script is old and doesn’t work.

I opened a Pull Request with a fixed version of the code. If you liked the solution, please leave a thumbs up there so it can be merged.

Below, I’ll explain each of the files.

1 - Dockerfile

The Dockerfile starts from SQL Server 2022 image and copies all the files in the directory to the /usr/config directory of the container. Then, it sets the container entrypoint to the entrypoint.sh file.

FROM mcr.microsoft.com/mssql/server:2022-latest

# Create a config directory
RUN mkdir -p /usr/config
WORKDIR /usr/config

# Bundle config source
COPY . /usr/config

ENTRYPOINT ["./entrypoint.sh"]

2 - entrypoint.sh

This bash script starts the configure-db.sh file without waiting for the process to end and starts the SQL Server process.

#!/bin/bash

# Start the script to create the DB and user
/usr/config/configure-db.sh &

# Start SQL Server
/opt/mssql/bin/sqlservr

3 - configure-db.sh

This is the most important part. The script runs until timeout (in this example, set to 60 seconds in the TRIES variable) or until success, querying SQL Server for the database status.

Then it runs the setup.sql script file and logs Configuration completed. to the console in case of success, or exits with status code 1 and logs a timeout message in case of error.

ℹ️ The Configuration completed message is important because we can use it to wait for the scripts to complete before accessing the database. I’ll use it in the next section when starting the container from Testcontainers.

#!/bin/bash

# Calls SQLCMD to verify that system and user databases return "0" which means all databases are in an "online" state,
# then run the configuration script (setup.sql)
# https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017 

TRIES=60
DBSTATUS=1
ERRCODE=1
i=0

while [[ $DBSTATUS -ne 0 ]] && [[ $i -lt $TRIES ]]; do
	i=$((i+1))
	DBSTATUS=$(/opt/mssql-tools/bin/sqlcmd -h -1 -t 1 -U sa -P $MSSQL_SA_PASSWORD -Q "SET NOCOUNT ON; Select COALESCE(SUM(state), 0) from sys.databases") || DBSTATUS=1
	
	sleep 1s
done

if [ $DBSTATUS -ne 0 ]; then 
	echo "SQL Server took more than $TRIES seconds to start up or one or more databases are not in an ONLINE state"
	exit 1
fi

# Run the setup script to create the DB and the schema in the DB
echo "Running configuration script..."

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $MSSQL_SA_PASSWORD -d master -i setup.sql

echo "Configuration completed."

4 - setup.sql

This is the script that will be run. Here I’m just creating the database.

CREATE DATABASE [MyDatabase]
GO

5 - Building the container image

Now, we have to build the container image from the Dockerfile we created. In this example, I’m naming the image mydatabase-sqlserver:

docker build -t mydatabase-sqlserver .

6 - Starting the container

Finally, we can run the container with docker run:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=yourStrong(!)Password" -p 1433:1433 mydatabase-sqlserver

Or create a compose.yaml file:

services:
  sqlserver:
    build: .
    environment:
      MSSQL_SA_PASSWORD: "yourStrong(!)Password"
      ACCEPT_EULA: "Y"
      MSSQL_PID: "Developer"
    ports:
      - "1433:1433"

And run:

docker compose up

Source code of this example

• https://github.com/dgenezini/SqlServerScriptOnStartup

Running the configuration script in Testcontainers

Testcontainers is a library that provides lightweight, throwaway instances of databases, selenium web browsers, or anything that can run in a container. These instances can be especially useful for testing applications against real dependencies, like databases, that can be created and disposed of after the tests.

I explained about Testcontainers in this post.

I strongly suggest reading, as here, I’ll show only the specifics of running the SQL Server with the configuration scripts in Testcontainers.

Starting the container

When starting the container, we need to override two configurations in the MsSqlBuilder:

• With the WithImage method, we override MsSqlBuilder default image with our custom image. In this example, mydatabase-sqlserver;
• With the WaitStrategy method, we override MsSqlBuilder wait strategy with UntilMessageIsLogged. This will make the StartAsync method hang until the message Configuration completed is logged in the container logs, making sure our script does run before we use the container.

1
2
3
4
5
6
7

var MsSqlContainer = new MsSqlBuilder()
    .WithImage("mydatabase-sqlserver")
    .WithPassword("SqlServer2022!Password")    
    .WithWaitStrategy(Wait.ForUnixContainer().UntilMessageIsLogged("Configuration completed"))
    .Build();

await MsSqlContainer.StartAsync();

ℹ️ We can use the ExecScriptAsync method of the DockerContainer class to run scripts in the container, but the idea here is to have a default script that runs outside of Testcontainer also, when running the application locally, for example.

Source code of this example

• https://github.com/dgenezini/TestcontainersSqlServerSample