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:
1: Use a compression method that works in both Windows and Linux
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
2: Put backup in a place accessible by SQL Server container
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:
3: Run docker compose and decompress the 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
4: Restore 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
RestoreDb.sh:
#!/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]
5: Github workflow
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
Debugging
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'.
PS
We are searching for talented software engineers, please have a look at https://clientapps.jobadder.com/30580/bizcover
Top comments (0)