2

Recently felt the fall out (crappy legacy code base) of a database migration by a third party vendor where the collation was swapped from case-insensitive to case-sensitive. I am wondering if the collation was deliberately changed during the migration? I would have thought that in any database migration the collation would remain unchanged (i.e. stay the same as the source database collation). Does the server which the database is hosted automatically override the collation maybe?

Improve this question
2
  • Who did te migration? Which process was used? I would ask who migrated the database or the vendor.
    – spaghettidba
    Commented Jul 11, 2018 at 8:37
  • 1
    One thing is that - the cloud might have set a case sensitive collation as default. You might have create a db (shell) and then just migrated the data using bcp out and bcp in. This way you ended up from case insensitive to case sensitive collation.
    – Kin Shah
    Commented Jul 11, 2018 at 16:46

1 Answer 1

Reset to default
0

There is a lot of information missing from the question, such as:

  1. What does "cloud" mean, exactly?
    • Microsoft Azure SQL Database
    • Microsoft Azure SQL Database Managed Instance
    • Microsoft Azure VM with SQL Server installed
    • Amazon (AWS) RDS
    • Amazon (AWS) VM with SQL Server installed?
    • something else
  2. What is meant by "migration"?
  3. What is meant by "database"?
    • An actual Database that resides in an Instance of SQL Server
    • An Instance of SQL Server that contains Databases
  4. How does "crappy legacy code base" factor into this Collation issue? Or is it just venting / kvetching?

Generally speaking, there is no obvious / default answer since there is no single mechanism / approach for migrating data, nor is there a singular definition of "cloud" in this context. I would expect that some options (such as restoring from a .bak backup file) would keep the Database's default Collation intact, but that does not imply anything about the Instance-level Collation.

What this comes down to is: even without having more detailed information to go on, since there is a Collation difference at some level (Instance and/or Database), it would seem that the vendor that performed the migration was not careful to ensure that either a) the exact same Collation was being used if given a choice to set a new Collation, or b) that you were notified that there would be a change of Collation at the specific level(s) if there was no choice and it was going to be different than the current Collation(s).

I would definitely contact the vendor regarding this issue. Typically, if there is a default Collation that cannot be changed, it is only the Instance-level, and is case-insensitive:

  • Azure SQL Database Managed Instances use: SQL_Latin1_General_CP1_CI_AS (unfortunately)
  • Partially-Contained Databases use: Latin1_General_100_CI_AS_WS_KS_SC
  • SQL Server Express Local DB uses: SQL_Latin1_General_CP1_CI_AS (unfortunately)

References:

Improve this answer
4
  • Thanks for the in-depth reply. The 'migrator' is a third party in the process. This issue caused a waste of 2 man days. No feedback from the vendor. The intermediaries answer to the issue was "all fixed now" problem gone away etc. I would have thought an organisation that provides DBA services would not have made this fundamental blunder.
    – Gary Thomann
    Commented Jul 11, 2018 at 21:53
  • 1
    You are welcome. You might want to get clarification from the service provider as to how they fixed it, or what exactly was "fixed". Collation is a terribly misunderstood area, and it is possible that they consider it "fixed" because 1 thing that was obviously wrong is now correct, but not knowing of other areas that might have been affected and possibly not "fixed" due to not being known about.
    – Solomon Rutzky
    Commented Jul 11, 2018 at 21:56
  • 1
    I agree with point 4 in that I am venting a bit. Programs fell over due to fk joins where the data one side is in caps and the other lowercase. It is real data-frisbee territory with lots of obfuscated design and code. But thats life.
    – Gary Thomann
    Commented Jul 11, 2018 at 22:00
  • 1
    @GaryThomann I hear ya. That is definitely frustrating. Foreign Keys on string columns is dubious in the first place. If it must be done, then forcing the Collation of those columns to be binary (i.e. ending with _BIN2) provides better performance and removes the chances of such failures.
    – Solomon Rutzky
    Commented Jul 11, 2018 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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