Change collation of existing Azure SQL database


Another article I wrote to Azure SQL DB Support blog

https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/11/09/change-collation-of-existing-azure-sql-database/

There is no easy way at this moment to change an existing database collation. The workaround is to modify manually the BACPAC file

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)

But to change an existing DB, check the information 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 sure 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

*Until this moment (2019-03-27) this parameter is not a documented parameter at
https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-2017#import-parameters-and-properties


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

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/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s