SQL – Fragen und AntwortenPartitionierung, Konsistenzprüfungen und mehr

Paul S. Randal

F: Ich habe versehentlich eine Produktionsdatenbank an einen SQL Server® 2005-Server angefügt und versuche jetzt, sie an den richtigen Server anzufügen, auf dem SQL Server 2000 ausgeführt wird. Ich habe versucht, die Datenbank einfach zu trennen und sie an den SQL Server 2000-Server anzufügen. Ich habe es auch mithilfe einer Sicherung und Wiederherstellung versucht, aber keine dieser Methoden funktioniert. Warum funktioniert es nicht auf meinem SQL Server 2000-Server? Dies ist die einzige Kopie der Datenbank, über die ich verfüge.

A: Der erste Punkt, der hierbei zu beachten ist, ist die Wichtigkeit von Sicherungen. Datenbankadministratoren denken in der Regel, dass sie Sicherungen für die Wiederherstellung nach einer Beschädigung oder einem anderen Notfall, bei dem Daten zerstört wurden, benötigen. Bei Ihnen ist jedoch ein weniger offensichtlicher Notfall aufgetreten – ein Fehler während einer Aktualisierung, obwohl es sich in diesem Fall um eine versehentliche Aktualisierung handelte. Dennoch gilt, dass Sie für den Fall, dass Ihre Datenbank beschädigt wird, stets über eine aktuelle vollständige Datenbanksicherung verfügen sollten.

Eine absichtliche oder versehentliche Aktualisierung ist ein unidirektionaler Vorgang, dessen Auswirkungen sich nur sehr schwer rückgängig machen lassen. Wenn Sie von einer Version von SQL Server auf eine andere aktualisieren, wird in der Datenbank eine Reihe von Aktualisierungsschritten durchgeführt. Jeder Schritt umfasst in der Regel einige physische Änderungen an der Datenbank. Zudem wird bei jedem Schritt die Versionsnummer der Datenbank erhöht.

Eine der wesentlichen Änderungen, die beim Aktualisieren einer Datenbank von SQL Server 2000 auf SQL Server 2005 durchgeführt werden, besteht beispielsweise darin, die Struktur der Systemkataloge einer Datenbank (häufig als Systemtabellen oder Datenbankmetadaten bezeichnet) zu ändern, die verschiedene Metadaten über Tabellen, Indizes, Spalten, Zuordnungen und andere Details in Bezug auf die relationale und physische Struktur der Datenbank enthalten.

Bei jedem dieser Aktualisierungsschritte wird die Versionsnummer der Datenbank erhöht. SQL Server 7.0-Datenbanken weisen beispielsweise die Versionsnummer 515, SQL Server 2000-Datenbanken die Versionsnummer 539 und SQL Server 2005-Datenbanken die Versionsnummer 611 (oder 612, wenn das vardecimal-Feature aktiviert ist) auf. Dadurch kann SQL Server den letzten Aktualisierungsschritt ermitteln, der in Bezug auf die Datenbank durchgeführt wurde.

Versionen von SQL Server können keine Datenbanken lesen, die auf neuere Versionen von SQL Server aktualisiert wurden (SQL Server 2000 kann z. B. keine Datenbank lesen, die auf SQL Server 2005 aktualisiert wurde). Dies liegt daran, dass ältere Versionen nicht über den erforderlichen Code zum Interpretieren der aktualisierten Strukturen und des aktualisierten Datenbanklayouts verfügen. Genau darin liegt das Problem bei Ihrer Datenbank, die auf SQL Server 2005 aktualisiert wurde: Sie kann nicht wieder an SQL Server 2000 angefügt werden.

Wenn Sie nicht über eine vollständige Datenbanksicherung verfügen, besteht Ihre einzige Option darin, alle Daten aus der aktualisierten Datenbank zu exportieren und sie manuell wieder in eine neue SQL Server 2000-Datenbank zu übertragen. Solange Sie keine neuen Features von SQL Server 2005 verwendet haben, können Sie die Datenbankstruktur mithilfe eines Skripts kopieren, die Datenbank unter SQL Server 2000 erstellen und dann die Daten exportieren/importieren.

