SQL SERVER – How to Change SQL Server Collation on Microsoft Azure VM (IaaS)

I have been using Microsoft Azure, SQL Virtual Machine (IaaS) for quite some time because few of my clients ask questions about them. My clients generally use images from gallery to deploy SQL Server. Once they realized that they want to change the SQL Server collation instance. I was contacted to know the steps to do that. I learned something new so sharing via the blog.

Caution: Rebuild of system databases is as good as the fresh installation of SQL Server. YOU WOULD LOSE ALL EXISTING INFORMATION so please backup the stuff, if needed.

Creating VM from gallery doesn’t provide the option to choose SQL collation. By default, they are installed with SQL_Latin1_General_CP1_CI_AS collation. If collation change is needed, we need to do that as the first thing after provisioning VM. This is to make sure the collation matches with your planned use.

As you may know that the changing collation of SQL Server instance needs REBUILD of system databases. System Databases should be rebuilt from the command prompt. We CANNOT specify one system databases (master, model, msdb) to be rebuilt. We can use either use

  • Installation media
  • From the folder “Setup Bootstrap” located with installation logs.

%programfiles%\Microsoft SQL Server\<Build>\Setup Bootstrap\<SQLVersionfolder>

For SQL Server 2014 server, it is

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014

On Azure VM the SQL installation media is located on C Drive. My VM is SQL Server 2016.

SQL SERVER - How to Change SQL Server Collation on Microsoft Azure VM (IaaS) vm-coll-01

And there is the command

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=”BUILTIN\Administrators” /SAPWD= A@$tr0ngP@$$w0rd /SQLCOLLATION=Japanese_Bushu_Kakusu_100_CS_AI

You need to replace parameter values based on your requirement. I have run that from cached folder not media and that also worked.

SQL SERVER - How to Change SQL Server Collation on Microsoft Azure VM (IaaS) vm-coll-02

In short, you don’t need to have a media for rebuilding the system database. It takes the files from Template folder and re-deploy SQL Server with desired instance.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Azure, SQL Collation, SQL Server
Previous Post
SQL SERVER – FIX: Could not connect because the maximum number of 1 user connections has already been reached
Next Post
SQL SERVER – What is DBCC TRACEON and DBCC TRACEOFF messages in ERRORLOG?

Related Posts

3 Comments. Leave new

  • Chetan Koriya
    April 5, 2016 12:41 pm

    Hello Pinal sir,

    I am fetching problem when restoring database using script, there i found that issue is with logical file name.

    I created database with name TEST_1 and restoring database using bak file name of TEST_0.bak file,

    It is bak of database TEST_0.

    First i restored it with manual process in database TEST_1 it was successfully restored,but TEST_1 logical file name changed with TEST_0 and TEST_0_log.

    Second I have bak file of database TEST_2 (TEST_2.bak), i try to restore in database TEST_1 with script,

    here logical file name is different it’s show me below error

    Msg 3234, Level 16, State 2, Line 3
    Logical file ‘TEST_2’ is not part of database ‘TEST_2’. Use RESTORE FILELISTONLY to list the logical file names.
    Msg 3013, Level 16, State 1, Line 3
    RESTORE DATABASE is terminating abnormally.

    Reply
  • Thank you once again, Pinal!
    You’re the best!

    Reply
  • Thank you – Worked first time against a new VM instance

    Reply

Leave a Reply