How to change an Azure SQL Database Collation
We created an Azure SQL database using the default collation that was available. Now, there is a requirement from the client to change the collation. Is it possible to change the collation of an Azure SQL database after it is created?
This tip assumes you already have an Azure SQL database. If you are new to Azure, try this link: https://azure.microsoft.com/en-us/free/ which will give you $200 USD credit for a month. Otherwise, logon to your Azure portal: https://portal.azure.com.
As per Microsoft, it is not really possible to change the collation of an Azure SQL database once it is created. You can refer to this Microsoft link where it mentions that “You cannot change the collation for an Azure SQL Database after it is created”. However, in this tip, we will perform a demo with the following steps:
- Export the Azure SQL database to an on-premises server
- Change the database collation on the on-premises server
- Export the on-premises database
- Finally import into the Azure server with the new collation
In the next tip, we will explore how to hack the BACPAC file after exporting the Azure SQL database and re-importing the BACPAC file with the new collation.
Azure SQL Database collation
Connect to the Azure SQL database, right click on the database to view the properties and collation.
Here, the Azure SQL database collation is: SQL_Latin1_General_CP1_CI_AS as shown. Now, let us try to change the collation of the database to Latin1_General_100_CS_AS_SC.
Run this code on the Azure SQL database to change the collation.
USE master; GO ALTER DATABASE SQLTips COLLATE Latin1_General_100_CS_AS_SC
This will generate an error message as shown.
The error says - “The database could not be exclusively locked to perform the operation”. Sometimes, you may have a different error message, but no matter what the error is, you will not be able to change the collation of an Azure SQL database this way.
Export Azure SQL Database to a BACPAC
In order to restore the Azure database on to an on-premises server, export the Azure database to a BACPAC. Connect to the Azure SQL instance, right click on the Azure SQL database and click on the option to “Export Data-tier application”.
Once you click on “Export Data-tier Application”, you will get this “Introduction” screen where the wizard describes in brief the process to follow. Click on “Next”.
In the next screen, you will get the option to specify the location of the BACPAC file. Provide a file name for the BACPAC file and click on “Next”.
In the next window, you will see the summary section.
Review the details on the screen and click on “Finish”. Once you click on “Finish”, the process of extracting the schema and data begins and this process takes some time to complete based on the size and contents of your Azure SQL database.
Once the process completes, you will see the final confirmation of the process completion as shown.
With this, you have successfully exported the Azure SQL database to a BACPAC.
Import BACPAC file to an on-premises SQL Server instance
Connect to your on-premises SQL Server instance and start the process to import the BACPAC file. Connect to your SQL instance, right click on the “Databases” option to “Import Data-tier Application”.
You will see this “Introduction” screen, click on “Next”.
Click on “Next” to provide the location of BACPAC file. This is the same file you exported from the Azure SQL database.
Once you provide the actual BACPAC file location, click on “Next”. In the following window, provide the location for the data and log files to be restored on-premises.
Once you click on “Next”, you will see the “Summary” section to review. Click on “Finish” to start the import process.
Once you click on “Finish”, the process of Importing the database begins.
Once the process completes, you will get confirmation of completion as shown. Click on “Close”, once you see this screen.
Database restored on on-premises SQL Instance and change collation of the on-premises database
Once the import process completes, you will notice that a new database has been created on your on-premises SQL instance.
Before you change the collation of this database, check the current collation of the “SQLTips” database on the on-premises instance.
The collation - “SQL_Latin1_General_CP1_CI_AS” is the same collation as that of the Azure SQL database. Connect to the on-premises SQL instance and run the command to change the database collation. Run this code on the on-premises SQL database to change the collation.
USE master; GO ALTER DATABASE SQLTips COLLATE Latin1_General_100_CS_AS_SC
You will notice that the command completed successfully.
You can check the collation of the on-premises SQLTips database to confirm that the collation has changed.
This confirms that the collation of the on-premises database is changed to Latin1_General_100_CS_AS_SC
Export on-premises database to BACPAC
Now that you have the new collation for your on-premises SQL database, perform the step to export the data-tier application of the on-premises SQL database. Right click on the database, go to “Tasks”, and click as shown.
Just follow the sequence of steps in the wizard similar to what you had performed for exporting the Azure SQL database. The only difference now is you are exporting the on-premises SQL database with the new collation. The process completes in some time similar to the time taken for the exporting the Azure SQL database.
Import the BACPAC from on-premises database to the Azure SQL instance which hosts the Azure SQL database
With the BACPAC file exported from on-premises SQL instance, connect to the Azure SQL instance to import the BACPAC file.
You will get the wizard, similar to what you had seen while importing the BACPAC to on-premises SQL instance earlier on. Just follow the sequence in the wizard to complete the import process. Ensure to select the correct BACPAC file while performing the import as shown.
Select the correct BACPAC file and click on “Next”, which will take you to this screen.
You will notice that a new database name gets auto populated. We wouldn’t be able to provide same name as that of the Azure SQL DB – “SQLTips” as it already exists. If you try renaming the database as “SQLTips”, you will get this error message.
You can either leave the auto populated database name as the default or provide a name of your choice and click on “Next” to start the process of importing the BACPAC on to the Azure SQL instance. Once the import process completes, the new database would be available to use as shown.
With this, we have the desired collation for the Azure SQL database. You may delete the “SQLTips” database with the old collation and rename the new “SQLTips_on_premises” database as “SQLTips” if you wish to retain only one database with the correct collation. With this workaround, we were able to change the collation of an Azure SQL database even though it is not possible to directly make the collation change after the Azure SQL database is created. In the next tip, we will try to hack the BACPAC file after it is exported from the Azure SQL database and make the collation update directly on the BACPAC file and import it back to the Azure SQL instance.
- In this tip, you were able to apply a work around to change the collation of an Azure SQL database after it was created.
- Try this tip on your own test Azure SQL database.
- Try this tip using your own on-premises SQL Server instance.
- To get familiar with Azure, refer the numerous tips available at this link.
About the author
View all my tips
Article Last Updated: 2018-07-17