Um die Datenbank in SQL Server 2005 mithilfe eines Skripts zu kopieren, können Sie den Objekt-Explorer in SQL Server Management Studio verwenden. (Klicken Sie mit der rechten Maustaste auf die Datenbank, und wählen Sie „Tasks“ und dann die Option „Skripts generieren“ aus.) Der Assistent ist selbsterklärend und generiert ein Skript zum Erstellen aller Objekte, Indizes, Einschränkungen, Trigger und so weiter.

F: Wir haben vor kurzem unser Schema neu entworfen, damit bei der Haupttabelle Tabellenpartitionierung verwendet wird. Uns wurde gesagt, dass dies zur Verbesserung der Leistung beiträgt. Die Datenbank ist auf einem einzelnen 120-GB-Laufwerk gespeichert und die Tabelle in einer einzigen Dateigruppe enthalten. Es wird kein änderbarer Ausschnitt verwendet, sondern es werden lediglich jede Woche neue Partitionen hinzugefügt, und alle Daten müssen online verfügbar sein. Bei den meisten unserer Abfragen werden Daten aus einer einzigen Woche verarbeitet, und einige wenige erstrecken sich über das letzte Jahr. Dies scheint sich mit einer einzigen Dateigruppe einfacher realisieren zu lassen. Habe ich Recht, oder steckt mehr dahinter?

A: Obwohl es einfacher erscheinen mag, eine einzige Dateigruppe zu verwenden, ist es unwahrscheinlich, dass Sie auf diese Weise die mit der Partitionierung verbundenen Vorteile erzielen. Die Partitionierung wird hauptsächlich für eine effizientere Datenbankwartung und zur Erhöhung der Datenverfügbarkeit im Notfall verwendet. Zudem können Sie ein Schema erstellen, das Leistungssteigerungen bewirkt.

Ein typisches Beispiel für die Partitionierung ist eine Umsatztabelle mit 12 Partitionen, von denen jede einen Monat der Umsatzdaten des letzten Jahres repräsentiert. Am Ende des Monats wird die älteste Partition ausgelagert (und archiviert oder gelöscht), und es wird zu einer neuen Partition gewechselt. Dies ist das Szenario mit änderbarem Ausschnitt, das Sie erwähnt haben. Für die Partition des aktuellen Monats wird Lese-/Schreibzugriff festgelegt, und die Partitionen der vorhergehenden Monate sind schreibgeschützt. Jede Partition wird in einer eigenen Dateigruppe gespeichert. Dieses Schema bietet sämtliche Vorteile der Partitionierung, ist aber nicht unter allen Umständen optimal.

Meine Frau Kimberly hat eine abgeänderte Version des oben beschriebenen Schemas entwickelt, die eine effizientere Indizierung ermöglicht. Ziehen Sie in Erwägung, die Umsatztabelle in zwei Tabellen zu teilen – eine für die einzelne Lese-/Schreibpartition und eine für die 11 schreibgeschützten Partitionen, mit einer partitionierten Ansicht beider Tabellen.

Dadurch kann die Lese-/Schreibtabelle über eine kleine Anzahl von Indizes und die schreibgeschützte Tabelle über mehr Indizes zur Unterstützung von Berichtsabfragen verfügen. Dies erhöht wiederum deutlich die Effizienz von DML-Vorgängen (Data Manipulation Language, Datenbearbeitungssprache) für die Lese-/Schreibdaten, da nicht so viele nicht gruppierte Indizes verwaltet werden müssen.

Zudem müssen bei Abfragen für die Lese-/Schreibdaten nicht auch die schreibgeschützten Daten verarbeitet werden. Der Partitionsausschluss in Abfrageplänen ist in SQL Server 2005 nicht perfekt, insbesondere in Fällen mit komplexen Prädikaten, wurde aber in SQL Server 2008 wesentlich verbessert. Weitere Informationen hierzu finden Sie in Kimberlys Blogbeitrag unter sqlskills.com/blogs/kimberly/default,month,2007-10.aspx#a71c70243-3d57-4940-9af7-a802b73f2f93.

