locked
Change Collation After setting up Always On Availability Group RRS feed

  • Question

  • Hi

    I have a problem and I don't know what is the best solution.

    I have set up Always On Availability Group with 2 nodes on SQL Server 2017. Default server collation of the previous sql server (2005) was "SQL_Latin1_General_CP1256_CI_AS". I used "Arabic_CI_AS" as server collation which results in conflict between user databases' collation and tempDB collation.

    I don't know if it's better to:

    1- Change the server collation to "SQL_Latin1_General_CP1256_CI_AS"

    or

    2- Change user database collation to "Arabic_CI_AS"

    I red some instructions to change server collation on Always On AG and it seems i have to remove Listener and AG (Which i prefer not to). But I couldn't find any instruction to change the database collation in Always On AG.

    I Appreciate your help as it's the production server and we have some problems in our software due to his conflict.

    Monday, September 3, 2018 1:34 PM

All replies

  • So you want to use old collation SQL_latin1_general_CP1256_CI_AS correct, in that case it would be better to change server collation. You need to rebuild the instance again with new collation

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, September 3, 2018 3:01 PM
  • Hi Shashank

    Thanks for your reply.

    Is that instruction work for me as I have Always On Availability Group?

    Monday, September 3, 2018 5:50 PM
  • Hi Shashank

    Thanks for your reply.

    Is that instruction work for me as I have Always On Availability Group?

    This blog has more details about how to change collation of instance participating in AG. But wait before you proceed which collation you want to use ? It seems like if you change server collation you would have to remove AG and reconfigure after instance is rebuild which you clearly dont want.

    So as of now the server collation is Arabic one and database is SQL_Latin1_General_CP1256_CI_AS, now which one you want to use. ofcourse changing database collation is easy and that will match that of server.  But please note changing database collation can have issues later.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, September 3, 2018 6:06 PM
  • I prefer to do it in a way that I have less issues and needs less support time. I think it's changing the server collation to SQL_Latin1_General_CP1256_CI_AS. This way we have less changes in software code.


    Also I red that blog, but I thought there may be easier way that doesn't require Always On removing and reconfiguration.

    Monday, September 3, 2018 6:43 PM
  • Personally, I prefer a Windows collation over an SQL collation. However, changing the database collation is utterly painful. If you run ALTER DATABASE COLLATE, that only changes the collation of the system tables, so that does not resolve the problems with the temp tables. The rest you need to do yourself, and it's not fun as indexes has to be dropped and recreated etc.

    So in the end, staying with the SQL collation is the only reasonable choice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, September 3, 2018 9:30 PM
  • I prefer to do it in a way that I have less issues and needs less support time. I think it's changing the server collation to SQL_Latin1_General_CP1256_CI_AS. This way we have less changes in software code.

    It seems the older SQL instance (2005) had a different server collation(Latin) than the new AG setup (which's Arabic). Which collation do you want for your SQL instances? I would check for application compatibility and other things as it changes the behavior of case sensitivity besides other things. I would not go with one just because it's easy to implement. If you have a valid reason for going with Arabic then there's no harm in changing the DBs' collation to match server's collation (Arabic). Remember, in AG both replicas have to be the same collation.

    But, if you prefer to keep DBs as default (Latin) and server as Arabic then know that tempdb uses server collation (Arabic) and if you create temp tables etc. then they will use tempdb's collation and your cross joins reference etc will fail. An exception to this would be if you use contained databases wherein they use the DB's collation. 

    All in all, I'd agree with Erland and stay with SQL collation (On a side note, Erland authored a section on collation in Kalen's SQL Server 2012 internals book wherein he talks about collation in greater detail. You might also want to read it for some better understanding).


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, September 3, 2018 11:25 PM
  • I prefer to do it in a way that I have less issues and needs less support time. I think it's changing the server collation to SQL_Latin1_General_CP1256_CI_AS. This way we have less changes in software code.


    Also I red that blog, but I thought there may be easier way that doesn't require Always On removing and reconfiguration.

    Yes changing server collation to match that of database should has less repercussions or I shall say should be safe, again this decision of keeping what collation should come from you if you can live with database and server having different collation then it is ok.

    As i see when you rebuild database everything is rebuilt and back to original form so I guess it is better to remove AG and then rebuild it. Plus you have to take complete backup of system and user database before proceeding. You do not need to take individual backup of jobs and logins and backup of master and msdb would do it for you. 

    Please note AG and listener details before removing them. Plus you might as well need to reinitialize secondary.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, September 4, 2018 3:59 AM
  • But, if you prefer to keep DBs as default (Latin) and server as Arabic then know that tempdb uses server collation (Arabic) and if you create temp tables etc. then they will use tempdb's collation and your cross joins reference etc will fail. An exception to this would be if you use contained databases wherein they use the DB's collation. 

    Moshin, as you might guess SQL_Latin1_General_CP1256_CI_AS is an Arabic collation. The naming of the SQL collations could be, well, better.

    Tuesday, September 4, 2018 9:38 PM