Changing SQL collation for deployed instance without reinstall (almost)

IMPORTANT UPDATE: If you use K2 4.6.11 or newer you have to have SQL_Latin1_General_CP1_CI_AS collation on a SQL Server instance which hosts K2 DB (more details)

Collation matters. Period. If you not sure just wait till you get an incident or issue which will teach you that it is. Some apps require you to have specific collation for their back end DB. For example for K2 you need to have  Latin1_General_CI_AS collation (see my earlier post on that _ ).

Nonetheless it can often be the case that your RTFM/Prerequisites_Check moment (I wonder if I can copyright/trademark this term? I heard that Michael Buffer trademarked his “let’s get ready to rumble” phrase _ ) happens after SQL server instance has been already provisioned. For newly created (clean install) K2 database you just need to delete database which was created on instance with wrong collation and allow K2 Setup Manager to create it again after you deal with your instance collation. There is an MSDN article detailing that _ but I will outline the procedure below.

Aforementioned MSDN article says that you have to have installation media to perform this operation, but in fact you can get away with Setup Bootstrap folder which should present on your server in location similar to one below (may vary from version to version, see details _ )

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

So steps are the following:

0. In case you have any non system databases attached to this instance take a note of database(s) file location, frequently it is just default SQL instance data folder common to all databases. Save this path or paths to notepad. 

1. Locate setup bootstrap folder for your SQL server installation, open CMD or PS window (don’t forget “dot net sourcing” thing for the latter):

# SQL 2012
cd "C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012"
# SQL 2014
cd "C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014"
# SQL 2016:
cd "C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016"
# SQL 2017:
cd "C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017"

2. Execute the following command:

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Domain\User /SAPWD=P@ssw0rd /SQLCOLLATION=Latin1_General_CI_AS

Once this operation completes  master, model, msdb, and tempdb system databases are rebuilt (dropped and re-created) with collation you specified in the command. Obviously all your modifications to these databases are lost (if you made any) and essentially it is sort of reinstall/repair type of operation just much quicker and without need to wade through SQL Setup GUI.

3. In case there were non system databases attached to this SQL instance (see step 0) they all are detached now. See my other blog post explaining how to attach all the databases from specific folder

Please follow and like us:
0

2 thoughts on “Changing SQL collation for deployed instance without reinstall (almost)

  1. Matthew Vorster

    A word of caution when running the setup to rebuild the system databases to change the collation, this drops all user databases. Be sure to create a backup of any non system databases first.

    Reply
    1. Mikhail Post author

      Are you 100% sure, I believe it should just un-mount them, i.e. you need to go and mound DB files after this… And if you have a lot of them you can do it using script – https://mikerodionov.com/2016/05/sql-server-how-to-attach-all-databases-from-specific-folder/
      As per MSFT docs /ACTION=REBUILDDATABASE just rebuilds system database – “Specifies that Setup re-create the system databases”, in case it supposed to drop user databases MSFT documentation would have big warning about that which I don’t see, for example, here: https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *