Plan konserwacji serwera SQL - najlepsze praktyki dotyczące zadań i harmonogramu

Zadanie polega na opracowaniu planu konserwacji naszych baz danych Sql Server 2005. Wiem, że w przypadku kopii zapasowych chcę codziennie wykonywać pełną kopię zapasową bazy danych i kopie zapasowe dziennika transakcji co 15 minut. Mój problem polega na ustaleniu, jakie inne zadania chcę wykonywać i jak często je wykonywać.

Do tej pory mam to na uwadze. Popraw mnie, jeśli w moim myśleniu są jakieś wady lub lepszy sposób na zrobienie tego.

1. Kopia zapasowa - wszystkie tabele, pełna kopia zapasowa (codziennie)
2. Kopia zapasowa - wybrane tabele, pełna kopia zapasowa (co godzinę)
3. Kopia zapasowa - dzienniki transakcji (co 15 minut)
4. Sprawdzaj integralność bazy danych (codziennie)
5. Reorganizuj indeks (codziennie)
6. Aktualizuj statystyki (codziennie)
7. Zmniejsz bazę danych (co tydzień)
8. Przebuduj indeks (co tydzień)
9. Czyszczenie konserwacyjne (codziennie)

Pamiętam, jak czytałem jakiś czas temu (kiedy ustawiłem podobny plan w innym zadaniu), że niektóre z tych zadań nie muszą być uruchamiane codziennie lub nie powinny być uruchamiane codziennie. Jeśli chodzi o to, to mi ucieka. Mógłbym skorzystać z krótkich wskazówek na temat tworzenia lepszego planu konserwacji, który zmniejszy utratę danych w przypadku katastrofy, ale nie obciąży systemu podczas pracy w godzinach szczytu (a także zwiększy wydajność).

Josh,

Jest to bardzo częste zadanie dla wszystkich DBA, a poprawna odpowiedź NIE jest taka sama dla każdego i dla każdego serwera. Podobnie jak wiele innych rzeczy, zależy to od tego, czego potrzebujesz.

Zdecydowanie nie chcesz uruchamiać „Shrink Database”, jak już sugerowano. Jego ZŁO do wydajności, a poniżej odniesienie pokaże ci dlaczego. Powoduje to fragmentację dysku i indeksów, co może prowadzić do problemów z wydajnością. Lepiej jest, jeśli wstępnie przydzielisz duży rozmiar danych i plików dziennika, aby automatyczne uruchamianie NIE uruchomiło się.

Nie zrozumiałem twojego nr 2. pełna kopia zapasowa wybranych tabel. Czy możesz rozwinąć więcej na ten temat?

Przychodząc do reorganizacji indeksu, aktualizacji statystyk i przebudowy indeksu, musisz uważać na to, jak to zrobisz, w przeciwnym razie skończysz na użyciu większej ilości zasobów, a także na problemach z wydajnością.

Po przebudowaniu indeksów statystyki indeksów są aktualizowane za pomocą fullscan, ale jeśli później zaktualizujesz statystyki, zostaną one ponownie zaktualizowane z domyślną próbką (która zależy od kilku czynników, zwykle 5% tabeli, gdy rozmiar tabeli> 8 MB), co może prowadzić do problemów z wydajnością. W zależności od posiadanej edycji może być możliwe przebudowywanie indeksu online. Właściwym sposobem wykonania tej czynności jest sprawdzenie stopnia fragmentacji i w zależności od tego albo przebuduj indeks, albo zreorganizuj indeks + zaktualizuj statystyki. Możesz także chcieć określić, które tabele wymagają częstszej aktualizacji statystyk i próbować częściej aktualizować statystyki.

Plany konserwacji są w porządku, ale trudno jest uzyskać z nich jak najwięcej, wykonując te dostosowania, chyba że możesz zalogować się do SSIS i dostosować MP. dlatego wolę NIE używać ich i używać darmowych skryptów Oli Hallengren, które są bardziej niezawodne niż MP. Radziłbym również nadrobić zaległości w artykule Paula Randala na ten temat.

Patrz: http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

To NIE jest wyczerpująca odpowiedź na twoje pytanie, ale dobry punkt wyjścia. HTH i daj nam znać, jeśli masz dodatkowe pytania / komentarze.

