As a SQL Server user, you may come across situations where you need to change the collation of your SQL Server instance. This could be due to various reasons, such as compatibility issues or specific language requirements. In this blog post, we will discuss how to change the collation of a SQL Server instance on Microsoft Azure’s SQL Virtual Machine (IaaS).
By default, when you deploy a SQL Server on Azure VM using images from the gallery, it is installed with the SQL_Latin1_General_CP1_CI_AS collation. However, if you need to change the collation, you cannot do it during the provisioning process. Instead, you will have to rebuild the system databases after the VM is provisioned.
Before we proceed, it is important to note that rebuilding the system databases is similar to performing a fresh installation of SQL Server. This means that all existing information will be lost. Therefore, it is crucial to backup any important data before proceeding with the collation change.
To rebuild the system databases, you can use the command prompt. Unfortunately, you cannot specify individual system databases (master, model, msdb) to be rebuilt. Instead, you will need to use the SQL Server installation media or the cached folder on the Azure VM.
If you are using the installation media, you can find the necessary files in the “Setup Bootstrap” folder located within the installation logs. The path for SQL Server 2014 server is typically: C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014
. However, on Azure VM, the SQL installation media is located on the C drive.
Once you have located the installation media or the cached folder, you can use the following command to rebuild the system databases:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /SAPWD=A@$tr0ngP@$$w0rd /SQLCOLLATION=Japanese_Bushu_Kakusu_100_CS_AI
Make sure to replace the parameter values based on your specific requirements. For example, you can change the /SQLCOLLATION
parameter to the desired collation you want to use.
It is worth mentioning that you don’t necessarily need the installation media to rebuild the system databases. The command will take the necessary files from the Template folder and redeploy SQL Server with the desired instance.
Remember, changing the collation of a SQL Server instance is a critical operation that should be performed with caution. It is always recommended to test the process in a non-production environment before applying it to a live system.
We hope this blog post has provided you with the necessary information to change the collation of a SQL Server instance on Azure Virtual Machine. If you have any questions or need further assistance, feel free to leave a comment below.