Administracja MS SQL
Lublin

Zakres

Administracja bazami danych: cztery obszary, uprawnienia i monitoring

Administrujemy bazami Microsoft SQL Server. Zakres układa się w cztery obszary: indeksy, statystyki, plan konserwacji i kopie zapasowe, a obok nich stoją prawa dostępu do bazy oraz zgłoszenia o tym, co się z nią dzieje.

Każdy z sześciu kafli ma własny podrozdział, a kolejność w obu miejscach jest ta sama.

• Indeksy według stopnia fragmentacji
• Trafne plany zapytań
• Konserwacja w oknie serwisowym
• Rytm kopii i kopia poza serwerem
• Prawa dostępu do bazy
• Miejsce, kolejki, długie zapytania

Indeksy w SQL Server: przebudowa i reorganizacja według stopnia fragmentacji

Lista dokumentów, która kiedyś otwierała się od razu, po kilku latach potrafi ładować się wyraźnie dłużej. Sprawdzamy wtedy stopień fragmentacji indeksów, bo od niego zależy, które z nich warto ruszać, i nie przebudowujemy wszystkiego hurtem.

Indeks to uporządkowana struktura, po której silnik znajduje wiersze bez czytania całej tabeli. Po wielu zmianach kolejność logiczna stron indeksu rozjeżdża się z ich kolejnością fizyczną na dysku, a na stronach zostaje więcej wolnego miejsca, więc ten sam odczyt kosztuje serwer więcej pracy. Reorganizacja układa istniejące strony, a przebudowa indeksów SQL Server tworzy strukturę od nowa. Takie prace wchodzą w oknie serwisowym, najczęściej nocnym, a jeśli miałyby zatrzymać program, termin ustalamy z firmą z wyprzedzeniem. Określenie defragmentacja bazy danych MS SQL jest nieścisłe, bo porządkowanie dotyczy indeksów.

Statystyki i plan wykonania zapytania: dlaczego silnik wybiera wolniejszą drogę

Plan wykonania zapytania SQL to droga, którą silnik wybiera, żeby pobrać dane. Aktualizacja statystyk SQL Server jest u nas stałym zadaniem właśnie dlatego, że utrzymuje plan wykonania w zgodzie z zawartością tabel.

Silnik planuje zapytanie, zanim je wykona, i opiera ten plan na statystykach o rozkładzie wartości w kolumnach. Gdy statystyki są nieaktualne, szacowana liczba wierszy rozjeżdża się z rzeczywistą, a plan wybiera droższą metodę, na przykład odczyt całej tabeli zamiast wyszukania po indeksie.

Skutek bywa rozciągnięty w czasie. Gotowe plany są przechowywane i używane ponownie przy kolejnych wywołaniach tego samego zapytania, więc plan ułożony na nieaktualnych statystykach może obciążać serwer długo po tym, jak powstał. Aktualizacja statystyk unieważnia plany oparte na starych wartościach, a kolejne wywołanie zapytania układa plan od nowa.

Plan konserwacji bazy dopasowany do okien serwisowych firmy

O godzinach decyduje firma: kiedy baza może przyjąć zadania, które ją obciążą. Konserwację układamy pod okna serwisowe firmy, bo system przy większości tych prac nie musi być zatrzymany.

Plan konserwacji to zestaw czynności: kopie, aktualizacja statystyk, porządek w indeksach i sprawdzenie spójności bazy. Zadania harmonogramu dzielą ten plan na części, a każde z nich ma własne okno uruchomienia i własną historię wykonań. Mechanizm nosi angielską nazwę maintenance plan SQL Server.

Osobno traktujemy shrink bazy danych SQL. Kurczenie plików zwalnia miejsce na dysku, ale nie zmniejsza bazy na stałe, a przy okazji przesuwa strony i podnosi fragmentację indeksów, więc po nim indeksy wymagają uporządkowania. Trwałe odchudzanie, czyli przeniesienie starych dokumentów do archiwum, prowadzimy jako osobne zadanie, oddane odchudzaniu bazy danych w Lublinie poza bieżącą opieką nad bazą.

Kopie pełne, różnicowe i kopia dziennika transakcji w ustalonym rytmie

Rytm kopii wynika z dwóch ustaleń: ile danych firma może stracić w najgorszym przypadku i jak długo produkcja może stać. Z tych dwóch odpowiedzi wychodzi, czy wystarczą kopie pełne i różnicowe, czy potrzebne są też kopie dziennika transakcji, możliwe wyłącznie w modelu odzyskiwania, który dziennik zachowuje.

Kopia pełna zapisuje całą bazę, różnicowa zmiany od ostatniej pełnej, a kopia dziennika transakcji zapis operacji od poprzedniej kopii dziennika. Różnicowa odnosi się do ostatniej pełnej, nie do poprzedniej różnicowej, i tym różni się od przyrostowej.

