SQL – Fragen und AntwortenE/A-Fehler, Datenbankspiegelung und mehr

Paul S. Randal

F: Ich führe seit kurzem regelmäßige Konsistenzprüfungen für meine verwalteten Datenbanken durch und habe sogar ein paar SQL Agent-Warnungen hinzugefügt, mit denen E/A-Fehler aus den Benutzerabfragen abgefangen werden. Ich weiß nicht, ob die Logik wirklich funktioniert, die ich um die Überprüfungen und die Warnungen herum implementiert habe, weil keine meiner Datenbanken eine Beschädigung aufweist. Wie kann ich Beschädigungen in einer Testdatenbank erzeugen, damit ich sicher sein kann, dass ich alles richtig eingerichtet habe? Gibt es noch mehr, woran ich E/A-Fehler erkennen kann?

A: Der alte Trick bei SQL Server® 2000, mit dem eine fehlerhafte Datenbank zu Testzwecken erstellt werden konnte, bestand darin, manuell eine Zeile aus der sysindexes-Tabelle in einer Testdatenbank zu löschen. Aber bei SQL Server 2005 ist die Beschädigung einer Systemtabelle auf diese Weise sehr schwierig. Die beste Möglichkeit, eine Testdatenbank zu beschädigen, ist ein Hex-Editor, mit dem Sie eine Datei bearbeiten können, während die Datenbank heruntergefahren ist. Gehen Sie wie folgt vor:

• Fahren Sie die Datenbank herunter, damit die Dateien nicht gesperrt sind. (Achten Sie aber darauf, die Datenbank nicht zu trennen, denn wenn Sie die falsche Seite beschädigen, können Sie sie u. U. nicht wieder anhängen.)
• Wählen Sie einen Offset aus, der mindestens 100 Seiten (mindestens 819.200 Byte) weit in der Datei auftritt, aber achten Sie darauf, dass der Offset an einer 8192-Byte-Begrenzung (einer Seitenbegrenzung) ausgerichtet ist. Damit gehen Sie kritischen Metadatenseiten und Zuordnungsbitmustern aus dem Weg, sodass Sie die Datenbank starten und DBCC CHECKDB ausführen können.
• Schreiben Sie am ausgewählten Offset einige leere Bytes (Nullen) in die Datei. Dieses Verfahren ist fast schon eine Garantie für einige Seitenheader-Beschädigungsfehler.

Nichtsdestoweniger gilt: Die schnellste Möglichkeit, eine fehlerhafte Testdatenbank zu erstellen, besteht in der Verwendung einer fehlerhaften Datenbank, die ein anderer Benutzer bereits erstellt hat. Beispiele für fehlerhafte SQL Server 2000- und SQL Server 2005-Datenbanken (mit Erklärungen) finden Sie in meinem Blog (unter go.microsoft.com/fwlink/?LinkId=115151).

Was Ihre zweite Frage angeht, wie Sie E/A-Fehler erkennen: Aktivieren Sie Seitenprüfsummen. Dieses Feature stand erstmals bei SQL Server 2005 zur Verfügung und sollte eine gesamte Datenbankseite vor Fehlern schützen, die vom E/A-Subsystem eingeführt wurden.

Wenn eine Seite auf einen Datenträger geschrieben wird, berechnet SQL Server abschließend eine Prüfsumme für die gesamte 8-KB-Seite, und die Prüfsumme wird auf der Seite eingetragen. Wird eine Seite vom Datenträger gelesen, die eine Seitenprüfsumme besitzt, wird die Prüfsumme neu berechnet und mit der auf der Seite gespeicherten Prüfsumme verglichen. Stimmen diese Werte nicht überein, wurde die Seite durch eine Ursache außerhalb von SQL Server beschädigt, und der Fehler 824 wird ausgelöst. Der Fehler wird für die Verbindung angezeigt, die das Lesen der Seite ausgelöst hat, und im SQL Server-Fehlerprotokoll und im Windows®-Anwendungsereignisprotokoll festgehalten.

