In this article I will explain how to change the collation for your production Azure SQL Databases without loosing data/updates on the database with a minimum downtime.
Steps in brief:
- Take a copy of the production database to a higher service tier ex: P11 on the same server.
- Export the database to bacpac using SQLPackage. (It’s better to use a VM in the same region of the SQL server for less latency)
- Modify the collation by editing the model.xml file.
- Import the database again to a higher service tier ex: P11 by using SQLPackage and overriding the model.xml path.
- Change the database name, or modify app connection string to use the new database.
Things to consider:
- 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 higher 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-...
- 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.
Steps in details:
1. Make sure you have a modern version of SQLPackage.
- Latest SQLPackage version is here .
- Sqlpackage installs to the C:\Program Files\Microsoft SQL Server\150\DAC\bin directory.
2. Start the maintenance window for your application.
3. Make a copy of the production database to a higher service tier ex: P11.
How to copy SQL databases: https://docs.microsoft.com/en-us/azure/azure-sql/database/database-copy?tabs=azure-powershell#copy-u...
4. Export the copied database using SQLPackage from a VM on the same region of your SQL server.
- Open CMD > Navigate to SQLPackage location > ex: “C:\Program Files\Microsoft SQL Server\150\DAC\bin” .
- Run the below command to export the database:
sqlpackage.exe /Action:Export /ssn:tcp:<ServerName>.database.windows.net,1433 /sdn:<DatabaseName> /su:<UserName> /sp:<Password> /tf:<TargetFile> /p:Storage=File
5. Change the database collation in the bacpac model.xml file:
- Open the .bacpac file using WinRAR without de-compress the file.
- 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:
From: <Property Name="Collation" Value="SQL_Latin1_General_CP1_CI_AS" />
To: <Property Name="Collation" Value="Hebrew_CI_AS" />
6. Run the import using sqlpackage.exe, and use the /ModelFilePath:C:\Temp\model.xml parameter to override the model.xml in the .bacpac.
For example:
sqlpackage.exe /Action:Import /tsn:<server>.database.windows.net /tdn:<database> /tu:<user> /tp:<password> /sf:"C:\Temp\database.bacpac" /ModelFilePath:C:\Temp\model.xml /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P11
7. When the import operation completed, Change the database name, or modify the application connection string to use the new database.
8. Stop the maintenance window for your application and run workload.
9. Delete the copied and old databases.