I have an app which uses Azure SQL Server as data storage. The app has integration tests, where it connects to a real database - which for the purpose of tests is a SQL Server local DB that comes with Visual Studio.
To deploy the app we use Azure Devops pipelines with windows-latest image. One step of the deployment pipeline is calling all tests for the solution.
Now I am adding a full text search catalogue. I created a new migration and added
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("create fulltext catalog ft as default", true);
migrationBuilder.Sql("create fulltext index on dbo.Persons([FirstName] language 1033, [LastName] language 1033) key index PK_Persons on ft with stoplist = system", true);
}
When I run my integration tests the DB migration step fails with
SQLException: cannot use full-text search in user instance
This is as expected since localDb does not support full text indexing.
This would not be a problem on dev local machines as SQL Server Express with Advanced Services can be used. It is less ideal on the build server as it only has localDb as part of Visual Studio and Microsoft does not list any (other) SQL Server for the image.
So, what would be a correct (industry standard-ish) approach to address this problem?
For now I am considering using an environment variable to skip the migration step when run integration tests or check the db version on migration and skip the step for localdb.
I do not want to consider setting up a real db in Azure for the tests.
The migration runs fine over the production database.
After looking into the problem, I fixed it with following:
We use windows hosts and for an unknown to me reason Microsoft does not have a Windows container for SQL server, and the Linux container did not spin on a windows host, so, I had to move the 'tests' step into another yaml file and run it as a separate job
jobs:
- template: Tests.yml
inside the Tests.yml I have
jobs:
- job: RunTests
displayName: 'Tests'
pool:
vmImage: 'ubuntu-latest'
steps:
- task: DockerCompose@0
displayName: "Run SQL server"
inputs:
containerregistrytype: 'Container Registry'
dockerComposeFile: '$(Build.SourcesDirectory)/SqlServerContainer/docker-compose.yaml'
action: 'Run a Docker Compose command'
dockerComposeCommand: 'up -d'
which composes the container with SQL, adds full text search and spins it up.
For the docker-compose.yaml and the dockerfile itself we need to thank Ted Spence, I just changed from SQL Server 2022 image to 2019 as 2022 did not work for me the first time and I never gave it a second chance.
Please note that I also set context to be the same folder as the compose yaml - otherwise it did not want to see the dockerfile - although, it worked great on my dev machine without it.
version: "3.2"
services:
sqlserver:
container_name: sqlserver
build:
context: .
dockerfile: sqlserver-fulltext.Dockerfile
ports:
- "1433:1433"
environment:
SA_PASSWORD: "StrongPassword!!11"
ACCEPT_EULA: "Y"
sqlserver-fulltext.Dockerfile
FROM mcr.microsoft.com/mssql/server:2019-latest
# Switch to root to install fulltext - apt-get won't work unless you switch users!
USER root
# Install dependencies - these are required to make changes to apt-get below
RUN apt-get update
RUN apt-get install -yq gnupg gnupg2 gnupg1 curl apt-transport-https
# Install SQL Server package links - why aren't these already embedded in the image? How weird.
RUN curl https://packages.microsoft.com/keys/microsoft.asc -o /var/opt/mssql/ms-key.cer
RUN apt-key add /var/opt/mssql/ms-key.cer
RUN curl https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list -o /etc/apt/sources.list.d/mssql-server-2019.list
RUN apt-get update
# Install SQL Server full-text-search - this only works if you add the packages references into apt-get above
RUN apt-get install -y mssql-server-fts
# Cleanup
RUN apt-get clean
RUN rm -rf /var/lib/apt/lists
# Run SQL Server process
ENTRYPOINT [ "/opt/mssql/bin/sqlservr" ]
It is great Microsoft allows to install Full-Text Search on Linux with one command - but it would be even better if they had another container available with advanced services. Although, I want advanced services, somebody else what reports, another one wants replication - you can't have it all...
What I also considered: