How to Change Collation of an Azure SQL Managed Instance When There Are Dependency Errors
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: Start the maintenance window for your application. Export the database using SQLPackage: sqlpackage.exe /Action:Export /ssn:tcp:<ServerName>.database.windows.net,1433 /sdn:<DatabaseName> /su:<UserName> /sp:<Password> /tf:<TargetFile> Rename the *.bacpac file to *.zip so you can open it with the file explorer: Copy the model.xml to a local folder “C:\Temp\model.xml”. Edit the “C:\Temp\model.xml” with the desired collation and save the file. For example: <Property Name="Collation" Value="Thai_CI_AS" /> Rename the file extension modified in step 3 from *.zip back to *.bacpac. 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 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: Stop the maintenance window for your application and run the workload. Ensure that the application is working as expected.How to Change collation for production Azure SQL databases
Azure SQL Database only supports changing collation by modifying the model.xml file for .bacpac files. Schedule a maintenance window for your application during the process and stop the workload to prevent loosing updates on your database. Do the export/import to/from databases with a high service tier to boost the operation. Use a VM in the same region to save latency time. If your database is/was used for Data Sync Service, consider removing DSS object before exporting the database. Check: https://techcommunity.microsoft.com/t5/azure-database-support-blog/exporting-a-database-that-is-was-used-as-sql-data-sync-metadata/ba-p/369062 If your database is a part of Geo-DR replication, consider removing the Geo link and delete secondary database before starting the operation in order to create a new Geo replication and sync the new database with the new collation to the secondary server.