Um Ihnen zu zeigen, was ich meine, möchte ich einige der Features erläutern, die durch Partitionierung über mehrere Dateigruppen ermöglicht werden.

Partielle Datenbankverfügbarkeit Hierbei handelt es sich um die Fähigkeit einer Datenbank, während einer Notfallwiederherstellungssituation online und zugänglich zu sein, solange die primäre Dateigruppe online ist. Falls Sie nur über eine einzige Dateigruppe verfügen, ist die gesamte Datenbank nicht verfügbar, während sie wiederhergestellt wird. Wenn die Daten auf mehrere Dateigruppen verteilt sind, sind nur die beschädigten Dateigruppen während der Wiederherstellung offline, und Sie können möglicherweise weiter mit Ihrer Anwendung arbeiten.

Schrittweise Wiederherstellung Dieses Schema ähnelt der partiellen Datenbankverfügbarkeit. Bei einer einzelnen Dateigruppe ist die Wiederherstellungseinheit entweder eine einzelne Seite oder die gesamte Datenbank. Mit mehreren Dateigruppen können Sie eine einzelne Dateigruppe wiederherstellen und dadurch partielle Datenbankverfügbarkeit ermöglichen.

Partitionierte Datenbankwartung Mit jedem der beiden oben erläuterten Partitionierungsschemas können Sie die Indexfragmentierung pro Partition beseitigen, selbst wenn sich alle Partitionen in einer einzigen Dateigruppe befinden. Mit einer einzigen Dateigruppe büßen Sie aber die Möglichkeit ein, Konsistenzprüfungen pro Dateigruppe durchzuführen, durch die die Menge der Daten, die bei der Überprüfung der Datenbankkonsistenz (database consistency checking, DBCC) verarbeitet werden müssen, drastisch verringert werden kann (und dadurch wiederum die Menge der verwendeten CPU- und E/A-Ressourcen).

Einfach ausgedrückt: Obwohl Sie über mehrere Partitionen innerhalb derselben Dateigruppe verfügen können, bietet eine 1:1-Zuordnung zwischen Partitionen und Dateigruppen zahlreiche Vorteile.

F: Vor kurzem trat ein Problem bei einem unserer High-End-Datenbankserver auf – eine fehlerhafte Arbeitsspeicherplatine verursachte Beschädigungen. Wir entdeckten diese, als in unserer Anwendung zufällige Daten auftauchten. Daraufhin führten wir DBCC CHECKDB aus und fanden viele verschiedene Beschädigungen. Leider waren diese auch in unseren Sicherungen vorhanden, sodass wir die fehlerhaften Daten manuell bereinigen mussten.

Lange Rede, kurzer Sinn: Wir haben die fehlerhafte Hardware ersetzt und Seitenprüfsummen aktiviert. Wir würden gern regelmäßige Konsistenzprüfungen ausführen, verfügen aber nicht über ein großes Wartungsfenster, und die Prüfung unserer 2,4-TB-Datenbank nimmt viel Zeit in Anspruch. Was können wir tun?

A: Die Frage, wie für eine sehr große Datenbank (Very Large Database, VLDB) Konsistenzprüfungen und andere Wartungsaufgaben durchgeführt werden können, stellt sich immer häufiger. Viele DBAs geben auf, nachdem sie festgestellt haben, dass die Ausführung von DBCC CHECKDB länger dauert, als es ihr Wartungsfenster zulässt. (In einigen Situationen wird die Datenbank täglich rund um die Uhr verwendet, und es ist nicht möglich, den für DBCC CHECKDB erforderlichen CPU- und E/A-Aufwand längere Zeit zu tolerieren.)

Statt aufzugeben und keine Konsistenzprüfungen auszuführen (was definitiv nicht empfehlenswert ist), stehen vier verschiedene Methoden zur Auswahl. Ich habe persönlich Kunden geholfen, diese vier Ansätze zu nutzen.

