SQL: Bezbolesna zmiana collation serwera SQL po instalacji

Zazwyczaj wskutek złej komunikacji może okazać się, że serwer został zainstalowany z niepoprawnym collation. Generalnie każde collation jest dobre, ale problem zaczyna się wtedy, gdy na serwerze pojawia się baza danych ze swoim specyficznym collation i niektóre zapytania lub procedury kończą się błędem „collation conflict”.

Problem można próbować rozwiązywać na wiele sposobów.

Zdecydowanie najlepszy – tak zmienić kod zapytań, aby podczas odwoływania do obiektów serwerowych, tworzenia obiektów tymczasowych itp. Korzystać z collation bazy danych. Wystarczy w tym celu zazwyczaj dodać instrukcję COLLATE DATABASE_DEFAULT. Niestety jest to edycja kodu aplikacji, do którego nie zawsze masz dostęp.

Zmiana collation bazy danych, dość skomplikowany proces, bo wymaga właściwie utworzenia bazy danych na nowo ze wszystkimi obiektami korzystającymi z collation takiego jak na serwerze. Obiekty jak tabele, indeksy, procedury, funkcje trzeba zeskryptować, odtworzyć obiekty i zaimportować dane na nowo. Bardzo skomplikowane, długotrwałe i łatwo o pomyłkę, a na dodatek nie zawsze aplikacja pozwala na pracę z innym collation, więc nie zawsze ta metoda powinna być w ogóle brana pod uwagę.

Zmiana collation na serwerze. To trochę ostateczność, bo zmiana może zepsuć z kolei inne bazy danych zainstalowane na serwerze. Na dodatek zmiana collation wiąże się z przebudowaniem baz systemowych, innymi słowy utracone zostaną informacje o bazach danych, loginach, opcjach serwera, endpointach itp. Nic więc dziwnego, że i ta opcja nie jest mile widziana przez administratorów. Proces przebudowy systemowych baz danych polega na uruchomieniu komendy:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName  /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]  /SQLCOLLATION=CollationName 

i jest opisany na stronach MS:  https://msdn.microsoft.com/en-us/library/ms179254.aspx

Okazuje się, że jest też nieoficjalny sposób zmiany collation na serwerze z wykorzystaniem opcji q

1.       Oczywiście sprawdź aktualne collation baz systemowych, użytkownika i collation serwera oraz sporządź wszystkie możliwe backupy, bo jak by nie było za chwilę wykonasz czynności, po których potencjalnie serwer może przestać działać i będą kłopoty

2.       Odłącz od serwera te bazy danych, których collation nie ma być zmienione. (Omawiana procedura zmienia collation dla serwera i wszystkich w tej chwili połączonych baz danych).

3.       Zatrzymaj SQL server (np. korzystając z SQL Configuration Managera)

4.       Otwórz command prompt i przejdź do  katalogu instalacyjnego SQL np.:
C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXP1014\MSSQL\BINN
i uruchom polecenie:
sqlservr -m -T4022 -T3659 -s”SQLEXP2014″ -q”SQL_Latin1_General_CP1_CI_AI”

Oto znaczenie opcji:
-m oznacza tryb „minimum configuration” wykorzystywany do prac administracyjnych, gdy nie można zagwarantować dostępności nawet baz systemowych
-T4022 trace flag powoduje nie uruchamianie procedur startowych. Flaga może być używana podczas rozwiązywania problemów z uruchomieniem SQL serwera powodowanych błędami w procedurach startowych
-T3659 trace flag powodujący logowanie wszelkich komunikatów do error log, co może być przydatne jeżeli z czasem chcesz zidentyfikować potencjalne problemy, które pojawiły się w czasie zmieniania collation

-s – określa nazwę instancji SQL, której collation będziesz zmieniać, jeśli zmiana ma być wykonana na instancji domyślnej można ten parametr opuścić
-q – to właśnie owa cudowna opcja, która zmieni collation na wskazaną w cudzysłowach

5. Poczekaj na zakończenie przebudowy baz. Po zakończeniu Naciśnij CTRL+C, co spowoduje zatrzymanie SQL Servera uruchomionego w trybie „minimal configuration”

6. Uruchom SQL w normalny sposób i sprawdź collation. Jeśli odtłaczałeś bazy danych to teraz podłącz je ponownie.

Sprawdzone na SQL 2012 i 2014. Czekam na okazję przetestowania na innych wersjach. W moim przypadku uszkodzony był tylko moduł CLR znajdujący się w bazie danych. Usunięcie modułu i jego ponowne załadowanie rozwiązało problem.

Przy okazji – najbardziej kompletna lista trace flag, jaką udało mi się znaleźć:

http://www.sqlservice.se/updated-microsoft-sql-server-trace-flag-list/