6

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.

8

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 :-)

  • 2
    Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks! – Tony Hinkle Mar 1 at 20:51
  • 2
    @TonyHinkle please see my updated notes (bottom half of the answer) – Solomon Rutzky Mar 1 at 21:01
  • 2
    --what collation do you dream in? :) – Tony Hinkle Mar 1 at 21:50
  • 3
    Solomon dreams in every collation simultaneously. – Max Vernon Mar 1 at 21:58
  • 1
    +1 As someone who manages three large JIRA instances, i can tell you it is the app-side that complains about violations.The main concern is making sure it uses case insensitive which without may break some of the OP’s plugins. Also, @TonyHinkle, the instance can be whatever it wants, JIRA is like all other apps that use a user defined to a database. It won’t use the Instance collation. – clifton_h Mar 2 at 1:55
4

This is too long for a comment but I want to call out the potential for data loss in char/varchar columns when changing the code page. Data may be mapped to a different character than the source code page or a '?' when no character exists at all. Importantly, no errors will be reported during the migration if data loss occurs.

This script shows the mapping for non-ASCII characters going from the 1252 to 437 code page:

WITH 
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 127 AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,CP1252_Characters AS (
        SELECT
              num AS CodePoint
            , CHAR(num) COLLATE SQL_Latin1_General_CP1_CI_AS AS CP1252_Character
        FROM t1k
        WHERE num < 256
    )
SELECT
      CP1252_Characters.CodePoint AS CP1252_CodePoint
    , CP1252_Characters.CP1252_Character
    , CP1252_Characters.CP1252_Character COLLATE SQL_Latin1_General_CP437_CI_AI AS Converted_CP457Character
FROM CP1252_Characters;

Note the absence of the Euro symbol in the 437 code page, for example. I hope you didn't have any Jira tickets containing this currency symbol :-)

