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?
There is a lot of information missing from the question, such as:
- 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
- What is meant by "migration"?
- What is meant by "database"?
- An actual Database that resides in an Instance of SQL Server
- An Instance of SQL Server that contains Databases
- 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:
- Partially-Contained Databases use:
- SQL Server Express Local DB uses: