Change Collation on an Installed Instance

Change Collation on an Installed Instance

Everyone has encountered the problem, when you have already installed a SQL Server with userDB already Deployed and  oops, somebody noticed that databases from inherit environment had a different Collation and more important,

there is no time to re-install the DBMS…. what can we do?

if we don't have other choice, there is still another way..

Well the following process is not documented by Microsoft, but it is possible to perform and works in newer versions. 

 Change the collation by setting instance in single user mode

Steps Overview:

Take FULL Backup for all  SQL Server instances and databases in the environment. Make sure you script out your login and security information, in case you need to recreate SQL Logins, indexes, constraints, fk, computed columns, statistics. (You never know….)

Identify where your binaries are…

Stop Database services on Cluster  SQL “Instance” targeted for your change .

Start DBMS in Service mode  (Single User mode) with start options: Option:      q: Change the collation to targeted trace flags: T4022:           T3659:

Perform changing catalog, and recreating indexes. (DBMS will perform this step)

Stop DBMS from Single User mode

Start DBMS services.

if your Environment is a Standalone Instance

Identify Where your binaries are: There are numbered ways to do this, for this exercise you can go to SQL Serverconfiguration Manager Identify your SQL Serviceand go to Service tab and path….

Stop  SQLServices

 

NOTE: make sure your Full Text Search feature is stopped, due this service utilizes the index objects, which will be recreated…

now that you know your binn path, you go command prompt with Admin. Privileges or in this casePowerShell.

go to binaries path and start sql server binary as follows:

sqlservr.exe -m-T4022 -T3659 -s"<INSTANCENAME>" -q"<CollationName>"

NOTE: One other way to start single user mode is using the net start <MSSQLSERVER> command,

you just have to besure if there is more than one sql server binaries running in the same server as in a cluster environment it has to be named the Service name byMSSQL$<Instance name> or the "Display Name" of the service

for more details onhow to start and stop services through net command:

https://technet.microsoft.com/en-us/library/dd421654.aspx

Then you will see all system dbs indexes objects restored….

Once all it is completed successfully you can stop service in single user mode

NET STOP <Service name>

Now that change is done Start service normally and connect to SQL and query sys.databases to check collations.

If your Environment is Clustered:

1. You have to go to the Failover Cluster Manager and identify the instance that you wish to do the change.
2. Connect to the Owner node,Stop the SQL service.
3. go to SQL Configuration manager stop the Full Text Search services and all related to the instance.
4. go to the Binn path that Service indicates you to… or use net start command.

if you use binn command:

if you use net start:

5. after the change stop services and start from FCM service.

NOTE: Always check the SQL Errorlog to verify all messages, in order to fix any error you encountered for user db, remember dbms Will do server principal objects until the end so server collation Will be reflected sr the end. You Will see SQL server error log accepted the change until the end.

And you are done.