Rajendra Gupta
Check existing collation

How to change an Azure SQL Database Collation

August 24, 2021 by

This article explores the Azure SQL Database Collation configuration and how to change it while importing the database from a BACPAC file.

Introduction

The collation refers to a configuration that determines database engine behavior for character data set at a server, database, or, column level. SQL Server has a wide range of collations for handling language differences in different parts of the world. It provides sorting rules, case sensitivity for Unicode data types (nchar, nvarchar, or ntext) and non-Unicode (char, varchar, nvarchar).

SQL Server supports the following collations.

  • Windows
  • Binary: The binary collation always ends with either _BIN or _BIN2
  • SQL Server: These collation names always start with SQL_

For a traditional (on-premises) SQL Server, the operating system locale determines the default collation. However, you can modify the server-level collation during SQL Server installation or modifying the OS locale. The default SQL Server collation is SQL_Latin1_General_CP1_CI_AS. You can understand these collations in detail by referring to Collation and Unicode support.

Requirements

This article presumes that you have a basic understanding of Azure SQL Databases. If you are a beginner, you can refer to existing articles SQL Azure on SQLShack. You can utilize Microsoft Azure free credit by sign up at URL https://azure.microsoft.com/en-us/free/

Azure SQL Database Collation

Azure SQL DB is a managed PaaS offering for SQL Server with certain restrictions and limitations for performing specific tasks. Like on-premises SQL Server, Azure DB also supports various collations. To get a list of supported collations for Azure SQL DB, we can query the sys.fn_helpcollations() function. It returns a list of 5,508 collations. The below figure gives you a glimpse of the collation and descriptions.

Supported Collation list

For Azure SQL DB, you can choose database collation in the additional settings tab of creating SQL Database. As shown below, the default collation appears as SQL_Latin1_General_CP1_CI_AS.

We can break down this collation for understanding it.

  • SQL – It refers that it is a SQL Server collation
  • Latin1_General – This part defines the character sort rule
  • CI – It refers to case insensitive
  • AS – It defines accent sensitivity

Specify collation while creating database

You can click on find a collation and look for the specific collation you wish to configure for Azure DB.

You can deploy an Azure database on an existing Azure logical server. Therefore, if you create a new Azure database using SSMS, you can define a specific collation using the options page.

Check collation in SSMS

If you create an Azure SQL DB using T-SQL, you can use the COLLATE keyword and specify the required DB collation.

You must create the database in the correct collation. Your SQL queries might not work correctly due to collation mismatch. However, if you need to modify after creating the Azure SQL Database, it is not straightforward. Azure SQL DB does not support changing collation using the ALTER DATABASE COLLATE. You get the following error message if you try to change it.

Modify collation using COLLATE function

  • Note: Do not modify or set a specific collation until you have specific requirements. Never do it directly on a production database. Test it thoroughly on a lower DB environment and then plan to proceed for production collation