Seitenprüfsummen sind standardmäßig für alle Datenbanken aktiviert, die unter SQL Server 2005 und SQL Server 2008 erstellt werden. Bei Datenbanken, die von früheren Versionen von SQL Server auf eine neuere Version aktualisiert wurden, müssen sie jedoch manuell aktiviert werden. Mit diesem Code aktivieren Sie die Seitenprüfsummen:

ALTER DATABASE mydb SET PAGE_VERIFY CHECKSUM;

Tipp: Ändern des SQL Server-Standardports

Standardmäßig ist Port 1433 der konfigurierte Port für SQL Server-Instanzen. Sobald dieser Port von einer Instanz verwendet wird, kann er nicht mehr von einer anderen Instanz genutzt werden. Wenn Sie also eine zweite (benannte) Instanz installieren, die das Netz mithilfe von tcp abhört, benötigt diese Instanz einen anderen Port. In einigen Fällen möchte der Administrator den Port aus Verschleierungsgründen ändern (obwohl die Verschleierung dabei eher gering ist und problemlos von einem Portscanner überwunden werden kann). In diesem Fall müssen Sie dann den Client natürlich so konfigurieren, dass er einen anderen Port verwendet. Hierzu gibt es drei übliche Verfahren.

Erstens: Angenommen, der Administrator hat den Port einer Instanz in 5555 geändert. Dann können Sie einfach die Portnummer der Instanz innerhalb des Namens des Computers angeben, zu dem eine Verbindung hergestellt werden soll, und zwar mit der Syntax „MeinServername,5555“. Ändert sich der Port erneut, müssen die Clients ihre connectionStrings ebenfalls ändern.

Eine andere Option besteht in der Verwendung von SQL Server-Aliasnamen, die auf dem Client konfiguriert werden. Neben dem Aliasnamen müssen auch der Servername, der Portname und das Protokoll angegeben werden. Ein einmal konfigurierter Alias kann wie ein Servername verwendet werden, um eine Verbindung zur Datenbankinstanz herzustellen. Der Vorteil bei dieser Option besteht darin, dass Änderungen der Serverkonfiguration von einem Domänenadministrator bereitgestellt werden können, weil die Einstellungen in der Registrierung gespeichert sind.

Die dritte Option für benannte Instanzen, in denen der Benutzer nur den Instanznamen kennt und den Namen mit der Syntax „Computername\Instanzname im ConnectionString“ angibt, ist der SQL Server-Browserdienst. Dieser Dienst ist bereits in SQL Server 2000 als Teil eines ausführenden Diensts implementiert. Bei SQL Server 2005 wurde der SQL Server-Browserdienst als separater Dienst implementiert. Neben der Instanzermittlung für den Computer beantwortet dieser Dienst auch eingehende UDP-Anforderungen (User Datagram-Protokoll) auf Port 1434 mit der entsprechenden Portnummer für die angeforderte Instanz, was eine Umleitung für den Client und die Unterstützung einer transparenten Verbindung ermöglicht.

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

F: Um die Fragmentierung in meiner Datenbank zu beseitigen, habe ich einen nächtlichen Wartungsplan eingerichtet, mit dem sämtliche Indizes in der Produktionsdatenbank neu erstellt werden. Die Produktionsdatenbank wird unter SQL Server 2005 Enterprise Edition mit SP2 ausgeführt. Ich habe bemerkt, dass die Datenbank hierdurch übermäßig stark anwächst. Deshalb habe ich einen weiteren Schritt eingefügt, mit dem diese zusätzlichen Leerräume verkleinert werden, weil es nicht viel Platz auf dem Datenträger gibt. Jetzt scheint der Neuerstellungsschritt nicht mehr zu funktionieren. Woran liegt das?

A: Sie sind auf ein Problem gestoßen, das beim Einrichten eines Wartungsplans häufig auftritt. Sie sind in einem Zyklus mit Jo-Jo-Effekt gefangen.

