To change SQL Server 2014 Default Instance Collation

Chandra Mohan Kanithi
Chandra Mohan Kanithi used Ask the Experts™
on
Hi,

I need to change SQL Server 2014 Default Instance Collation and find the Server details.

User Databases : 15
Size of Databases : Below 10 GB  - 10 Databases
                                : 10 GB to 15 GB - 2 Databases
                                : Remaining Three are 36 Gb, 71 GB and 100 GB

Note: Transaction Log files drive have 135 GB free space out of 200 GB.

I have successfully applied following solution on Test Server.

1.      Stop SQL Server Services.
2.      Open a Command Prompt and Navigate to the Binn Directory
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn
 3.      Apply a New SQL Server Collation
sqlservr -m -T4022 -T3659 -s"MSSQLSERVER" -q"SQL_Latin1_General_CP1250_CI_AS"
4.      Start SQL Services and verify collation

Please provide precautions regarding log space during collation change (indexes are restoring during this activity) and more pre-requisites.

Thanks,
Chandra
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
Please keep in mind that by just changing the SQL Server Default collation your Database Tables having any "text" like columns may be still in old collation and you will need to change them all to the desired collation by running ALTER TABLE...ALTER COLUMN.
Regarding the Database T-Log size/growth/free disk space...what is/are the database Recovery options? FULL, SIMPLE
If you commit after each change upon completion in SIMPLE recovery mode you should have enough free disk space to make the changes but in FULL or BULK-LOGGED you may need to take some T-Log backups in between changes in order to recycle and reuse T-Log space.
David ToddSenior Database Administrator
Commented:
Hi,

Two ways to change the collation on a database or instance.

As Icohan mentioned above it involves changing each and every text field in the database in question - ie create a new database with the correct collation and schema with correct collation and then transfer all the data as necessary.

Or one I got shown - if the change is merely between different English collations - is to change the collation from the command line.
https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation/

I've done this on one set of servers (dev/test/prod) and all seem okay a year later.

HTH
  David
Chandra Mohan KanithiSenior Principal Consultant - Database

Author

Commented:
Thanks for prompt response.

Regards,
Chandra

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial