Skip to main content

How to change an Azure SQL Database Collation

How to change an Azure SQL Database Collation

Problem
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?
Solution
Technically collation change for SQL Azure DB is very much complicated process. usually it is not recommended to do so. But as a work around, you can follow these steps.
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.
  • 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.
Example the Azure SQL database collation is: SQL_Latin1_General_CP1_CI_AS.
Let us try to change the collation of the database to Latin1_General_100_CS_AS_SC.
1. You will need to export the database.

Once the process completes, you will see the final confirmation of the process completion.

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”.

Once you click on “Finish”, the process of Importing the database begins.
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 DatabaeName
COLLATE Latin1_General_100_CS_AS_SC







You can check the collation of the on-premises database to confirm that the collation has changed.



Now, again you need to export the BACPAC file and then import on Azure. 





















Labels:





























Comments











Post a Comment























Popular posts from this blog



















Troubleshooting transient connection errors to Azure SQL Database




























  Troubleshooting transient connection errors to Azure SQL Database      Dear friends, I'm Yogesh. At my work place, I have faced these type of issues at times.    Let us understand what is this transient error?    A transient error has an underlying cause that soon resolves itself. It causes occasionally is when the Azure system quickly shifts hardware resources to better load-balance various workloads. Most of these reconfiguration events finish in less than 60 seconds.    During this reconfiguration time span, you might have connectivity issues to SQL Database.  To handle them, implement retry logic in their code instead of surfacing them to users as application errors.   If your client program uses ADO.NET, your program is told about the transient error by the throw of  SqlException .   What is the solution? Can Retry logic works?   When your program communicates with SQL Database through third-party middleware, ask the vendor whether the middle ware contains retry logic for tr




















Post a Comment









Read more






























Geo-Replication in SQL Azure Database




























   Geo-Replication in SQL Azure Database     Geo-Replication  is one of the Azure SQL features which allows making 3 readable replicas to your database in same or different data centers.   Geo-Replication option is available for all databases and service tiers in all region. If it is enabled, the application initiates to a secondary database.    we will review how to set up Geo-Replication on Azure SQL databases. Geo-Replication is an Azure SQL database feature that allows you to create a readable secondary database in the same region or cross-region. We can failover to the secondary database in case of an outage for a long time on the primary database server. We can also use this feature to migrate a database from one server to another server in the same or cross region with minimal downtime. Geo-replication uses the Always-on feature to replicate committed transactions to the secondary database asynchronously.       Select the database, Click on 'Geo Replication' in left hand




















Post a Comment









Read more




























DTU and eDTU in Azure SQL




























   DTU and eDTU in Azure SQL   The performance of SQL Database is based on DTU.   According to Microsoft, a DTU is a unit of measure of the resources that are guaranteed to be available to a single Azure SQL Database at a specific performance level within a single database tier. A DTU combines CPU, memory, data I/O, and transaction I/O.   Databases can be placed into an  elastic pool  on a SQL Database server that shares a pool of      resources among those databases. The shared pool of resources are measured      by  elastic Database      transition units (eDTU) .  The advantages of an elastic pool are:   They are scaled automatically.  They provide predictable       costs.   They are widely used for  varying  and  unpredictable      usage patterns .    Determining DTUs for Workloads   ·          If you are planning to move your on-premises Databases to Azure SQL Database, Azure provides a  DTU Calculator  to find the approximate DTUs required for setting up the database.   ·         




















1 comment









Read more