SQL Deployments to Azure Managed Instance

[aurora.brignola]

We have migrated our Databases to Azure SQL Managed Instance and the Octopus sql deployments are now failing with the following error:

*** Verification of the deployment plan failed.
Warning SQL72047: The option DisableBroker for service broker is not supported

The script runs fine when I manually execute it on the DB, so it must be something from the Deploy package step.

[clare.martin]

Good morning @aurora.brignola,

Thank you for contacting Octopus Support and welcome to the forums!

I am sorry to hear one of your deployments are failing, unfortunately the error you are seeing is coming from SQL not Octopus so we are fairly limited as to what support we can provide however we like to try help where we can.

You mentioned you are running this manually on the DB, are you going through SSMS and running the query directly on the DB? If so that will probably be different than deploying to SQL.

I did find some information online about this and it turns out the disable broker feature is not supported in an Azure SQL managed instance:

SQL Server Service Broker - SQL Server

Service broker is enabled by default and cannot be disabled. The following ALTER DATABASE options are not supported:

• ENABLE_BROKER
• DISABLE_BROKER

I am unsure why this works when you do it manually but it might be the way the script is running it, if you are disabling the broker directly on the Database through SSMS this may work but when running it via a script it wont.

I would see if Microsoft have any ideas on this as we are not Database experts with regards to the backend hosting, hopefully the information above helps and I am sorry we cannot provide you with any more insight.

Reach out if you have any further queries and I will do my best to help,
Kind Regards,
Clare