Sql-server – Process to change collation on a database

collationdbatoolssql serversql server 2014

I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.

So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly quickly. So I thought about just scripting out all objects, find/replace all references to the old collation, create the new database, and then use dbatools to copy the data:

Get-DbaDbTable -SqlInstance SourceServer -Database SourceDb |
 foreach-object 
    { Copy-DbaDbTableData -DestinationDatabase TargetDb -DestinationTable $_.Name }

Is this a valid approach to changing collation on everything? At first blush it seems to have worked out rather swimmingly (i.e., got through the migration of data with no errors).

Going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP437_CI_AI if that matters.

Best Answer

Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation

Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.

Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:

Collation error in Jira when the database and server/default collation do not match in SQL Server

Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:

The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.

whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement ;-).

My advice: don't change anything! You even said:

a database that has been in production for a few years

If it has been in production for years without running into problems, then there are likely no problems :-)

Related Question