SQL Server 2008R2 database migration to cloud: case-insensitive collation changed to case-sensitive 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"?


Database was detached / attached, or backed-up / restored
Objects and data were moved via scripting out and applying at the destination, or directly ETLed
Data Migration Assistant
SSDT and BACPAC file
Azure Database Migration Service

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


SQL Server database migration to Azure SQL Database
Migrate your SQL Server database to Azure SQL Database using DMA
SQL Server instance migration to Azure SQL Database Managed Instance