get a sql server (express) during a pipeline build azure devops

Question

i'm setting up a pipeline for asp.net application. During integration tests task i need to connect to a SQL server. How can i say to the pipeline that i need a sql service ?

I have tried with multiple microsoft hosted agent pool (Windows Server 1803, Hosted 2017 & 2019) I use Windows Server 1803 and issue is:

The operating system of the container does not match the operating system of the host.

I would like setting up correctly a temporaly sql server to running tests.

I have used localdb instead.

i run this script before my intregration tests task

SqlLocalDB.exe create "DeptLocalDB"  
SqlLocalDB.exe share "DeptLocalDB" "DeptSharedLocalDB"  
SqlLocalDB.exe start "DeptLocalDB"  
SqlLocalDB.exe info "DeptLocalDB"

To connect with powershell: Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "(localdb)\.\DeptSharedLocalDB"

To connect with sqlcmd: sqlcmd -S (localdb)\.\DeptSharedLocalDB

To connect a c# app (connectionString): "Data Source=(localdb)\.\DeptS haredLocalDB;Initial Catalog=DeptLocalDB;Integrated Security=True;"

If someone know how to mount a sql server in a container on azure pipeline, it will be appreciated. Thank for reading

Answer 1

Chocolatey is installed on windows-latest.

So, if you in you YAML file define:

  pool:
    vmImage: windows-latest

you can then install SQL Server Express using choco:

 - script: choco install sql-server-express

Answer 2

azure-pipelines.yml:

pool:
  vmImage: 'windows-latest'

...

- task: PowerShell@2
  displayName: 'start mssqllocaldb'
  inputs:
    targetType: 'inline'
    script: 'sqllocaldb start mssqllocaldb'

After the following connection string is valid:

Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=my-db;Integrated Security=True;

Source:

https://www.jannikbuschke.de/blog/azure-devops-enable-mssqllocaldb/