Alter database with random behavior in Azure MI connection

Question

Hi Team,

We are providing support for Azure Managed Instance in our application. We want to alter database and want to change its Collate.
As per the observation, we able to change the collate of database, but sometimes database gets locked and database entry gets added in "sys.dm_tran_locks" table.
Can you please help us, why we are facing this random behavior (as database is not getting locked always)?

Answer 1

Hi Sayali Khot (skhot),

Welcome to Micrsoft Q&A forum.

As I understand, you are experiencing random behavior in Azure SQL Managed Instance connection.

From the verbatim, it seems like this is due to dependency errors.

Please refer to the Microsoft techcommunity blogpost detailing on how to change collation in such case:

https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-change-collation-of-an-azure-sql-managed-instance-when/ba-p/4046454

Server-level collation in Azure SQL Managed Instance can be specified when the instance is created and cannot be changed later. You can set server-level collation via Azure portal or PowerShell and Resource Manager template while you are creating the instance. Default server-level collation is SQL_Latin1_General_CP1_CI_AS.

Let us know if this helps in your scenario.

Thanks