I’ve been doing a lot of work lately in Microsoft Azure. When you spin up a new VM using the SQL server template the database collation is
SQL_Latin1_General_CP1_CI_AS. The primary system I deploy into these databases requires that the database be
Latin1_General_CI_AI. While there are other ways to accomplish this change below is what I have found to be the quickest and most consistent way to update the collation.
** Note: This is not recommended on databases already holding data, but it should work the same.
- On the DB server open SQL Server Configuration Manager
- Stop the SQL Server Services that are running
- Open an elevated Command Prompt and navigate to
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn
sqlservr -m -T4022 -T3659 -q "Latin1_General_CI_AI"replacing
Latin1_General_CI_AIwith your required collation. The flag
-T4022will bypass database startup and
-T3659will write errors to the logs.
- When the Command Prompt shows a success message, close the cmd window
- Start services back up in SQL Server Configuration Manager
After completing these steps the database will be the expected/required collation.