Erstellen eines SQL Server-Wartungsplans
Typische Aktivitäten, die Sie für die regelmäßige SQL Server-Wartung planen können, sind die Folgenden:
- Datenbank- und Transaktionsprotokollsicherungen
- Datenbankkonsistenzprüfungen
- Indexverwaltung
- Statistikupdates
Es ist wichtig, die Bedeutung von Sicherungen sowie der Wartung von Indizes und Statistiken für alle Ihre Datenbanken zu verstehen. Datenbankkonsistenzprüfungen, auch als CHECKDB (mit dem Befehl DBCC CHECKDB) bezeichnet, sind ebenso wichtig, da sie die einzige Möglichkeit sind, eine gesamte Datenbank auf Beschädigung zu überprüfen. Abhängig von der Größe Ihrer Datenbanken und Ihren Anforderungen an die Uptime können Sie alle diese Aktivitäten nachts durchführen. In Produktionssystemen sind die Wartungsvorgänge jedoch häufig über die Woche verteilt, da sowohl die Indexwartung als auch die Konsistenzprüfungen sehr E/A-intensive Vorgänge sind und typischerweise am Wochenende durchgeführt werden.
Viele DBAs staffeln Sicherungen großer Datenbanken, führen eine vollständige Sicherung pro Woche aus und verwenden differenzielle und Transaktionsprotokollsicherungen, um die Wiederherstellung eines bestimmten Zeitpunkts zu verwalten. SQL Server bietet eine integrierte Möglichkeit zum Verwalten all dieser Aufgaben mithilfe von Wartungsplänen. Wartungspläne erstellen einen Workflow mit Aufgaben zur Unterstützung Ihrer Datenbanken und werden als Integration Services-Pakete erstellt, sodass Sie Ihre Wartungsaktivitäten planen können. Darüber hinaus verwenden viele DBAs Open-Source-Skripts für die Datenbankwartung, um mehr Flexibilität und Kontrolle über Wartungsaktivitäten zu erhalten.
Bewährte Methoden für Wartungspläne
Wartungspläne helfen Ihnen nicht nur bei der Durchführung der Datenbankwartung, sondern bieten auch Optionen zum Löschen von Daten aus der msdb-Datenbank, die als Datenspeicher für den SQL Server-Agent dient. Außerdem können Sie mit Wartungsplänen angeben, dass ältere Datenbanksicherungen vom Datenträger entfernt werden sollen. Durch das Entfernen alter Sicherungsdateien wird die Größe des Sicherungsvolumes reduziert und die Größe der msdb-Datenbank verwaltet.
Stellen Sie sicher, dass der Aufbewahrungszeitraum Ihrer Sicherung länger ist als das Fenster für die Konsistenzprüfung. Wenn Sie beispielsweise wöchentlich eine Konsistenzprüfung ausführen, sollten Sie genügend Sicherungsverlauf beibehalten, um bei einer potenziellen Beschädigung, die bei der Konsistenzprüfung festgestellt wird, eine Wiederherstellung durchzuführen. Beachten Sie, dass der Sicherungsvorgang keine Beschädigung in einer Datenbank erkennt. Daher kann eine Beschädigung innerhalb einer Sicherungsdatei vorliegen. Wartungsplanaktivitäten werden als SQL Server-Agent-Aufträge zur Ausführung geplant.
Erstellen eines Wartungsplans
Sie können einen Wartungsplan mit SQL Server Management Studio erstellen, wie unten gezeigt. Im Beispiel sind mehrere Wartungsaufgaben in einem Wartungsplan zusammengefasst. Die bewährte Vorgehensweise besteht jedoch darin, einen separaten Wartungsplan für jede Art von Aufgabe zu erstellen – und möglicherweise sogar für bestimmte Datenbanken auf Ihrem Server. Sie können beispielsweise einen Wartungsplan für die Sicherung von Systemdatenbanken und einen anderen Wartungsplan für die Sicherung von Benutzerdatenbanken erstellen. Darüber hinaus können Sie einen separaten Wartungsplan für die Verarbeitung der Sicherung einer besonders großen Benutzerdatenbank konfigurieren. Die folgende Abbildung und die folgenden Beispiele veranschaulichen die Erstellung eines Wartungsplans mithilfe des Wartungsplanungs-Assistenten.
Die Abbildung zeigt den ersten Bildschirm des Wartungsplanungs-Assistenten in SQL Server Management Studio (SSMS). Sie müssen einen Namen für Ihren Wartungsplan und ein ausführendes Konto angeben. Die meisten Wartungsaufgaben werden als SQL Server-Agent-Dienstkonto ausgeführt, aber für Sicherheitszwecke müssen einige Aufgaben möglicherweise als anderes Konto ausgeführt werden. Wenn Sie z. B. eine Sicherung für eine Dateifreigabe ausführen müssen, auf die nur ein bestimmtes Konto zugreifen kann, verwenden Sie ein Proxykonto, das eine Komponente des SQL Server-Agents sind.
Was ist ein Proxykonto?
Ein Proxykonto ist ein Konto mit gespeicherten Anmeldeinformationen, mit denen der SQL Server-Agent bestimmte Auftragsschritte als vorgesehene benutzende Person ausführen kann. Die Anmeldeinformationen für diese benutzende Person werden als Anmeldeinformationen in der SQL Server-Instanz gespeichert. Proxykonten werden in der Regel verwendet, wenn bestimmte Auftragsschritte sehr präzise Sicherheitsrechte erfordern.
Angenommen, Sie haben einen SQL Server-Agent-Auftrag, der eine Datenbank in einer Netzwerkdateifreigabe sichern muss. Wenn das SQL Server-Agent-Dienstkonto keinen Zugriff auf die Dateifreigabe hat, können Sie ein Proxykonto mit den erforderlichen Berechtigungen erstellen. Dieses Proxykonto kann dann zum Ausführen des Sicherungsschritts verwendet werden, um sicherzustellen, dass es über die erforderlichen Zugriffsrechte verfügt.
Auftragszeitpläne
Auftragszeitpläne sind ein Teil des Auftragssystems in der msdb-Systemdatenbank. SQL Server-Agent-Aufträge und -Zeitpläne weisen eine m:n-Beziehung auf, d. h. jeder Auftrag kann mehrere Zeitpläne haben, und jeder Zeitplan kann mehreren Aufträgen zugewiesen werden. Der Wartungsplanungs-Assistent erlaubt jedoch nicht die Erstellung von unabhängigen Zeitplänen. Stattdessen wird ein bestimmter Zeitplan für jeden Wartungsplan erstellt.
Das folgende Beispiel zeigt den Zeitplan für eine wöchentliche Ausführung, aber Sie haben auch die Möglichkeit, einen Zeitplan mit stündlicher oder täglicher Wiederholung zu erstellen.
Der nächste Schritt besteht darin, die Wartungsaufgaben auszuwählen, die dem Plan hinzugefügt werden sollen. Das folgende Beispiel zeigt die Vorgänge, die von Ihrem Wartungsplan ausgeführt werden können.
Überprüfen sie die Datenbankintegrität – Mit dieser Aufgabe wird der DBCC CHECKDB Befehl ausgeführt, um die logische und physische Konsistenz jeder Datenbankseite zu überprüfen. Sie sollten diese Aufgabe regelmäßig ausführen und an Ihrem Sicherungsaufbewahrungsfenster ausrichten. Stellen Sie sicher, dass Sie eine Konsistenzprüfung abschließen, bevor Sie vorherige Sicherungen verwerfen, um die Weitergabe einer Beschädigung zu verhindern.
Verkleinern der Datenbank – Diese Aufgabe reduziert die Größe einer Datenbank- oder Transaktionsprotokolldatei, indem Daten in freien Speicherplatz auf Seiten verschoben werden. Sobald genügend Speicherplatz freigegeben wurde, ist die Rückgabe an das Dateisystem möglich. Es wird empfohlen, diese Aktion nicht in die regelmäßige Wartung einzuschließen, da sie eine starke Indexfragmentierung verursacht. Dies beeinträchtigt die Datenbankleistung. Der Vorgang ist außerdem sehr E/A- und CPU-intensiv, was sich erheblich auf die Systemleistung auswirken kann.
Index neu organisieren/ neu erstellen – Mit dieser Aufgabe wird die Fragmentierungsebene in den Indizes einer Datenbank überprüft und entweder neu erstellt oder der Index basierend auf der benutzerdefinierten Fragmentierungsebene neu organisiert. Durch die Neuerstellung eines Indexes werden auch die Statistiken aktualisiert.
Aktualisieren von Statistiken – Diese Aufgabe aktualisiert die Spalten- und Indexstatistiken, die von SQL Server zum Erstellen von Abfrageausführungsplänen verwendet werden. Genaue Statistiken sind für den Abfrageoptimierer entscheidend, um die besten Entscheidungen zu treffen. Sie können auswählen, welche Tabellen und Indizes überprüft werden sollen, und den Prozentsatz oder die Anzahl der zu scannenden Zeilen festlegen. Die standardmäßige Stichprobenrate ist in der Regel ausreichend, Sie benötigen jedoch möglicherweise detailliertere Statistiken für bestimmte Tabellen.
Bereinigungsverlauf – Diese Aufgabe löscht den Verlauf von Sicherungs- und Wiederherstellungsvorgängen aus der msdb Datenbank sowie den Verlauf von SQL Server-Agent-Aufträgen. Dies hilft beim Verwalten der Größe der msdb-Datenbank.
Ausführen des SQL Server-Agent-Auftrags – Diese Aufgabe führt einen benutzerdefinierten SQL Server-Agent-Auftrag aus.
Sicherungsdatenbank (Vollständig/Differenzial/Protokoll) – Diese Aufgabe sichert Datenbanken in einer SQL Server-Instanz. Eine vollständige Sicherung erfasst die gesamte Datenbank und dient als Ausgangspunkt für eine Wiederherstellung. Differenzielle Sicherungen erfassen die Seiten, die seit der letzten vollständigen Sicherung geändert wurden, stellen einen inkrementellen Wiederherstellungspunkt bereit. Transaktionsprotokollsicherungen erfassen die aktiven Seiten in Ihrem Transaktionsprotokoll und ermöglichen es Ihnen, Ihr Recovery Point Objective (RPO) zu definieren. Beachten Sie, dass Transaktionsprotokollsicherungen nicht für Datenbanken im Wiederherstellungsmodus EINFACH durchgeführt werden.
Wenn Sie z. B. sonntags eine vollständige Sicherung und jeden Abend unter der Woche eine differenzielle Sicherung erstellen, müssen Sie zum Herstellen des Datenbankstatus von Donnerstagmittag die vollständige Sicherung von Sonntag, die differenzielle Sicherung von Mittwoch und die Transaktionsprotokollsicherungen von Mittwoch bis Donnerstagmittag wiederherstellen.
Wartungsbereinigungsaufgaben – Diese Aufgabe entfernt alte Dateien im Zusammenhang mit Wartungsplänen, einschließlich Textberichte und Sicherungsdateien. Es werden nur Sicherungen in den angegebenen Ordnern entfernt, sodass alle Unterordner explizit aufgelistet werden müssen,. Andernfalls werden sie übersprungen.
Jede Aufgabe kann auf Benutzerdatenbanken, Systemdatenbanken oder eine benutzerdefinierte Auswahl von Datenbanken ausgerichtet sein und verfügt über spezifische Konfigurationsoptionen.
Nach der Erstellung wird der Plan als Auftrag im SQL Server-Agent angezeigt. Wenn Sie einen Zeitplan entweder während des Erstellungsprozesses oder danach hinzugefügt haben, wird dieser Auftrag ausgeführt und die Wartungsaufgaben werden durchgeführt.
Multiserverumgebung
In einer Multiserverumgebung können Sie mit dem SQL Server-Agent einen Server als primären Server festlegen, der Aufträge auf anderen Servern ausführen kann, die als Zielserver bezeichnet werden. Der primäre Server speichert die Hauptquelle der Aufträge und verteilt sie an die Zielserver. Zielserver stellen regelmäßig eine Verbindung mit dem primären Server her, um ihre Auftragspläne zu aktualisieren. Mit diesem Setup können Sie einen Auftrag einmal definieren und in Ihrem Unternehmen bereitstellen. Sie können z. B. Datenbankwartungsaufgaben auf dem primären Server konfigurieren und an eine Gruppe von Zielservern übertragen, um eine konsistente Bereitstellung sicherzustellen.