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