SQL: Bezbolesna zmiana collation serwera SQL po instalacji

10-sie-2016

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/

Komentarze:

  1. Mariusz napisał,

    Wykonałem zmianę Collation poleceniem z q na serwerze MS SQL 2017 Express i zadziałało. Wszystko się podniosło. Loginy pozostały nienaruszone.

  2. Tomasz napisał,

    Microsoft SQL Server 2019 – polecenie nie działa.
    Error: 3434, Severity: 20, State: 1.
    spid9s Cannot change sort order or locale. An unexpected failure occurred while trying to reindex the server to a new collation. SQL Server is shutting down.

  3. Jakub napisał,

    Potwierdzam, że na serwerze SQL 2016 sposób trzeci przeszedł gładko i bez problemów.

Autor: Rafał Kraik