How to Change Collation of an Azure SQL Managed Instance When There Are Dependency Errors

[PedroAcevedo]

In this article, I will explain how to change the collation for your Azure SQL Managed Instance when the following scenario is encountered:

You try to change the database collation with the following T-SQL:

USE [master]
GO
ALTER DATABASE [testdb] COLLATE SQL_Latin1_General_CP1_CS_AS
GO

And it fails with the following message:

Msg 5075, Level 16, State 1, Line 24

The column 'tablex.colx' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Steps in brief:

• Export the database to bacpac using SQLPackage.
• Modify the collation by editing the model.xml file.
• Import the database using SQLPackage and overriding the model.xml path.
• Change the database name or modify app connection string to use the new database.

Considerations:

• Schedule a maintenance window for your application during the process and stop the workload to prevent data loss.
• Ensure you have the latest SQLPackage version: Download and install SqlPackage - SQL Server | Microsoft Learn
• Review the different connection strings depending on the connection method outlined in these examples: SqlPackage Export - SQL Server | Microsoft Learn
• This article applies to Azure SQL DB and Azure SQL Managed Instance.

Steps in details:

1. Start the maintenance window for your application.

2. Export the database using SQLPackage:

sqlpackage.exe /Action:Export /ssn:tcp:<ServerName>.database.windows.net,1433 /sdn:<DatabaseName> /su:<UserName> /sp:<Password> /tf:<TargetFile>

3. Rename the *.bacpac file to *.zip so you can open it with the file explorer:

4. Copy the model.xml to a local folder “C:\Temp\model.xml”.

5. Edit the “C:\Temp\model.xml” with the desired collation and save the file.

For example:

 <Property Name="Collation" Value="Thai_CI_AS" />

6. Rename the file extension modified in step 3 from *.zip back to *.bacpac.
7.  Run the import using sqlpackage.exe. Use the flag /ModelFilePath:C:\Temp\model.xml to override the model.xml in the .bacpac file.

sqlpackage.exe /Action:Import /tsn:<server>.database.windows.net /tdn:<database> /tu:<user> /tp:<password> /sf:"C:\Temp\database.bacpac" /ModelFilePath:<path>model.xml

7. When the import operation is completed, change the database name, or modify the application connection string to use the new database name. You can also check the new collation setting in the database properties using SSMS:

8. Stop the maintenance window for your application and run the workload. Ensure that the application is working as expected.