Silnik zapisuje każdą zmianę w dzienniku transakcji przed zatwierdzeniem, więc przerwana operacja nie zostawia bazy w połowie drogi. W modelu odzyskiwania, który zachowuje dziennik, miejsce w pliku zwalnia się dopiero po zabezpieczeniu zawartości kopią dziennika, i to częsta przyczyna nagle kończącego się miejsca na dysku. Kopia zapasowa bazy danych powstaje według harmonogramu: zadanie uruchamia ją w ustalonych godzinach, a plik odkładamy poza serwerem produkcyjnym.

Mówimy tu o kopiach samej bazy. Poza tym zakresem zostają kopie plików i całych systemów firmy, czyli temat kopii zapasowych w firmie obejmujący całą infrastrukturę.

Uprawnienia do bazy, dostęp do serwera z internetu i szyfrowanie kopii

Prawa można nadać roli albo pojedynczemu użytkownikowi, a porządek zaczynamy od roli. Konto, którym program łączy się z bazą, często ma prawa administratora instancji, choć do pracy zwykle potrzebne są prawa w obrębie jednej bazy, a ich dokładny zakres określa dostawca systemu. Uprawnienia do bazy danych SQL układamy według zasady najmniejszych potrzebnych praw.

Instancja nasłuchuje na porcie TCP, a dla instancji domyślnej jest to port 1433. Serwer bazy odpowiadający na całym internecie bywa odpytywany przez automaty, dlatego ograniczamy taki dostęp do adresów programu. Plik kopii trzymamy poza serwerem produkcyjnym, żeby nie dzielił losu bazy przy awarii albo ataku typu ransomware, a szyfrowanie kopii zapasowej sprawia, że w cudzych rękach plik jest bezużyteczny.

Monitoring bazy: wolne miejsce, kolejki i długie zapytania

Zgłoszenia obejmują kończące się miejsce, rosnący czas stałych operacji, kolejki i błędy w dzienniku zdarzeń. Ustalamy, kto dostaje powiadomienie o każdym z tych zdarzeń.

Pomiar wyprzedza poprawki: sprawdzamy, które zapytania obciążają procesor i dysk. Wolne zapytania SQL Server widać w takim pomiarze razem z liczbą wykonań, więc optymalizacja bazy danych MS SQL ma punkt wyjścia w liczbach.

Użytkownik widzi czasem zawieszony program, choć serwer pracuje. Dzieje się tak, gdy dwie sesje chcą zmienić te same wiersze i jedna czeka na zwolnienie blokady. Zakleszczenie silnik rozwiązuje sam, przerywając jedną z sesji.

Wersje

SQL Server Express a wariant płatny: limity, które decydują o wyborze

SQL Server Express bywa instalowany razem z programem magazynowym przy wdrożeniu i pracuje potem latami. Pytanie, czy SQL Server Express wystarczy, wraca zwykle wtedy, gdy baza rośnie i zbliża się do limitu.

Limit rozmiaru bazy i wykorzystania pamięci w wariancie bezpłatnym

Wariant bezpłatny ma limit rozmiaru pojedynczej bazy i limit wykorzystywanej pamięci. Po osiągnięciu limitu rozmiaru silnik odmawia zapisu nowych danych, więc problem wychodzi w trakcie pracy, przy wystawianiu dokumentu. Wartości limitów różnią się między wydaniami, dlatego sprawdzamy je dla wersji z danej firmy.

Druga różnica bywa ważniejsza: w tym wariancie brakuje usługi harmonogramu zadań, więc kopie i konserwację uruchamia zadanie systemu operacyjnego albo skrypt. Z takim układem spotykamy się przy MS SQL w małej firmie i przy jednej bazie na jednym serwerze.

Wersje SQL Server i wsparcie producenta: dlaczego wydanie silnika ma znaczenie

Wersję i wariant silnika odczytuje się z właściwości serwera albo z zapytania o wersję, więc pytanie, jak sprawdzić wersję SQL Server, zamyka się w jednym kroku. Każde wydanie ma okres, w którym producent publikuje dla niego poprawki; po tym okresie poprawki już nie powstają.

Aktualizacja SQL Server a zgodność z systemem magazynowym

Zanim migracja silnika trafi na produkcję, testujemy zgodność nowszej wersji z systemem ERP. Baza przeniesiona na nowsze wydanie zachowuje swój poziom zgodności, o ile nowe wydanie ten poziom jeszcze obsługuje; poziom niższy od najniższego obsługiwanego zostaje podniesiony. Kolejność ustala więc program: najpierw jego wymagania, potem wersja silnika. Przy migracji SQL Server wymagania podaje producent programu, na przykład w programach InsERT w Lublinie i w innych systemach magazynowych.