Change collation of existing Azure SQL database


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-collation?view=azuresqldb-current)

 

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

1 - Export the database to .bacpac file

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/

 

Comments (0)

Skip to main content