Beim Neuerstellen eines Index wird zuerst eine neue Kopie des Index erstellt, bevor der vorhandene Index gelöscht wird. Dieses Verfahren erfordert zusätzlichen Platz in den Datenbankdateien – meist ebenso viel Platz wie der eigentliche Index. In SQL Server 2000 war auch zusätzlicher Platz zum Sortieren der Indexzeilen erforderlich (etwa 20 Prozent der Indexgröße), aber diese Anforderung wurde durch eine einfache Indexneuerstellung in SQL Server 2005 ersetzt.

In einigen Fällen möchten die Administratoren den zusätzlichen Platz beseitigen, der durch die Neuerstellung des Index entstanden ist. Darum fügen sie in den Wartungsplan nach dem Neuerstellungsschritt einen Verkleinerungsvorgang ein. Leider ist nicht allgemein bekannt, dass dieser Verkleinerungsvorgang aufgrund der Art des Algorithmus zu einer Indexfragmentierung führt. Dies bedeutet, dass der neu erstellte und defragmentierte Index sofort wieder fragmentiert wird, was die positive Wirkung der Neuerstellung zunichte macht.

In Anbetracht der Tatsache, dass die Datenbankdatei bei der nächsten Indexneuerstellung weiter wachsen wird, ist es besser, den zusätzlichen Platz in der Datenbank zu belassen und ganz auf den Verkleinerungsvorgang zu verzichten. (Darüber hinaus führt das dauernde Vergrößern und Verkleinern der Datenbankdateien zu Dateifragmentierung auf der Betriebssystemebene, was auf dieselbe Weise wie die Indexfragmentierung zu Leistungseinbußen führen kann.)

Eine weitere Möglichkeit ist die Verlängerung der Intervalle, in denen Sie die Indizes neu erstellen. Oder probieren Sie einmal eine alternative Methode aus, z. B. das alte DBCC INDEXDEFRAG, das ich für SQL Server 2000 geschrieben hatte, oder die neuere ALTER INDEX REORGANIZE-Syntax in SQL Server 2005 und SQL Server 2008.

Es gibt ein nützliches Whitepaper, in dem die Indexfragmentierung erläutert wird und das Anhaltspunkte für den richtigen Zeitpunkt liefert, zu dem die Fragmentierung beseitigt werden sollte (unter go.microsoft.com/fwlink/?LinkId=115154). Dieser Artikel wurde für SQL Server 2000 geschrieben, aber die Konzepte sind immer noch gültig.

F: Wir haben die Strategie zur Notfallwiederherstellung in meinem Unternehmen evaluiert, und ich denke, dass die Datenbankspiegelung der richtige Weg für unsere Situation sein könnte. Auf dem Server, den ich zu schützen versuche, befinden sich viele nicht zusammenhängende Datenbanken (das Ergebnis eines früheren Serverkonsolidierungsprojekts), und ich möchte die Datenbankspiegelung für alle diese Datenbanken verwenden. Ich beschäftige mich nun mit der Frage, wie viele Datenbanken ich spiegeln kann, bevor die Leistung zurückgeht.

A: Die Antwort auf diese Frage ist eine, die ich immer wieder gebe: Das kommt ganz darauf an. Laut den veröffentlichten Richtlinien sollten nicht mehr als 10 Datenbanken pro Instanz gespiegelt werden, aber diese Zahl ist nur eine grobe Schätzung des Maximalwerts für die meisten Benutzer. Berücksichtigen Sie die folgenden Faktoren beim Hardwaresetup:

• Wie viel Speicher benötigen die Prinzipal- und die Spiegelinstanz? (Im Idealfall sollte dieser Wert identisch sein.)
• Wie viel Verarbeitungsleistung benötigen die Prinzipal- und die Spiegelinstanz? (Auch dieser Wert sollte gleich sein.)
• Wie viel Bandbreite besitzt das E/A-Subsystem auf der Spiegelinstanz? (Dieser Wert sollte identisch mit dem Wert auf der Prinzipalinstanz sein.)
• Wie viele Transaktionsprotokolleinträge werden jeweils durch die Arbeitsauslastung der Datenbanken erzeugt?
• Wie viel Netzwerkbandbreite ist zwischen der Prinzipalinstanz und der Spiegelinstanz verfügbar?