Podzielę się swoim doświadczeniem, nawet jeśli już zaakceptowałeś odpowiedź. Może to będzie pomocne :-).

1. Pełna codzienna kopia zapasowa (codziennie) - świetnie, ale nie zapomnij sprawdzić miejsca i usunąć stare pliki po określonym czasie.
2. Twórz kopie zapasowe wybranych tabel (co godzinę) - nie rozumiem, dlaczego jest to potrzebne, lepiej skorzystaj z różnicowych kopii zapasowych. Jak wykonać kopię zapasową tylko niektórych tabel: SSIS, skrypty, BCP? Jeśli chodzi o kopie zapasowe różnic, nie planuj ich zbyt często, ponieważ ukradniesz rolę kopii zapasowych dzienników.
3. Kopie zapasowe dziennika transakcji (co 15 minut) - świetnie, czy na pewno potrzebujesz wszystkich baz danych? Czy wszystkie bazy danych używają pełnego modelu odzyskiwania, czy nie?
4. Sprawdź integralność db - tak, ale musisz się upewnić, że nie zabijesz swojego środowiska. Instrukcje sprawdzania DBCC są dość samolubne w odniesieniu do zasobów i skanują pełne dbs, więc należy je zaplanować poza godzinami pracy.
5. Reorganizuj indeks (codziennie) - nie zmuszaj go, rób to tylko w razie potrzeby. Sprawdź indeks DMV dotyczący fragmentacji i zreorganizuj go tylko w zależności od potrzeb. Przenosiłbym wszystkie operacje indeksu i statystyki na jednym cotygodniowym zadaniu.
6. Aktualizuj statystyki (codziennie) - Proszę zobaczyć moją odpowiedź na poprzednie pytanie. Zamiast wymuszać aktualizację wszystkich statystyk, codziennie lepiej sprawdzaj, kiedy statystyki zostały ostatnio zaktualizowane, i tylko w niektórych przypadkach je aktualizuj.
7. Zmniejsz bazę danych (co tydzień) - Och, nie. Przeczytaj artykuł Paula Randala na temat zmniejszania plików.
8. Przebuduj indeks (co tydzień) - patrz 5.

9. Czyszczenie konserwacyjne (codziennie) - z tym dobrze.

10. Lepiej też dodaj zadanie weryfikujące kopie zapasowe - istnieje wersja polecenia PRZYWRÓĆ (Verify .. tylko jeśli dobrze pamiętam) - powiedzmy co tydzień, choć wolę to codziennie.

Wspominasz, że chcesz być chroniony w przypadku utraty danych, więc powiedziałbym, że musisz dodać systemowe bazy danych w tej procedurze konserwacji. A także zadbaj o tworzenie kopii zapasowych plików na innym komputerze niż sam serwer. Zachowaj gdzieś plik z tym, co robić na wypadek, gdybyś musiał odbudować master db, msdb..etc. Powodzenia w zadaniu!

Późna odpowiedź, ale może być przydatna dla innych czytelników.

Należy pamiętać, że istnieje wiele zadań związanych z konserwacją lub raportowaniem, które można utworzyć, które niosą ze sobą niewidoczne ryzyko.

Co stałoby się, gdyby dysk zapełnił się podczas codziennych różnicowych kopii zapasowych? A co jeśli zadanie odbudowywania indeksu działa wyjątkowo długo? Co powiesz na to, czy proces ładowania danych spowoduje rozległą rywalizację o zasoby, powodując normalne operacje na kolana? Wszystkie są planowanymi wydarzeniami, ale mogą powodować znaczne zakłócenia w samych procesach, które staramy się chronić.

Zawsze należy zastanowić się, w jaki sposób różne zadania współdziałają i mierzyć czas tak, aby zadania wrażliwe lub wymagające dużych zasobów nie nakładały się.

Proponuję najpierw przeczytać ten artykuł: http://www.sqlshack.com/removing-the-risk-from-important-maintenance-tasks-in-sql-server/

