2

How do I change a DB Collation without removing the DB from high availability group? Can I just take it offline and change it? or I really do need to take it out of high availability group 1st then set to single user then alter then reset it back to multiuser and back to high availability? will it cause an issue with the partner database?

Sorry I forgot the specs. I accidentally choose a case sensitive collation type. and I want to change it to Case insensitive type. but this DB is on the high availability always on group.

Thank you

  • You cannot really change a DB collation in place, just the DEFAULT COLLATION (IE Collation which new text columns are created as). Could you provide a bit more context - why does the collation need changing? What from/to etc? – George.Palacios Mar 13 '18 at 16:35
  • Are you talking about Replication or Always On Availability Groups? If the DB is offline then you cannot make changes to it. You also don't need to be in single user mode in order to change the Collation, BUT there are several restrictions that might prevent it from completing, such as having schema-bound objects that reference any string columns, possibly having check constraints on string columns, and other stuff like that. Also, collation of existing columns of User Tables won't change, just string columns of system Tables / Views and the default for new columns not specifying COLLATE. – Solomon Rutzky Mar 13 '18 at 17:21
  • 1
    @SolomonRutzky we fixed it. the issue was the collation was case sensitive type and i switched it to insensitive by taking it out of the high availability group, then change it. The error I was getting was "I cannot Exclusively locked the db". – CyberNinja Mar 13 '18 at 17:43
  • OK. Then you can post your own answer to this and accept it tomorrow I think. You should update the question to include that error message. Error messages are always appreciated :-) – Solomon Rutzky Mar 13 '18 at 17:48
0

I have to remove the DB from High Availability Group(Always on), change the DB to single user, change the collation then change it back to multiuser,back to the high availability group. The good thing is this database is new and it only have 5 tables otherwise it will be more complicated. I was just hoping I don't have to remove it from the group.

  • Just curious: did you need to put it in SINGLE_USER mode? I know that you can change the DB Collation without doing that. Or did it just make things easier? I wonder if removing it from the group was enough. Also, sometimes there are lingering processes still associated with the DB (e.g. connection pooled connections that haven't timed out, etc). Also, if you still have the exact error message, can you please put that in the question? it will help when others search for it. thanks :) – Solomon Rutzky Mar 15 '18 at 13:59
  • yes, i have to put in single_user mode because it's throwing "The database could not be exclusively locked to perform the operation". I just followed other DBA suggestions because for one I'm not a DBA. I tried changing it without switching usermode but no can do. – CyberNinja Mar 15 '18 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.