+------------------+------------------+--------------------------+
| CP1252_CodePoint | CP1252_Character | Converted_CP457Character |
+------------------+------------------+--------------------------+
|              128 | €                | ?                        |
|              129 |                 | ?                        |
|              130 | ‚                | ,                        |
|              131 | ƒ                | ƒ                        |
|              132 | „                | ,                        |
|              133 | …                | .                        |
|              134 | †                | +                        |
|              135 | ‡                | ╪                        |
|              136 | ˆ                | ^                        |
|              137 | ‰                | %                        |
|              138 | Š                | S                        |
|              139 | ‹                | <                        |
|              140 | Π               | O                        |
|              141 |                 | ?                        |
|              142 | Ž                | Z                        |
|              143 |                 | ?                        |
|              144 |                 | ?                        |
|              145 | ‘                | `                        |
|              146 | ’                | '                        |
|              147 | “                | "                        |
|              148 | ”                | "                        |
|              149 | •                |                         |
|              150 | –                | -                        |
|              151 | —                | -                        |
|              152 | ˜                | ~                        |
|              153 | ™                | T                        |
|              154 | š                | s                        |
|              155 | ›                | >                        |
|              156 | œ                | o                        |
|              157 |                 | ?                        |
|              158 | ž                | z                        |
|              159 | Ÿ                | Y                        |
|              160 |                  |                          |
|              161 | ¡                | ¡                        |
|              162 | ¢                | ¢                        |
|              163 | £                | £                        |
|              164 | ¤                |                         |
|              165 | ¥                | ¥                        |
|              166 | ¦                | ▌                        |
|              167 | §                |                         |
|              168 | ¨                | "                        |
|              169 | ©                | c                        |
|              170 | ª                | ª                        |
|              171 | «                | «                        |
|              172 | ¬                | ¬                        |
|              173 | ­                | -                        |
|              174 | ®                | r                        |
|              175 | ¯                | _                        |
|              176 | °                | °                        |
|              177 | ±                | ±                        |
|              178 | ²                | ²                        |
|              179 | ³                | 3                        |
|              180 | ´                | '                        |
|              181 | µ                | µ                        |
|              182 | ¶                |                         |
|              183 | ·                | ·                        |
|              184 | ¸                | ,                        |
|              185 | ¹                | 1                        |
|              186 | º                | º                        |
|              187 | »                | »                        |
|              188 | ¼                | ¼                        |
|              189 | ½                | ½                        |
|              190 | ¾                | _                        |
|              191 | ¿                | ¿                        |
|              192 | À                | A                        |
|              193 | Á                | A                        |
|              194 | Â                | A                        |
|              195 | Ã                | A                        |
|              196 | Ä                | Ä                        |
|              197 | Å                | Å                        |
|              198 | Æ                | Æ                        |
|              199 | Ç                | Ç                        |
|              200 | È                | E                        |
|              201 | É                | É                        |
|              202 | Ê                | E                        |
|              203 | Ë                | E                        |
|              204 | Ì                | I                        |
|              205 | Í                | I                        |
|              206 | Î                | I                        |
|              207 | Ï                | I                        |
|              208 | Ð                | D                        |
|              209 | Ñ                | Ñ                        |
|              210 | Ò                | O                        |
|              211 | Ó                | O                        |
|              212 | Ô                | O                        |
|              213 | Õ                | O                        |
|              214 | Ö                | Ö                        |
|              215 | ×                | x                        |
|              216 | Ø                | O                        |
|              217 | Ù                | U                        |
|              218 | Ú                | U                        |
|              219 | Û                | U                        |
|              220 | Ü                | Ü                        |
|              221 | Ý                | Y                        |
|              222 | Þ                | _                        |
|              223 | ß                | ß                        |
|              224 | à                | à                        |
|              225 | á                | á                        |
|              226 | â                | â                        |
|              227 | ã                | a                        |
|              228 | ä                | ä                        |
|              229 | å                | å                        |
|              230 | æ                | æ                        |
|              231 | ç                | ç                        |
|              232 | è                | è                        |
|              233 | é                | é                        |
|              234 | ê                | ê                        |
|              235 | ë                | ë                        |
|              236 | ì                | ì                        |
|              237 | í                | í                        |
|              238 | î                | î                        |
|              239 | ï                | ï                        |
|              240 | ð                | d                        |
|              241 | ñ                | ñ                        |
|              242 | ò                | ò                        |
|              243 | ó                | ó                        |
|              244 | ô                | ô                        |
|              245 | õ                | o                        |
|              246 | ö                | ö                        |
|              247 | ÷                | ÷                        |
|              248 | ø                | o                        |
|              249 | ù                | ù                        |
|              250 | ú                | ú                        |
|              251 | û                | û                        |
|              252 | ü                | ü                        |
|              253 | ý                | y                        |
|              254 | þ                | _                        |
|              255 | ÿ                | ÿ                        |
+------------------+------------------+--------------------------+
  • OMG! Just think of all the euros we would lose! :) Thanks for this--very helpful. – Tony Hinkle Mar 2 at 13:23
  • @TonyHinkle and Dan: yes, this is definitely worth highlighting (and is covered in detail in Item \#2 under "General Concepts" in Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?, which is also linked in my answer). HOWEVER, with regards to Jira, it is unlikely to be an issue since the Atlassian-created Jira tables use only NVARCHAR and NTEXT 😿, so tickets are safe. – Solomon Rutzky Mar 4 at 14:43
  • @TonyHinkle and Dan: to be fair, there are two 3rd-party packages with tables in the Jira DB: "JQuartz" (scheduling) and something with all of its tables being prefixed with "AO_XX_name" where the "X"s are 6 hex digits. I think it's for sprint boards as some table names are: AOHIP_CHAT_USER, BOARDADMINS, CARDLAYOUT, ESTIMATESTATISTIC, SPRINTMARKER, SWIMLANE, etc. Those two packages contain both NVARCHAR / NTEXT ("AO" has some NVARCHAR(MAX)) and VARCHAR columns. But jiraissue, project, projectcategory, propertytext, etc tables use NTEXT for "Description" columns. – Solomon Rutzky Mar 4 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.