I am deploying a database to an Azure SQL Server with Azure Pipelines. The deployment is working really well. So I added the creation and deployment of a temporary database (one that is removed at the end of the pipeline) for unit testing of my USP's.
I created a unit test .dll with SSDT. I configured the app.config and tested it on my laptop in Visual Studio. On my laptop it works, the unit tests are run an all pass.
I tried to connect to the server with the credentials from the app.config in SSMS. The connection is established.
But, I cant get the pipeline (or hosted agent) task to connect to the database from within the pipeline.
I tried adding the IP of the agent to the server whitelist. I checked the "allow azure services to access the database". These did not solve the problem.
The error I get is this:
Initialization method BuilEnDeployTest_Unittest.SqlServerUnitTest001.TestInitialize threw exception. System.Data.SqlClient.SqlException: System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) ---> System.ComponentModel.Win32Exception: An existing connection was forcibly closed by the remote host.
pipeline yml:
trigger:
- test
name: $(TeamProject)_$(Date:yyyyMMdd)$(Rev:_r)
variables:
- group: "DatabaseDeploy"
- name: databaseName
value: sqldb-AzureDevOps_$(Build.BuildNumber)
#- name: ExecutionContext.__Database__
# value: $(variables.databaseName)
stages:
### Building the code in the Pull-Request
- stage: Build
displayName: Build the project
jobs:
- job: Build
displayName: Build DB project
pool:
vmImage: windows-latest
steps:
- script: echo '$(Build.BuildNumber)'
displayName: BuildName
#- powershell:
# Get-ChildItem -Path $(Agent.WorkFolder)\1\s\BuilEnDeployTest_Unittest -recurse
# displayName: Folder structure
#- script: echo '$(Agent.WorkFolder)\1\s\BuilEnDeployTest_Unittest\app.config'
# displayName: app.config
- powershell:
Get-Content -path $(Agent.WorkFolder)/1/s/BuilEnDeployTest_Unittest/app.config
displayName: app.config
- powershell: >
(Get-Content -path $(Agent.WorkFolder)/1/s/BuilEnDeployTest_Unittest/app.config -Raw)
-replace 'MyDatabase', '$(databaseName)'
-replace 'MyServer', '$(ServerName_unittst).database.windows.net'
-replace 'MyUser', '$(DatabaseUser_unittst)'
-replace 'MyPassword', '$(DatabasePassword_unittst)'| Set-Content -Force -Path $(Agent.WorkFolder)/1/s/BuilEnDeployTest_Unittest/app.config
- powershell:
Get-Content -path $(Agent.WorkFolder)/1/s/BuilEnDeployTest_Unittest/app.config
displayName: app.config
- task: VSBuild@1
displayName: Building the database project
inputs:
solution: '**\*.sln'
- task: CopyFiles@2
displayName: Copy Build Artifacts
inputs:
SourceFolder: '$(agent.builddirectory)\s'
Contents: '**'
TargetFolder: '$(build.artifactstagingdirectory)'
### In the commands below, 'pipeline' is used instead of 'Container', allthough
### 'Container' is often seen in the online documentation.
### I have noticed that changing 'pipeline' to 'Container' breaks my code
### I haven't spend time in figuring out why. It could be because of the path I use
### down the line.
- task: PublishPipelineArtifact@1
displayName: Publish Build Artifacts
inputs:
PathtoPublish: '$(Build.ArtifactStagingDirectory)'
ArtifactName: 'database'
publishLocation: 'pipeline'
#### Unit testing the Pull-Request
- stage: Unit_testing
displayName: Database UnitTest
condition: and(succeeded(), eq(variables['Build.Reason'], 'PullRequest'))
jobs:
- job: Deploy_to_temporary_database
displayName: Deploy to temporary database
pool:
vmImage: windows-latest
steps:
############ Create the Azure Resources as defined in the AzureSQLARMTemplate.json
## When the Azure SQL server does not exist it is also created by the ARM deployment.
## NOTE: I experienced errors when deleting and recreating the Azure SQL server. This could be
## because of the little time between deletion and creation.
## Deployment through the ARM template can deal with an existing server. In that case
## the server creation is skipped. So best to not delete the Azure SQL server untill the project
## is abandoned.
- task: AzurePowerShell@5
displayName: 'Create unittest DB'
inputs:
azureSubscription: $(connectedServiceNameARM)
ScriptType: 'FilePath'
ScriptPath: './ARMtemplate/CreateAzureSQLAndDB.ps1'
ScriptArguments: -azuresqlserverName $(ServerName_unittst) -sqlserverAdminLogin $(DatabaseUser_unittst) -sqlserverAdminPassword $(DatabasePassword_unittst) -databaseName $(databaseName) -Location 'northeurope' -ResourceGroupName 'rg-temp' -TemplateFile ARMtemplate\AzureSQLARMTemplate.json
azurePowerShellVersion: 'LatestVersion'
############ Download the build artifacts from the build stage
- task: DownloadPipelineArtifact@2
displayName: 'Download artifacts'
inputs:
buildType: 'current'
artifactName: 'database'
targetPath: '$(Pipeline.Workspace)/database'
- powershell:
Get-ChildItem -Path $(Pipeline.Workspace)/database -recurse
displayName: Folder structure
- powershell:
Get-Content -path $(Pipeline.Workspace)/database/a/BuilEnDeployTest_Unittest/bin/Debug/BuilEnDeployTest_Unittest.dll.config
displayName: BuilEnDeployTest_Unittest.dll.config
############ Deploy the database dacpac file to the newly created database
- task: SqlAzureDacpacDeployment@1
displayName: 'Deploy unittest DB'
inputs:
connectedServiceNameARM: $(connectedServiceNameARM)
ServerName: $(ServerName_unittst).database.windows.net
DatabaseName: $(databaseName)
SqlUsername: $(DatabaseUser_unittst)
SqlPassword: $(DatabasePassword_unittst)
DacpacFile: $(Pipeline.Workspace)/database/a/$(dacpacfile_location)
PublishProfile: $(Pipeline.Workspace)/database/a/$(publishprofile_location)
## Create a firewall rule for the IP adress of the build agent
- task: AzurePowerShell@5
displayName: 'Add buildserver public ip'
inputs:
azureSubscription: $(connectedServiceNameARM)
ScriptType: InlineScript
Inline: |
$ip = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content
$AzureSQLFirewallRule = Get-AzSqlServerFirewallRule -ResourceGroupName 'rg-temp' -ServerName $(ServerName_unittst) -FirewallRuleName "azuredevops"
if ($AzureSQLFirewallRule -eq $null) {New-AzSqlServerFirewallRule -ResourceGroupName 'rg-temp' -ServerName $(ServerName_unittst) -FirewallRuleName "azuredevops" -StartIpAddress $ip -EndIpAddress $ip}
azurePowerShellVersion: 'LatestVersion'
############ Run the unittest
- task: VSTest@2
inputs:
testAssemblyVer2: $(Pipeline.Workspace)/database/a/$(Unittestdll_location)
testConfiguration: 'Debug'
runOnlyImpactedTests: true
runInParallel: false
############ Delete the newly created database
- task: AzurePowerShell@5
displayName: 'Delete unittest DB'
inputs:
azureSubscription: $(connectedServiceNameARM)
ScriptType: 'FilePath'
ScriptPath: './ARMtemplate/CleanupAzureSQLAndDB.ps1'
ScriptArguments: -azuresqlserverName $(ServerName_unittst) -databaseName $(databaseName) -ResourceGroupName 'rg-temp'
azurePowerShellVersion: 'LatestVersion'
## Delete a firewall rule for the IP adress of the build agent
- task: AzurePowerShell@5
displayName: 'remove buildserver public ip'
inputs:
azureSubscription: $(connectedServiceNameARM)
ScriptType: InlineScript
Inline: |
$ip = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content
Remove-AzSqlServerFirewallRule -ResourceGroupName 'rg-temp' -ServerName $(ServerName_unittst) -FirewallRuleName "azuredevops"
azurePowerShellVersion: 'LatestVersion'
## Deploying the merged result to the database
- stage: Deploy_to_tst
displayName: Deploy
condition: and(succeeded(), ne(variables['Build.Reason'], 'PullRequest'))
jobs:
- job: Deploy
displayName: Deploy
pool:
vmImage: windows-latest
steps:
- task: DownloadPipelineArtifact@2
inputs:
buildType: 'current'
artifactName: 'database'
targetPath: '$(Pipeline.Workspace)/database'
- powershell:
#Get-ChildItem -Path $(Pipeline.Workspace)/database -recurse
#Get-ChildItem -Path $(Agent.Workspace) -recurse
- task: SqlAzureDacpacDeployment@1
displayName: 'Deploying the database to Azure'
inputs:
connectedServiceNameARM: $(connectedServiceNameARM)
ServerName: $(Server_tst)
DatabaseName: $(DatabaseName_tst)
SqlUsername: $(DatabaseUser_tst)
SqlPassword: $(DatabasePassword_tst)
DacpacFile: $(Pipeline.Workspace)/database/a/$(dacpacfile_location)
PublishProfile: $(Pipeline.Workspace)/database/a/$(publishprofile_location)
For hosted agent check if you can create a local db with admin username and password. You can use these credentials later to connect to the database. Normal approach is usually just test if your sql project is deployable. You create local db and nuke it.
For your unit tests you can always keep a developer databae in your release pipeline where you run your test cases.
Edited:
For unittesting Azure SQL code you can use a 'normal' SQL server (version 16 and above) because the code will get the same results. Thus, the local database, available in the agent will be suitable for the unittests.
Start the localdb in the agent:
- task: PowerShell@2
displayName: 'start mssqllocaldb'
inputs:
targetType: 'inline'
script: 'sqllocaldb start mssqllocaldb'
The connectionstring to this db will be:
(localdb)\MSSQLLocalDB;Initial Catalog=my-db;Integrated Security=True;
Beware: On internet there are websites that have the connectionstring as (localdb)\\MSSQLLocalDB;Initial Catalog=my-db;Integrated Security=True; This can cause you to spend two hours in debugging and finally discovering that the connection string has one "\" too many.
Use this connectionstring in your app.config. This can be done by a replacement:
- powershell: >
(Get-Content -path $(Agent.WorkFolder)/1/s/{Your solution name}/app.config -Raw)
-replace 'MyConnectionString', 'Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=my-db;Integrated Security=True;' | Set-Content -Force -Path $(Agent.WorkFolder)/1/s/{Your solution name}/app.config
Besides building and copying the artefacts, I needed to add the following yml code to the pipeline script.
Deploy the database:
- script: SqlPackage.exe /Action:Publish /SourceFile:"$(Pipeline.Workspace)\database\a\{Your project name}\bin\Debug\{Your database name}.dacpac" /TargetDatabaseName:my-db /TargetServerName:"(localdb)\MSSQLLocalDB"
workingDirectory: C:\Program Files\Microsoft SQL Server\160\DAC\bin\
displayName: 'deploy database'
The unit tests themselves kept on failing due to a missing dependency. To solve this I added the Microsoft Data SqlClient to my solution in Visual Studio and restored the dependencies with the following yml:
- task: NuGetCommand@2
inputs:
restoreSolution: '$(solution)'