Change collation of existing Azure SQL database

[Azure-DB-Support-Team]

First published on MSDN on Nov 09, 2018
When you are creating a new database you can change the collation in the portal



Or else using TSQL ( https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-database-... )



However there is no easy way at this moment to change an existing database collation. The workaround is to use the BACPAC that can be used like the sample below



UPDATE 2019-02-06



Just got an information from the product group that there is an easier way to do this

We added a /ModelFilePath command line parameter to sqlpackage to override the model.xml. This should simplify the process steps to avoid the re-compute the hash in the origin.xml, and re-compressing the .bacpac which results in corruption for large files.

So the steps are:

1. Make you have a modern version of sqlpackage that support the /ModelFilePath parameter (we added it about a year ago).

• Latest sqlpackage version is here .


• Sqlpackage installs to the C:\Program Files\Microsoft SQL Server\150\DAC\bin directory.

2. Open the .bacpac file using winzip or 7-zip



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



4. Edit the “C:\Temp\model.xml” with the desired collation. For example:

From: <Property Name="Collation" Value="SQL_Latin1_General_CP1_CI_AS" />
To: <Property Name="Collation" Value="Latin1_General_BIN" />
5. Run the import using sqlpackage.exe, but 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




Below you can find the old way, from the original post



1 - Export the database to .bacpac file

• https://docs.microsoft.com/en-us/azure/sql-database/sql-database-export

2 - Rename it to .ZIP

3 - Unzip in a folder, you are going to see files like below



4 - Alter the file model.XML , with the requested default collation for the database
From: <Property Name="Collation" Value=" SQL_Latin1_General_CP1_CI_AS " />
To: <Property Name="Collation" Value=" Latin1_General_BIN " />
5 - You can also check also fields that have non default collations
<Element Type="SqlSimpleColumn" Name="[dbo].[Table_1].[anothercollation]">
<Property Name="Collation" Value=" SQL_Latin1_General_CP1_CS_AS " />
6 - Alter the file origin.xml

• You are going to find a checksum like the below

<Checksums>
<Checksum Uri="/model.xml">35EF75437D2087E0FB029E5D726B229B973CFB0EFE75D014E74214C468976D51</Checksum>
</Checksums>

• You need to recalculate it using a Powershell script. Make sure to change the file path

$modelXmlPath = " C:\FILEPATH\model.xml "
$hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")
$fileStream = new-object System.IO.FileStream -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)
$hash = $hasher.ComputeHash($fileStream)
$hashString = ""
Foreach ($b in $hash) { $hashString += $b.ToString("X2") }
$fileStream.Close()
$hashString

• Change the file origin.xml with the new hash and save it

7 - Zip the files again

• Make sure not to zip the folder where you unzipped the bacpac, only the files inside it

8 - Rename the zip to DBNAME. bacpac

9 - Do the import

10 - You can check the collation in the fields with this query
SELECT T.name, C.name, C.collation_name
FROM sys.tables T
INNER JOIN sys.columns C
ON T.object_id = C.object_id
WHERE C.collation_name IS NOT NULL




I used as a reference the information from this other article
https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/08/16/editing-a-bacpac-file/