I have the following scenario: 2 database servers SQL Server 2008 R2 on Windows server 2012 R2, Cluster 2 nodes (with no-shared storage), with different Server Collation settings, planning to implement an AG after upgrade SQL Server to 2014 SP2.
Will that different Server Collation settings have an impact on implementing the AG? Or will it affect the SQL Failover operation if required to be done?
Note: this structure is still in the testing phase.
Best Answer
Based on Sean's comment on the question, it sounds like there is a requirement that the Server / Instance -level Collations be the same. But, even if you could technically set up the Availability Group with differing Server default Collations, you wouldn't want to do that as you could easily experience unpredictable / inconsistent behavior when there is a fail over, such as sorting and/or comparison differences.
You could also have code breaking. For example, if the primary system is case insensitive and you have casing differences in variable names, that code will break if moving to an instance having either a case sensitive or binary Collation. This is due to Server-level Collation affecting variable names (including cursor variable names).