Another article I wrote to Azure SQL DB Support blog
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 articlehttps://blogs.msdn.microsoft.com/azuresqldbsupport/2017/08/16/editing-a-bacpac-file/
Sergio Fonseca Blog 