Die letzten beiden Faktoren sind am wichtigsten. Wenn die verfügbare Netzwerkbandbreite zwischen den beiden Instanzen nicht ausreicht, um die kombinierte Anzahl der erzeugten Transaktionsprotokolleinträge pro Sekunde für alle zu spiegelnden Datenbanken zu bewältigen, fällt die Leistung der Prinzipaldatenbanken ab. SQL Server 2008 trägt dazu bei, diese Belastung mithilfe der Protokolldatenstromkomprimierung zu verringern.

Der zweitwichtigste Aspekt sind die Speicher- und Threadanforderungen für das Spiegeln. Jede gespiegelte Datenbank benötigt einen Thread und dazu eine gewisse Speichermenge. Bei Servern mit geringerer Leistung führt eine große Anzahl gespiegelter Datenbanken zusammen mit der normalen Arbeitsauslastung eventuell zu einer zu starken Auslastung des Servers.

Überlegen Sie auch, wie die Datenbankspiegelung durchgeführt werden soll. Im synchronen Modus können Transaktionen auf der Prinzipaldatenbank erst dann einsetzen, wenn alle Transaktionsprotokolldatensätze in das Transaktionsprotokoll der gespiegelten Datenbank kopiert wurden. Deshalb könnten jegliche Verzögerungen durch ein überlastetes Netzwerk bei der Prinzipaldatenbank ein Leistungsproblem bezüglich der Arbeitsauslastung verursachen.

Im asynchronen Modus können Transaktionen auf der Prinzipaldatenbank ohne Wartezeit einsetzen, aber eine Netzwerkverzögerung könnte die Menge der Transaktionsprotokolleinträge, die an die Spiegelung gesendet werden sollen, anwachsen lassen. Dies kann Probleme mit der Transaktionsprotokollgröße verursachen. Aber es kommt noch schlimmer: Bei einem Ausfall oder einem Fehler können nicht gesendete Transaktionsprotokolle verloren gehen. Je mehr Transaktionsprotokolleinträge also noch nicht gesendet wurden, desto größer ist das Risiko von Datenverlusten bei einer Wiederherstellungssituation.

Die Szenarios können sich beträchtlich voneinander unterscheiden, und ich habe einige interessante Beispiele in realen Produktionsumgebungen gesehen. Beispielsweise habe ich eine Umgebung mit 150 Datenbanken gesehen, die alle nur äußerst geringe Aktivitäten aufwiesen, die zudem nicht alle gleichzeitig auftraten. Alle 150 Datenbanken wurden problemlos gespiegelt.

Als Gegenbeispiel sei ein Setup genannt, in dem nur drei stark ausgelastete Datenbanken arbeiteten, das jedoch ohne gute Netzwerkverbindung. In diesem Szenario konnte gerade mal eine einzige Datenbank gespiegelt werden, bevor der Mangel der Netzwerkbandbreite zu einem Rückgang der Arbeitsauslastung führte.

Der Schlüssel zum Erfolg besteht darin, zuerst den Umfang der erzeugten Protokolle zu berechnen. Wenn es scheint, dass die verfügbare Netzwerkbandbreite die Anzahl der zu spiegelnden Datenbanken unterstützen wird, dürfte alles gut gehen. Testen Sie Ihre Konfiguration, bevor Sie sie in der Produktionsumgebung einführen, und achten Sie darauf, alle Vorgänge aufzunehmen, bei denen Einträge für das Transaktionsprotokoll erzeugt werden könnten. Dies gilt insbesondere für sämtliche Arbeiten im Rahmen der Datenbankwartung.

Paul S. Randal ist der leitende Direktor von SQLskills.com und ein SQL Server-MVP. Paul Randal war von 1999 bis 2007 im SQL Server-Speichermodulteam von Microsoft tätig. Er 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.