This Should Be Fun
Imagine an environment where you have a production SQL 2005 cluster (2 nodes, 2 instances) and what you think is an identical test/dev 2005 cluster (2 nodes, also 2 instances). Now imagine you come to a conclusion 3 years after you’ve installed, configured, and utilized the four instances that one of the test/dev nodes is a different, albeit slight, collation than the others.
Yes, I’m refering to the collation builder issue that was new in SQL Server 2005 that allowed you to dynamically build your collation, picking and chosing language, accent sentivity, and case sensitivity when installing SQL Server. It appears that I used that functionality for the first instance I created (the primary dev/test one). Therefore selecting Latin General 1, case insensitive, and accent sensitive. This would make sense, it matches the primary characteristics of the *default* collation for SQL Server in the United States. However, the remaining test/dev instance as well as the two production instances are simply that default collation: Latin_General1_CP1_CI_AS.
Three years. Many databases migrating back and forth between all four instances. This should be a joy. Glad I’m always up for a challenge. Perhaps I’ll even take the opportunity to institute Disk Partition Allignment and snowball this entire change through the 200 (yes 200) databases hosted on those four instances.
If anyone has suggestions on simplifying some of the processes I’m going to have to employ I’m all ears. Otherwise look for me to document my processes and (hopefully) successes here.
Remember folks. Make sure you’ve the collations matching between instances you tend to migrate databases between. You can run databases in different collations from those of the system databases, but there are idiosyncrasies that develop in these situations occasionally. Be mindful that even if the language, case, and accent sensitivity match – if the collation is not the same specific collation then it’s not the same.