0

I was handed over an Azure DevOps Build Pipeline to modify to work with an Azure SQL Managed Instance database. The current pipeline works perfectly with Azure SQL Server database. (Note that both have exactly same schema and data, Managed Instance database is basically a copy of old database in Azure SQL)

enter image description here enter image description here

Only change I made was updating the Server, db name (same as old) ,login and password (same as old). When I run the pipeline and,relese pipeline stucks at Azure SQL Database deployment Task. And I waited for an hour and finally gives an error saying We stopped hearing from agent Hosted Agent. Verify the agent machine is running and has a healthy network connection.

enter image description here

Can you please help me to understand why this is happening ? Whether I'm doing something wrong here? I couldn't find any documentation related to this.

enter image description here

I tried the same with a differenct database in the Managed Instance (with only 2 tables) and the release completed successfully (But took around 8 mintues to complete).

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
Amila
  • 1
  • 1
  • Can you post text instead of images? Many people (like myself) have image sites blocked on corporate networks. In the meantime I suggest you add some "probe" lines to your deployment script, for example a line that runs some powershell like `Test-NetConnection -Port 1433` to test basic connectivity. Also can you clarify what kind of authentication you are using (sql? AAD?) and also indicate what type of DevOps runtime you are using(locally hosted or cloud hosted) – Nick.Mc May 08 '23 at 06:59
  • @Nick.McDermaid Thank you. Images above show the release pipeline tasks. I used Azure SQL DacpacTask with Azure Pipeline cloud-hosted agent (windows-latest). I'm using SQL Authentication as the authentication type. I'll check with a connectivity check script but I think this would not be the case as It's working for another database. – Amila May 09 '23 at 00:54
  • @Nick.McDermaid I added the PowerShell as you suggested and TcpTestSucceeded : True. – Amila May 09 '23 at 01:11
  • Then I tried to run the same SqlPackage.exe command to publish dacpac generated locally (Using Command Prompt) and it still took hours to deploy. The same dacpac can be deployed in Azure SQL within seconds. Why the dacpac publish (to upgrade) in Managed Instance db is incredibly slow? and this is the SqlPackage command I tried out, /Action: Publish /SourceFile:"C:\DWH_ISMCRMMirror.dacpac" /TargetServerName:"SQL MI Host, port" /TargetDatabaseName:"db Name" /TargetUser:"UName" /TargetPassword:"*******" /p:ExcludeObjectTypes=Tables;Logins;Users;SecurityPolicies /TargetTimeout:120 – Amila May 09 '23 at 03:56
  • You could try ticking "Enable System Diagnostics" to dump more details into your DevOps log. You could also use `sp_who2` in the target database to see if the process is logged in and locked by something. – Nick.Mc May 09 '23 at 08:17

0 Answers0