Azure Pipelines - Integration Tests with local SQL Server Fails

Bart Huls 0 Reputation points
2023-10-11T19:12:57.28+00:00

For the integration test we are are making use of the SQL Server database. The integration tests are currently running on a Self-Hosted Agent which contains a local installed SQL Server.

I want to get rid of the self-Hosted agents and move the integration test to an Azure pipeline (windows-latest)

According to the documentation I found on https://github.com/actions/runner-images there is no local SQL Server installed. I found some documentation about starting the SQL Server Express LocalDB (which is stangly enough not on installed software list)

In my Yaml pipeline I specified the following tasks:

- powershell: |
     sqllocaldb delete MSSQLLocalDB
     sqllocaldb create MSSQLLocalDB -s
     sqllocaldb start MSSQLLocalDB
     
     $sql = "CREATE database $(Database) on (name='$(Database)', filename='$(Agent.TempDirectory)\$(Database).mdf')"
     SqlCmd -d 'master' -S '(LocalDB)\MSSQLLocalDB' -W -h -1 -Q $sql

  displayName: 'start and create local DB'

- task: VSBuild@1   
  displayName: 'Build Database project'   
  inputs:     
    solution: src/Database/Database.sqlproj
    msbuildArgs: '/p:OutputPath=$(Build.ArtifactStagingDirectory)\Database /p:DSP=Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider'     
    platform: '$(BuildPlatform)'     
    configuration: '$(BuildConfiguration)'
    clean: true 

 powershell: |     
   dotnet tool update -g microsoft.sqlpackage         
   
   $database='$(Database)'
   & SqlPackage /Action:Publish `
       /SourceFile:$(Build.ArtifactStagingDirectory)\Database\Database.dacpac `      
	   /TargetServerName:'(localdb)\MSSQLLocalDB' `
       /TargetDatabaseName:$database `
       /p:AllowIncompatiblePlatform=true `
       /p:CreateNewDatabase=false `
       /p:AllowUnsafeRowLevelSecurityDataMovement=true `
       /P:DropObjectsNotInSource=true `            
       /p:DoNotDropObjectTypes='Logins;Users;Permissions;RoleMembership'
  continueOnError: true   
  displayName: 'Publish Database' 

This works locally and the Pubish command works fine with Azure databases.

Running the pipeline it thows an error like:

Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 5120, Level 16, State 108, Line 1 Unable to open the physical file "C:\Users\VssAdministrator\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\xxxx_MemoryFileGroup_32D38CB.mdf". Operating system error 0: "(null)".
Error SQL72045: Script execution error.  The executed script:
ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [MemoryFileGroup_32D38CB], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_MemoryFileGroup_32D38CB.mdf') TO FILEGROUP [MemoryFileGroup];

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,524 questions
{count} votes