CODEWITHSUNDEEP
azure-devopsazure-pipelineshosted
Quentin Martinez
Quentin Martinez

Reputation: 236

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

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

Upvotes: 14

Views: 6060

Answers (5)

Jeff Barnard
Jeff Barnard

Reputation: 124

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

Upvotes: 1

Ogglas
Ogglas

Reputation: 69948

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/

Upvotes: 8

Tore Aurstad
Tore Aurstad

Reputation: 3796

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

Upvotes: 3

Saeb Amini
Saeb Amini

Reputation: 24380

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.

Upvotes: 2

Niklas Arbin
Niklas Arbin

Reputation: 692

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

Upvotes: 9

Related Questions