Description

This article describes how to change the collation for the existing Azure SQL database.

For example, if you upgrade the Sitecore XP instance deployed in Azure App Service, you must change the collation for it from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CS_AS before upgrading your xDB Reference Data database.

Note: It is not possible to change a collation for Azure SQL database by simple script execution.

Solution

To change the collation for the existing Azure SQL database, consider the following method provided by Microsoft:

1. To export the Azure SQL Database into the data-tier application (BACPAC) file:
   
   • Connect to the Azure SQL instance, right-click on the Azure SQL database and click, Tasks, Export Data-tier application.
     
   • Go through the wizard, select a target path and name for the BACPAC file, and finalize the wizard.
   When you have successfully exported the Azure SQL database to the BACPAC file, you must next modify model.xml inside your BACPAC file.


2. To modify model.xml inside your BACPAC file:
   • The latest versions of SqlPackage.exe utility that support the /ModelFilePath parameter are available to import the BACPAC file with overriding. Download and install the latest SqlPackage.exe version from here.
   • The Sqlpackage utility is installed to the C:\Program Files\Microsoft SQL Server\150\DAC\bin directory.
   • Open the .bacpac file using winzip or 7-zip.
   • Copy the model.xml to a local folder C:\Temp\model.xml.
   • Edit the "C:\Temp\model.xml" with the "Latin1_General_CS_A collation" as follows:

     From <Property Name="Collation" Value="SQL_Latin1_General_CP1_CI_AS" />
     To <Property Name="Collation" Value="Latin1_General_CS_AS" />

     and save the changes.


3. To deploy your BACPAC back to Azure SQL with overridden model.xml:
   
   • Run the import using "sqlpackage.exe", but use the "/ModelFilePath:C:\Temp\model.xml" parameter to override the model.xml in the .bacpac. Also, make the target database name different from the name of the already existing database.
     
     For example:

     sqlpackage.exe /Action:Import /tsn:<Your DB Server Name>.database.windows.net /tdn:<Target DB Name> /tu:<SQL Admin User Name> /tp:<SQL Admin Password> /sf:"<Path to your BACPAC file>" /ModelFilePath: C:\Temp\model.xml

     Note: During deployment, you might get a warning in the console with the following message:

     *** Overriding model.xml using file 'C:\Temp\model.xml'.  Use of this setting may result in deployment failure and/or unintended data loss.  This setting is intended only for use when troubleshooting issues with publish, import or script generation.

     This is a common warning for deployment with model overriding, it does not mean that something went wrong with your customization.
     
     After you have got the Successfully imported database message, you can delete the old database with the old collation and rename the new one to the initial database name. Now you have only one database with the correct collation.