Verwenden der Option WITH PHYSICAL_ONLY von DBCC CHECKDB Bei der regelmäßigen Überprüfung der Datenbankkonsistenz mit DBCC CHECKDB werden zahlreiche logische Konsistenzprüfungen ausgeführt, die einen großen Teil der CPU-Ressourcen in Anspruch nehmen (sodass es sich im Grunde um einen CPU-gebundenen Vorgang handelt). Bei Verwendung der Option WITH PHYSICAL_ONLY beschränken sich die Prüfungen auf die sehr schnellen Prüfungen der Konsistenz der Zuordnungsbitmuster mit DBCC CHECKALLOC und anschließendes Lesen und Überprüfen jeder zugeordneten Seite in der Datenbank, wodurch die Prüfung aller für die Seiten vorhandenen Seitenprüfsummen erzwungen wird. Dadurch wird DBCC CHECKDB zu einem E/A-gebundenen Vorgang mit einer viel kürzeren Ausführungszeit. (Tatsächlich ist der Vorgang manchmal sogar um eine Größenordnung schneller als ein vollständiger DBCC CHECKDB-Vorgang, woraus sich beträchtliche Zeiteinsparungen ergeben können.)

Aufteilen der Konsistenzprüfungslast Hierzu ist es erforderlich, dass die Tabellen in der Datenbank in gleich große Gruppen aufgeteilt werden (der einfachste Ansatz besteht darin, diese Aufteilung nach der Seitenanzahl vorzunehmen) und dann jede Nacht mithilfe von DBCC CHECKTABLE-Befehlen die Konsistenz aller Tabellen in einer einzelnen Gruppe geprüft wird. Daher können Sie, wenn Sie beispielsweise über sieben Gruppen verfügen, von denen pro Tag eine geprüft wird, und Sie DBCC CHECKALLOC und DBCC CHECKCATALOG einmal pro Woche ausführen, das Äquivalent eines DBCC CHECKDB-Vorgangs erreichen – wenn auch über eine Woche verteilt.

Verwenden der Tabellenpartitionierung mit mehreren Dateigruppen Die größten Tabellen in der VLDB können auf mehrere Dateigruppen aufgeteilt werden. Ein Beispielschema für die Konsistenzprüfung kann darin bestehen, täglich DBCC CHECKFILEGROUP für die Dateigruppe, die die Lese-/Schreibpartition enthält, und wöchentlich DBCC CHECKFILEGROUP für die Dateigruppen, die die schreibgeschützten Partitionen enthalten, auszuführen. Der Gedanke dabei ist, dass die schreibgeschützten Daten umfassend gesichert und bei der alltäglichen Verarbeitung nicht verwendet werden. Daher muss ihre Konsistenz nicht so häufig geprüft werden, da die Beschädigung dieser Daten nicht so schwerwiegend ist.

Auslagern der Konsistenzprüfungen auf einen anderen Server Diese Option umfasst die Wiederherstellung regelmäßiger vollständiger Datenbanksicherungen auf einem anderen Server und die Ausführung von DBCC CHECKDB auf diesem Server. Offensichtlich wird dadurch der Produktionsserver vollständig von der Konsistenzprüfungslast befreit. Der Nachteil besteht jedoch darin, dass beim Auffinden einer Beschädigung eine Konsistenzprüfung auf dem Produktionsserver ausgeführt werden muss – dies sollte aber nur sehr selten vorkommen.

Sie sehen also, dass DBAs zahlreiche Optionen für die Durchführung von Konsistenzprüfungen bei einer VLDB zur Verfügung stehen, wodurch die zusätzliche Ressourcenlast verringert werden kann, die beim Ausführen eines vollständigen DBCC CHECKDB-Vorgangs erforderlich ist. Wie bereits erwähnt, habe ich persönlich Kunden geholfen, diese vier Ansätze zu nutzen. Ich bin überzeugt, dass Sie feststellen werden, dass diese Optionen für Sie funktionieren.

Tipp: Verwenden von Triggern zum Implementieren der serverseitigen Logik

