Tworzenie planu konserwacji programu SQL Server
Typowe działania, które można zaplanować dla regularnej konserwacji programu SQL Server, obejmują:
- Kopie zapasowe dziennika bazy danych i transakcji
- Sprawdzanie spójności bazy danych
- Konserwacja indeksu
- Aktualizacje statystyk
Ważne jest, aby zrozumieć znaczenie kopii zapasowych, a także konserwację indeksów i statystyk dla wszystkich baz danych. Kontrole spójności bazy danych, znane również jako CHECKDB (przy użyciu polecenia DBCC CHECKDB), są równie ważne, ponieważ są jedynym sposobem sprawdzania całej bazy danych pod kątem uszkodzenia. W zależności od rozmiaru baz danych i wymagań dotyczących czasu pracy możesz wykonywać wszystkie te działania w nocy. Jednak w systemach produkcyjnych operacje konserwacji są często rozłożone w ciągu tygodnia, ponieważ zarówno konserwacja indeksu, jak i kontrole spójności są bardzo intensywne pod względem operacji we/wy i zwykle wykonywane w godzinach weekendowych.
Wielu administratorów baz danych (DBA) dzieli harmonogram tworzenia kopii zapasowych dużych baz danych, wykonując jedną pełną kopię zapasową tygodniowo oraz używając różnicowych i logowych kopii zapasowych do zarządzania odzyskiwaniem danych do określonego punktu w czasie. Program SQL Server oferuje wbudowany sposób zarządzania wszystkimi tymi zadaniami przy użyciu planów konserwacji. Plany konserwacji tworzą przepływ pracy zadań do obsługi baz danych i są tworzone jako pakiety usług Integration Services, co umożliwia zaplanowanie działań konserwacyjnych. Ponadto wiele baz danych używa skryptów typu open source do konserwacji bazy danych, aby uzyskać większą elastyczność i kontrolę nad działaniami konserwacyjnymi.
Najlepsze rozwiązania dotyczące planów konserwacji
Plany konserwacji nie tylko ułatwiają konserwację bazy danych, ale także oferują opcje oczyszczania danych z bazy danych msdb, która służy jako magazyn danych dla agenta programu SQL Server. Ponadto plany konserwacji umożliwiają określenie usunięcia starszych kopii zapasowych bazy danych z dysku. Usunięcie starych plików kopii zapasowej zmniejsza rozmiar woluminu kopii zapasowej i ułatwia zarządzanie rozmiarem bazy danych msdb.
Upewnij się, że okres przechowywania kopii zapasowej jest dłuższy niż okno sprawdzania spójności. Jeśli na przykład co tydzień uruchomisz sprawdzanie spójności, zachowaj wystarczającą historię kopii zapasowych, aby odzyskać sprawność po wykryciu potencjalnego uszkodzenia podczas sprawdzania spójności. Należy pamiętać, że operacja tworzenia kopii zapasowej nie wykrywa uszkodzenia w bazie danych, więc istnieje możliwość uszkodzenia w pliku kopii zapasowej. Działania planu konserwacji są zaplanowane jako zadania agenta programu SQL Server do wykonania.
Tworzenie planu konserwacji
Plan konserwacji można utworzyć przy użyciu programu SQL Server Management Studio, jak pokazano poniżej. W tym przykładzie wiele zadań konserwacji jest połączonych w jeden plan konserwacji. Najlepszym rozwiązaniem jest jednak utworzenie oddzielnego planu konserwacji dla każdego typu zadania, a nawet w przypadku określonych baz danych na serwerze. Można na przykład utworzyć jeden plan konserwacji, aby utworzyć kopię zapasową systemowych baz danych, a drugi do tworzenia kopii zapasowych baz danych użytkowników. Ponadto można mieć oddzielny plan konserwacji do obsługi kopii zapasowej szczególnie dużej bazy danych użytkownika. Na poniższej ilustracji przedstawiono sposób tworzenia planu konserwacji przy użyciu Kreatora planu konserwacji.
Obraz przedstawia pierwszy ekran Kreatora planu konserwacji w programie SQL Server Management Studio (SSMS). Musisz określić nazwę planu konserwacji oraz konto do uruchamiania jako. Większość zadań konserwacji będzie działać jako konto usługi agenta programu SQL Server, ale w celach bezpieczeństwa niektóre zadania mogą wymagać uruchomienia jako innego konta. Jeśli na przykład musisz wykonać kopię zapasową na udziale plików dostępnym tylko przez określone konto, użyj użytkownika proxy, który jest składnikiem Agenta SQL Server.
Co to jest konto serwera proxy?
Konto proxy to konto z zapisanymi poświadczeniami, które agent oprogramowania SQL Server może używać do wykonywania określonych kroków zadania jako wyznaczony użytkownik. Dane logowania tego użytkownika są przechowywane jako poświadczenie w wystąpieniu programu SQL Server. Konta serwera proxy są zwykle używane, gdy określone kroki zadania wymagają bardzo granularnych praw zabezpieczeń.
Załóżmy, że masz zadanie agenta programu SQL Server, które musi utworzyć kopię zapasową bazy danych w sieciowym udziale plików. Jeśli konto usługi SQL Server Agent nie ma dostępu do udziału plików, możesz utworzyć konto proxy z niezbędnymi uprawnieniami. To konto serwera proxy może następnie służyć do uruchamiania kroku tworzenia kopii zapasowej, zapewniając, że ma wymagane prawa dostępu.
Harmonogramy zadań
Harmonogramy zadań są częścią systemu zadań w bazie danych systemu msdb. Zadania i harmonogramy agenta programu SQL Server mają relację wiele do wielu, co oznacza, że każde zadanie może mieć wiele harmonogramów, a każdy harmonogram można przypisać do wielu zadań. Jednak Kreator planów konserwacji nie zezwala na tworzenie niezależnych harmonogramów. Zamiast tego tworzy określony harmonogram dla każdego planu konserwacji.
W poniższym przykładzie przedstawiono harmonogram wykonywania tygodniowego, ale istnieje również możliwość utworzenia harmonogramu z cyklem godzinowym lub dziennym.
Następnym krokiem jest wybranie zadań konserwacji, które mają zostać dodane do planu. W poniższym przykładzie przedstawiono operacje dostępne do wykonania przez plan konserwacji.
Sprawdź integralność bazy danych — to zadanie uruchamia DBCC CHECKDB polecenie, aby zweryfikować spójność logiczną i fizyczną każdej strony bazy danych. To zadanie należy wykonać regularnie i dostosować je do okna przechowywania kopii zapasowych. Zanim odrzucisz wcześniejsze kopie zapasowe, upewnij się, że wykonasz sprawdzanie spójności, aby zapobiec przeniesieniu uszkodzenia.
Zmniejszanie bazy danych — to zadanie zmniejsza rozmiar bazy danych lub pliku dziennika transakcji przez przeniesienie danych na wolne miejsce na stronach. Po zwolnieniu wystarczającej ilości miejsca można go wrócić do systemu plików. Zaleca się, aby nie uwzględniać tej akcji w regularnej konserwacji, ponieważ powoduje poważne fragmentację indeksu, szkodząc wydajności bazy danych. Operacja jest również bardzo intensywnie wykorzystująca operacje we/wy i procesora CPU, co może znacząco wpłynąć na wydajność systemu.
Zreorganizowanie/ponowne kompilowanie indeksu — to zadanie sprawdza poziom fragmentacji w indeksach bazy danych i ponownie kompiluje lub reorganizuje indeks na podstawie poziomu fragmentacji zdefiniowanego przez użytkownika. Ponowne kompilowanie indeksu aktualizuje również statystyki.
Statystyki aktualizacji — to zadanie aktualizuje statystyki kolumn i indeksu używane przez program SQL Server do tworzenia planów wykonywania zapytań. Dokładne statystyki mają kluczowe znaczenie dla optymalizatora zapytań w celu podejmowania najlepszych decyzji. Możesz wybrać tabele i indeksy do skanowania oraz wartość procentową lub liczbę wierszy do skanowania. Domyślna częstotliwość próbkowania jest zwykle wystarczająca, ale może być potrzebna bardziej szczegółowa statystyka dla określonych tabel.
Historia oczyszczania — to zadanie usuwa historię operacji tworzenia kopii zapasowych i przywracania z msdb bazy danych, a także historię zadań agenta programu SQL Server. Ułatwia zarządzanie rozmiarem bazy danych msdb.
Wykonaj zadanie agenta programu SQL Server — to zadanie uruchamia zdefiniowane przez użytkownika zadanie agenta programu SQL Server.
Kopia zapasowa bazy danych (pełna/różnicowa/dziennik) — to zadanie tworzy kopię zapasową baz danych w wystąpieniu programu SQL Server. Pełna kopia zapasowa przechwytuje całą bazę danych i służy jako punkt początkowy przywracania. Różnicowe kopie zapasowe przechwytują strony, które uległy zmianie od ostatniej pełnej kopii zapasowej, zapewniając przyrostowy punkt przywracania. Kopie zapasowe dziennika transakcji przechwytują aktywne strony w dzienniku transakcji, co umożliwia zdefiniowanie celu punktu odzyskiwania. Należy pamiętać, że nie można wykonywać kopii zapasowych dziennika transakcji w bazach danych w trybie odzyskiwania SIMPLE.
Jeśli na przykład wykonasz pełną kopię zapasową w niedzielę i różnicową kopię zapasową każdego wieczoru w dni robocze, aby przywrócić bazę danych do południa w czwartek, przywrócisz pełną kopię zapasową z niedzieli, kopię zapasową różnicową z środy oraz kopie zapasowe dziennika transakcji od różnicowej kopii zapasowej z środy do czwartku w południe.
Zadania oczyszczania konserwacji — to zadanie usuwa stare pliki związane z planami konserwacji, w tym raporty tekstowe i pliki kopii zapasowej. Usuwa tylko kopie zapasowe w określonych folderach, więc wszystkie podfoldery muszą być jawnie wymienione lub zostaną pominięte.
Każde zadanie może być ograniczone do baz danych użytkowników, systemowych baz danych lub niestandardowego wyboru baz danych, a każde z nich ma określone opcje konfiguracji.
Po utworzeniu plan będzie wyświetlany jako zadanie w agencie programu SQL Server. Jeśli dodałeś harmonogram podczas procesu tworzenia lub później, to zadanie zostanie wykonane, a zadania konserwacyjne zostaną przeprowadzone.
Środowisko wieloserwerowe
W środowisku wieloserwerowym agent programu SQL Server umożliwia wyznaczenie jednego serwera jako serwera podstawowego, który może wykonywać zadania na innych serwerach, nazywanych serwerami docelowymi. Serwer podstawowy przechowuje główne źródło zadań i dystrybuuje je do serwerów docelowych. Serwery docelowe okresowo łączą się z serwerem podstawowym, aby zaktualizować harmonogramy zadań. Ta konfiguracja umożliwia zdefiniowanie zadania raz i wdrożenie go w przedsiębiorstwie. Można na przykład skonfigurować zadania konserwacji bazy danych na serwerze podstawowym i wypchnąć je do grupy serwerów docelowych, zapewniając spójne wdrażanie.