A while ago I was having an issue deploying a SQL Data Tools project using the Azure DevOps pipelines. It worked fine when there was no data in the database, but once there was some data to preserve in the release the DACPAC deployment started to timeout with the following error when running SqlPackage.
*** Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
So, I looked into this and added ‘/TargetTimeout:300 /p:CommandTimeout=1200‘ to the SqlPackage.exe command line section of the ‘Azure SQL Publish’ task.
However, this was not enough. The release still continued to timeout after approx. 60 seconds. It turns out that the build agent is not configured for long running queries and a registry change is required.
So the solution is to also add a PowerShell task before the SQL Publish that sets the registry first.
Write-Host "Setting registry to allow long running SQL queries"
C:\Windows\System32\reg.exe add HKCU\Software\Microsoft\VisualStudio\10.0\SQLDB\Database /v LongRunningQueryTimeoutSeconds /t REG_DWORD /d 0 /f
So the whole pipeline looks something like