In einigen Szenarios müssen Sie die serverseitige Logik mit Triggern implementieren. Es gibt aber bestimmte Fallen, die Sie kennen müssen. Im Folgenden werden einige wichtige Punkte erläutert, die Sie beachten sollten.

• Trigger werden von Anweisungen und nicht pro Zeile ausgelöst. Dennoch sollten Sie unbedingt zusätzliche Logik innerhalb Ihrer Triggerlogik verwenden, um das Szenario abzudecken, dass mehrere oder keine Zeilen von der Anweisung betroffen sind. (Trigger werden pro Anweisung ausgelöst, selbst wenn keine Zeilen betroffen sind.) Die betroffenen Daten werden in virtuellen Tabellen für die DML-Anweisungen (Data Manipulation Language, Datenbearbeitungssprache) gespeichert. Diese Tabellen können verknüpft werden, was Ihnen das Arbeiten mit den Daten ermöglicht.
• Trigger werden innerhalb Ihrer Transaktion synchron ausgeführt. Bedenken Sie dies stets, wenn Sie eine externe Anwendung aufrufen oder auf eine externe Ressource zugreifen möchten, bei der Sie nicht sicher sein können, dass die Rückgabe innerhalb kurzer oder angemessener Zeit erfolgt. Wenn Sie beispielsweise eine Update-Anweisung für eine Tabelle auslösen und innerhalb dieser Aktion ein Trigger ausgelöst wird, wird die Transaktion (das Implizite der Update-Anweisung) erst beendet, wenn die gesamte Logik des Triggers abgeschlossen ist. Wenn die externe Anwendung oder der externe Prozess einen Fehlercode zurückgibt, kann SQL Server die Transaktion abbrechen und Ihre Transaktion (in Abhängigkeit von der implementierten Fehlerbehandlung und vom Fehlercode) zurücksetzen. Wenn Sie innerhalb eines Triggers eine externe Aktion durchführen müssen und der Trigger für die Transaktion nicht entscheidend ist (oder nicht innerhalb desselben Bereichs ausgeführt werden muss), sollten Sie ihn daher auf einen anderen Prozess erweitern und Daten asynchron abrufen. Mit SQL Server 2005 wurde SQL Server Service Broker eingeführt, ein Feature, mit dem diese Vorgänge asynchron durchgeführt werden können.
• Ein Fehler, der von einer Anweisung innerhalb eines Triggers verursacht wird, lässt sich äußerst schwer finden. Wenn mehrere Tabellen an der Transaktion beteiligt sind, prüfen Sie im Fall eines Fehlers unbedingt die Trigger, und implementieren Sie eine ausreichende Fehlerbehandlung. Wenn Sie das Schema innerhalb Ihrer Datenbank ändern, stellen Sie sicher, dass Sie auch die Triggerlogik überwachen – andernfalls kann ein kleiner Trigger enorme Auswirkungen auf die Gesamtleistung und -stabilität haben. Die meisten Auswirkungen in Bezug auf Schemaänderungen können mit Visual Studio® for Database Professionals geprüft werden, indem beim Bearbeiten des Projekts automatische Schemaüberprüfungen durchgeführt werden und eine statische Codeanalyse vorgenommen wird, um auf Datentypinkonsistenzen zu prüfen.

– Jens K. Süßmeyer, Datenbankberater bei Microsoft

Paul S. Randal ist der leitende Direktor von SQLskills.com und ein SQL Server-MVP. Er war von 1999 bis 2007 im SQL Server-Speichermodulteam von Microsoft tätig. Paul Randal schrieb DBCC CHECKDB/repair für SQL Server 2005 und war bei der Entwicklung von SQL Server 2008 für das Kernspeichermodul zuständig. Als Experte für Notfallwiederherstellung, hohe Verfügbarkeit und Datenbankwartung ist Paul Randal ein regelmäßiger Referent bei Konferenzen. Sein Blog befindet sich unter SQLskills.com/blogs/paul.

© 2008 Microsoft Corporation und CMP Media, LLC. Alle Rechte vorbehalten. Die nicht genehmigte teilweise oder vollständige Vervielfältigung ist nicht zulässig.