Suppose you are deploying an Azure SQL Database from the on-premises BACPAC file. You want a different collation in the target database.

  • Note: I assume that you have an active Azure SQL DB. If not, you can use a free tier account for deploying your test database
  • For this article demo, we will do the following tasks:

    • Export the Azure SQL database in BACPAC format
    • Install Microsoft SQL Server Data-Tier Application Framework (18.1)
    • Modify model.xml file configuration
    • Use sqlpackage.exe to deploy the Azure database with updated collation
    • Verify the database collation

    Step 1: Export the Azure SQL database in BACPAC format

    In this step, we export Azure SQL DB in the BACPAC format. We can verify that the existing collation is SQL_Latin1_General_CP1_CI_AS.

    Check existing collation

    For export, right-click on the Azure database and choose Export Data-tier Application…

    Export the Azure SQL database in BACPAC format

    Specify the directory in the save to the local disk.

    Save to local disk

    Verify, export and monitor progress for data-tier application in BACPAC format.

    View status

    Step 2: Download Microsoft SQL Server Data-Tier Application Framework (18.1)

    You need to download and install the latest version of SSDT. Navigate to URL https://www.microsoft.com/en-us/download/details.aspx?id=57784 and click on Download.

    Download Microsoft SQL Server Data-Tier Application Framework

    Select the required MSI file version and download it. It is a lightweight package file.

    Choose MSI

    Follow the installation wizard for setting up the Microsoft SQL Server Data-tier application framework.

    Installation progress

    Installation progress message

    Installation finished

    Step 3: Rename the. BACPAC file to .ZIP

    In this step, we rename the exported BACPAC package file into a ZIP format. To do so, right-click on the imported BACPAC and change the extension to ZIP. The file type is compressed (zipped) after changing the extension, as shown in the below figure.

    Rename the .BACPAC file to .ZIP

    Please right-click on the compressed file and extract it. In the extracted folder, you find a model.xml, copy the model.xml to another directory such as the C:\Temp folder.

    Model,xml file

    Open the model.xml from C:\Temp\Model.xml and modify the DB collation. The following figure shows old and new collations for Azure SQL Database. Here we use SQL collation with case sensitivity.

    Modify collation in model.xml

    Step 4: Deploy Azure Database using sqlpackage.exe

    Here, navigate to directory C:\Program Files\Microsoft SQL Server\150\DAC\bin in command prompt. We use sqlpackage.exe to import the BACPAC with the /ModelFilePath parameter. It overrides the BACPAC model file configuration with the specific file in this parameter.

    Required parameters:

    • /tsn: Specify the Azure SQL Server FQDN in which we want to import this BACPAC file. In my case, it is azuredemosqldemo.database.windows.net
    • /tdn: It is the new Azure SQL Database name. You cannot import BACPAC on an existing database
    • /tu: Specify the admin username to connect with Azure SQL Server and deploy the database
    • /tp: Enter the password for the admin user
    • /sf: Specify the BACPAC file location
    • /ModelFilePath: Enter the updated model.XML file path

    Earlier, we changed the extension of the BACPAC file to ZIP. We need to change it back to BACPAC for running the sqlpackage.exe command.

    To modify the collation in the BACPAC, execute the following command in a command prompt.

    sqlpackage.exe /Action:Import /tsn:azuredemosqldemo.database.windows.net /tdn:azuredemodatabasenew /tu:sqladmin /tp:******* /sf:C:\Temp\azuredemodatabase.BACPAC /ModelFilePath:C:\Temp\model.xml

    You get a warning 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 intends to use only when troubleshooting issues with publish, import or script generation”

    It starts data import, process tables and deploys database on it. The database deployment time depends on the number of tables, data size, indexes.

    deploy database using sqlpackage.exe

    In the end, you get the message – Successfully Imported database.

    successful imported database

    Step 5: Verification

    Now, connect to Azure Database using SSMS and verify the database collation using the following script.

    As shown below, the newly deployed database uses collation SQL_Latin1_General_CP1_CS_AS.

    Verify the collation in deployed Azure SQL Database

    Now, login to the Azure portal and view the database properties. This database has a general-purpose Gen5, 2vcores pricing tier. We did not specify any pricing tier while deploying the database. Therefore, the Azure SQL database takes the default pricing tier.

    View pricing tiers

    Step 6: Deploy Azure database in a specific pricing tier

    Suppose we want to deploy a database with modified collation and a basic pricing tier. For this demo, we change the DB collation to Latin1_General_BIN. Therefore, open the model.xml and change the collation as highlighted before.

    Change DB collation to Latin1_General_BIN.

    This time we specify a new parameter(/p) for setting the database edition. It instructs Azure to deploy the database under the specific pricing tier.

    sqlpackage.exe /Action:Import /tsn:azuredemosqldemo.database.windows.net /tdn:azuredemodatabasenew /tu:sqladmin /tp:******* /p:DatabaseEdition=Basic /sf:C:\Temp\azuredemodatabase.BACPAC /ModelFilePath:C:\Temp\model.xml

    Deploy package

    Once the DB is deployed, verify the database collation. As shown below, it uses Latin1_General_BIN collation.

    Verify DB collation

    You can connect to the Azure portal and verify the pricing tier as Basic.

    Verify pricing tier

    • Note: You should not modify DB collation without any specific requirements. It might cause trouble if you change it arbitrarily

    Conclusion

    This article explored default Azure SQL Database collation and the process to change it while importing DB from a BACPAC file. You can specify your desired collation and deploy the database. However, never change the collation unless you have a specific requirement. You can try the steps on your own test Azure SQL database.

    Rajendra Gupta
Azure, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

435 Views