At our company, we wanted to run tests as E2E as possible, both in our local dev computers running windows and in our pipeline: GitHub action with the latest ubuntu (https://github.com/actions/virtual-environments/blob/main/images/linux/Ubuntu2004-README.md), and that includes reaching out to a real SQL Server DB so we could verify our queries and tables are working fine.
We found it super challenging to create a solution that works in both Windows and Linux, lots of online suggestions don't seem to work anymore, so took us a while to work it out, but we finally managed so we decided to share with the world.
These are all the steps we followed:
The DB backup is normally huge, and we need to store it in Github and move it around, so we want it compressed.
The command tar matches the requirement, and works in windows 10 out of the box (https://superuser.com/a/1515028):
tar -cvzf mvc_db_2021-08-25.tar.gz mvc_db.bak
To be able to share access to it, we created a folder named 'sql_server_backup'.
Which is then shared via a volume in docker-compose, simplified example:
version: '3.9' services: tests: image: my-special-web-project-with-db-backup depends_on: - db volumes: - ./sql_server_backup:/sql_server_backup db: image: mcr.microsoft.com/mssql/server:2019-latest volumes: - ./sql_server_backup:/sql_server_backup volumes: sql_server_backup:
In our repo root folder, we created a set-local-environment.sh:
#!/bin/bash -e docker-compose -f docker-compose-infra.yml up -d cd sql_server_backup tar -xzvf *.tar.gz # docker attach allow us to the logs inside the container, VERY useful for debugging #docker attach db_1 echo "Docker compose finished running"
The order of the commands is quite important, we want docker compose to go first, as starting up the SQL Server instance can take a while, in the meantime we are decompressing the backup
This step has been the most difficult, as there is a timing issue, SQL Server can take a while to be ready for use, and recommendations to use scripts like wait-for-it.sh did not work for us.
What worked was leveraging the health check mechanism, that runs regularly, if the DB is not ready to start restoring, it tries again a bit later. Here is the relevant section in the docker-compose:
version: '3.9' services: db: image: mcr.microsoft.com/mssql/server:2019-latest environment: MSSQL_SA_PASSWORD: Password123 ACCEPT_EULA: Y MSSQL_PID: Express healthcheck: # copied / adapted from https://github.com/Microsoft/mssql-docker/issues/133, leveraging https://docs.docker.com/engine/reference/builder/#healthcheck test: sh /sql_server_backup/RestoreDb.sh interval: 10s timeout: 10s retries: 10 start_period: 45s ports: - "1433:1433" volumes: - ./sql_server_backup:/sql_server_backup
#!/bin/bash -e /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "Password123" -i /sql_server_backup/RestoreDb.sql
RestoreDb.sql (with a bit of debugging logic):
-- this file will be called from the docker compose health check if exists ( select 1 from sys.databases where [name] = 'MvcDocker' ) begin EXEC xp_logevent 60000, 'RestoreDb.sql: MvcDocker database already exists, no need to run again', informational; return -- already run end EXEC xp_logevent 60000, 'RestoreDb.sql: Started script', informational; -- begin write to log the list of files in our folder of interest declare @ss table ([filename] varchar(1000), depth int, [file] int) insert into @ss EXEC xp_dirtree '/sql_server_backup/', 2, 1 -- list all files in our folder of interest (https://stackoverflow.com/a/13594903) declare @msg varchar(2000) = 'RestoreDb.sql: Files found in /sql_server_backup/: ' select @msg = @msg + [filename] + ',' from @ss EXEC xp_logevent 60000, @msg, informational; -- end write to log the list of files in our folder of interest GO -- restore the backup, to names and folder that make sense here RESTORE DATABASE MvcDocker FROM DISK='/sql_server_backup/mvc_db.bak' WITH MOVE 'OriginalFileName' TO '/var/opt/mssql/data/MvcDocker.mdf', MOVE 'OriginalFileName_log' TO '/var/opt/mssql/data/MvcDocker_log.ldf' GO use MvcDocker GO -- check if the restored DB works as expected select top 5 DisplayName from [SomeTableFromYourDb]
In our case, we were using .net and only our integration tests needed the DB, so we run it last, simplified example:
jobs: build_and_test_and_publish: runs-on: ubuntu-latest steps: - name: Checkout uses: actions/checkout@v2 - name: Create folder BuildReports run: mkdir BuildReports - name: DotNet restore run: dotnet restore --verbosity m > BuildReports/Restore.txt - name: DotNet build run: dotnet build --no-restore --verbosity m --configuration Release /nowarn:CS1591 /p:Version=$NUGET_PACKAGE_VERSION > BuildReports/Build.txt - name: DotNet unit tests run: dotnet test --no-build --configuration Release --filter=Type=Unit > BuildReports/UnitTests.txt - name: Docker setup run: sh ./set-local-environment.sh > BuildReports/DockerSetup.txt - name: DotNet integration tests run: dotnet test --no-build --configuration Release --filter=Type=Integration > BuildReports/IntegrationTests.txt
Try this window on your docker, this is what you should see if every works as expected
You will notice the message 'database already exists' will keep showing up, that's because the docker compose health check keeps running non stop.
At first our health check command will not work, because while the container itself is 'ready', the SQL Server inside it is still loading up, so the command fails silently.
When SQL Server is ready, the next iteration of the health check command will work and the DB will be restore.
In the next health check iteration, the DB will be already restored, so we will start seeing the message 'database already exists'.
We are searching for talented software engineers, please have a look at https://clientapps.jobadder.com/30580/bizcover