Opisuje, jak wykonywać ważne zadania konserwacyjne w SQL Server - bez ryzyka. Na przykład - możesz wbudować proste kontrole do zadań konserwacyjnych, które weryfikują dostępne zasoby, a także to, czego wymaga operacja przed wykonaniem. Dzięki temu masz pewność, że twoje środowisko poradzi sobie z tym, co masz zamiar zrobić, i przerwiesz ze znaczącym błędem, jeśli zasoby są niewystarczające.

1. Wydaje się w porządku

2. Możesz skorzystać z różnicowych kopii zapasowych tutaj. Sprawdź je na pewno

3. Wydaje się w porządku

4. Wydaje się w porządku

5. Jak wspomniano wcześniej, skurcze bazy danych są złe, ponieważ powodują fizyczne rozdrobnienie danych i plików dziennika, powodując w ten sposób więcej losowych odczytów We / Wy.

5, 6 i 8: patrz poniżej.

Te naprawdę idą w parze, ponieważ indeksy opierają się na aktualnych statystykach, a kolejność tych operacji jest dość ważna. Metodą podstawową, którą stosuję, co prawda może nie działać dla wszystkich, jest wykonanie dwóch rund utrzymywania indeksu. Najpierw wykonuję indeksy klastrowe, a następnie indeksy nieklastrowane. Metoda, którą stosuję dla obu jest następująca. Jeśli indeks jest wystarczająco duży i wystarczająco pofragmentowany (sys.dm_db_index_physical_stats), odbuduję indeks (który obejmuje aktualizację statystyk z pełnym skanowaniem). Jeśli indeks jest zbyt mały, aby go odbudować, lub nie jest wystarczająco rozdrobniony, aby go odbudować (mniej niż 100 stron i fragmentacja między 5% a 15%), najpierw przeprowadzę reorganizację indeksu. Następnie wykonam aktualizację statystyk przy pełnym skanie.

Teraz obejmuje to statystyki indeksu, ale zaniedbuje robienie czegokolwiek ze statystykami kolumn. Co tydzień będę aktualizować statystyki kolumn.

Mam nadzieję, że to pomogło w jakiś sposób!

Przechyliłem się do komentarza „utrata danych może mieć tutaj prawne konsekwencje”.

Następnie zdecydowanie chcesz uzyskać potężne narzędzie innej firmy (takie jak DPM), które może obsługiwać kopie zapasowe (i odzyskiwać po katastrofalnych zdarzeniach w mgnieniu oka i przy minimalnym zamieszaniu) dużo szybciej i dużo lepiej niż jakikolwiek skrypt, który można pobrać z Internetu.

Tworzenie kopii zapasowych to jedno. Umiejętność korzystania z nich w nagłych wypadkach to inna sprawa.

Nie zapominaj, że jeśli przywracasz kopię zapasową po poważnej awarii, prawdopodobnie jesteś już pod presją stresu i stresu. Nie potrzebujesz dodatkowego obciążenia polegającego na kopaniu i bezbłędnym zapisywaniu instrukcji RESTORE DATABASE z 12 plikami dzienników transakcji ... I modląc się, żeby cię to nie zawiodło ...

W moim miejscu pracy mogę odzyskać / przywrócić bazę danych 350 Gb do dowolnego punktu w ciągu 5 minut za ostatni tydzień za pomocą DPM. Z interfejsem GUI. Warto, w mojej książce.

Jeśli chodzi o resztę, zdecydowanie zajrzyj do skryptu indeksu Oli Hallengren i dostosuj parametry do swoich potrzeb. Osobiście połączyłem go z zaplanowanym zadaniem, które powoduje, że działa co godzinę przez godzinę bez ponownego skanowania, więc obsługuje najgorsze indeksy za każdym razem i wymusza pełne ponowne skanowanie fragmentacji w każdą sobotę lub kiedy wszystkie indeksy na liście zostały zdefragmentowane.

Na koniec dodaję mój głos do grupy „nie zmniejszaj automatycznie plików, nigdy”. File Shrink to narzędzie, z którego można korzystać sporadycznie, gdy wystąpi nieregularne zdarzenie, które przerasta twoje logi lub pliki DB (nieskończona pętla itp.). To nie powinno być narzędzie do konserwacji. Jeśli masz presję miejsca na dysku, zmniejszanie i tak tylko opóźni to, co nieuniknione.