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
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.
Configuration completedmessage 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
docker build -t mydatabase-sqlserver .
6 - Starting the container
Finally, we can run the container with
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=yourStrong(!)Password" -p 1433:1433 mydatabase-sqlserver
Or create a
services: sqlserver: build: . environment: MSSQL_SA_PASSWORD: "yourStrong(!)Password" ACCEPT_EULA: "Y" MSSQL_PID: "Developer" ports: - "1433:1433"
docker compose up
Source code of this example
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
- With the
WithImagemethod, we override
MsSqlBuilderdefault image with our custom image. In this example,
- With the
WaitStrategymethod, we override
MsSqlBuilderwait strategy with
UntilMessageIsLogged. This will make the
StartAsyncmethod hang until the message
Configuration completedis logged in the container logs, making sure our script does run before we use the container.
ℹ️ We can use the
ExecScriptAsyncmethod of the
DockerContainerclass 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.