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

score 8 · Answer 1 · edited Sep 19 '19 at 11:54

8

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

edited Sep 19 '19 at 11:54

simonauner

1,021
1
8
10

answered Sep 19 '19 at 11:25

Niklas Arbin

652
6
14

This works, but the step takes almost four minutes to complete. Is it posssible to speed it up? Is the connection string of the integration test project or project using this SQLEXPRESS still the same ? – Tore Aurstad Apr 02 '20 at 11:13
@ToreAurstad It's fastest to use LocalDB which is included. Using SQLExpress from Docker is also faster. – Niklas Arbin Apr 03 '20 at 13:51
@ToreAurstad See my answer with included `SQL Server Express LocalDB`. https://stackoverflow.com/a/65320975/3850405 – Ogglas Jan 18 '22 at 08:08

score 7 · Answer 2 · answered Dec 16 '20 at 09:57

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/

score 3 · Answer 3 · answered Apr 04 '20 at 23:48

Here a full example of how I achieved running Integration tests using Sql Express in Azure Devops. I had to use the YAML based pipelines so I could use simonauner's approach using Chocolatey to install Sql Express. Make note that I had to install EF Core tools since I use .Net Core 3.1 in this pipeline. Also note that I generate an EF Code First migration SQL file on the fly so that the rigged SQL Express instance is filled with contents.

Deploy SQL Express instance in Azure Devops, install and generate and run EF Code first migration sql script to update database with schema and seed data using EF Code First tools.

# ASP.NET Core (.NET Framework)
# Build and test ASP.NET Core projects targeting the full .NET Framework.
# Add steps that publish symbols, save build artifacts, and more:
# https://learn.microsoft.com/azure/devops/pipelines/languages/dotnet-core

trigger:
- feature/testability

pool:
  vmImage: 'windows-latest'

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

steps:
- script: choco install sql-server-express
- task: NuGetToolInstaller@1

- task: VisualStudioTestPlatformInstaller@1
  displayName: 'Visual Studio Test Platform Installer'
  inputs:
    versionSelector: latestStable

- task: NuGetCommand@2
  inputs:
    restoreSolution: '$(solution)'

- task: DotNetCoreCLI@2
  displayName: Build
  inputs:
    command: build
    projects: '**/*.csproj'
    arguments: '--configuration Debug' # Update this to match your need

- script: 'dotnet tool install --global dotnet-ef'
  displayName: 'Generate EF Code First Migrations SQL Script Update script'

- script: 'dotnet ef migrations script -i -o %BUILD_ARTIFACTSTAGINGDIRECTORY%\migrate.sql --project .\SomeAcme\SomeAcme.csproj'
  displayName: 'Generate EF Code First Migrations migrate.sql'

- script: 'sqlcmd -S .\SQLEXPRESS -Q "CREATE DATABASE [SomeAcmeDb]"'
  displayName: 'Create database SomeAcmeDb in Azure Devops SQL EXPRESS'

- script: 'sqlcmd -i %BUILD_ARTIFACTSTAGINGDIRECTORY%\migrate.sql -S .\SQLEXPRESS -d SomeAcmeDb'
  displayName: ' Run migrate.sql on SQL EXPRESS in Azure Devops'

# PowerShell
# Run a PowerShell script on Linux, macOS, or Windows
- task: PowerShell@2
  inputs:
    targetType: 'inline' # Optional. Options: filePath, inline
    #filePath: # Required when targetType == FilePath
    #arguments: # Optional
    script: 'gci -recurse -filter *.dll' # Required when targetType == Inline
    #errorActionPreference: 'stop' # Optional. Options: stop, continue, silentlyContinue
    #failOnStderr: false # Optional
    #ignoreLASTEXITCODE: false # Optional
    #pwsh: false # Optional
    #workingDirectory: # Optional

- task: VSTest@2
  displayName: 'VsTest - testAssemblies'
  inputs:
    testAssemblyVer2: |
     **\*SomeAcme.Tests.dll
     !**\*TestAdapter.dll
     !**\obj\**
    vsTestVersion: toolsInstaller
    testFiltercriteria: 'Category=IntegrationTest'
    runInParallel: false
    codeCoverageEnabled: false
    testRunTitle: 'XUnit tests SomeAcme solution integration test starting'
    failOnMinTestsNotRun: true
    rerunFailedTests: false

score 2 · Answer 4 · answered Oct 11 '19 at 01:20

2

In my case (some .NET Core integration tests that needed LocalDB), just using the windows-latest image did the job:

pool:
  vmImage: 'windows-latest'

The image comes with Visual Studio which includes LocalDB.

answered Oct 11 '19 at 01:20

Saeb Amini

23,054
9
78
76

1

Unfortunately the version included is SQL Server 2016 SP1 though so this fails if using any newer features – Martin Smith Mar 21 '21 at 16:57

score 1 · Answer 5 · answered Jun 16 '21 at 17:41

I need localdb in order to run database unit tests, and I also require at least SQL Server 2016 SP2 because my team is using some of the newer language features.

On a windows-2019 image, simply use choco or powershell to execute the following command:

choco upgrade sqllocaldb

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

5 Answers5