SQL – Fragen und AntwortenSicherungskomprimierung, Clientumleitung mit Spiegelung und mehr

Paul S. Randal

F Wir werden die meisten unserer Server auf SQL Server 2008 aktualisieren. Dabei ist eines der Features, bei denen ich es kaum erwarten kann, sie einzusetzen, die Sicherungskomprimierung. Mir ist bewusst, dass ich sie standardmäßig für alle Datenbanken auf jedem Server einschalten kann, aber ich habe auch gehört, dass es besser wäre, dies nicht zu tun. Ich bin nicht sicher, warum ich das Feature nicht standardmäßig aktivieren sollte, da es scheint, als ob ich dabei nichts zu verlieren hätte. Können Sie erklären, was hinter dem steckt, was ich gehört habe?

A Die Antwort darauf lautet wie so oft: Das kommt ganz darauf an! Zunächst möchte ich zur Erklärung ein paar Hintergrundinformationen bereitstellen.

Der wichtigste Punkt, der in Betracht gezogen werden muss, ist das Komprimierungsverhältnis, das jede Datenbanksicherung haben wird, wenn die Sicherungskomprimierung aktiviert ist. Das Komprimierungsverhältnis von allem, was durch einen Algorithmus komprimiert wird, wird von der Art der Daten bestimmt, die komprimiert werden.

Zufällige Daten (z. B. kleine Ganzzahlen) werden nicht so gut komprimiert werden können. Deshalb werden größtenteils die Tabellen- und Indexinhalte in der Datenbank das erreichbare Komprimierungsverhältnis bestimmen.

Hier sind einige Beispiele dafür, wann Sicherungskomprimierung kein hohes Komprimierungsverhältnis bieten kann:

• Wenn für die Datenbank transparente Datenverschlüsselung aktiviert ist, ist das Komprimierungsverhältnis sehr niedrig, da Daten, die komprimiert werden, sehr niedrige zufällige Zahlen sind.
• Wenn die meisten Daten in der Datenbank auf Spaltenebene verschlüsselt sind, wird das Komprimierungsverhältnis niedrig sein, da die Spaltenverschlüsselung wiederum die Daten mehr oder weniger randomisiert.
• Wenn für die meisten Tabellen in der Datenbank Datenkomprimierung aktiviert ist, wird das Komprimierungsverhältnis niedrig sein. Daten zu komprimieren, die meist bereits komprimiert sind, hat normalerweise nur geringe Auswirkungen.

Wenn das Komprimierungsverhältnis niedrig ist, ist das Problem nicht das niedrige Verhältnis, sondern die Tatsache, dass CPU-Ressourcen dafür verwendet werden, den Komprimierungsalgorithmus auszuführen, ohne dass dies einen Nutzen hat. Unabhängig davon, wie gut ein Teil der Daten komprimiert werden kann, werden CPU-Ressourcen immer verwendet, um Komprimierungs- und Dekomprimierungsalgorithmen auszuführen.

Dies bedeutet, dass Sie prüfen müssen, wie gut jede Datenbank in einer Sicherung komprimiert wird, bevor entschieden wird, Sicherungskomprimierung für diese Datenbank permanent zu verwenden. Andernfalls verschwenden Sie möglicherweise CPU-Ressourcen. Dies ist die Grundlage dafür, was Sie gehört haben.

Zusammenfassend bleibt zu sagen, dass, wenn die Mehrheit der Datenbanken von Sicherungskomprimierung profitiert, es sinnvoll ist, Sicherungskomprimierung auf Serverebene zu aktivieren und einige Sicherungsaufträge manuell zu ändern, um speziell die Option WITH NO_COMPRESSION zu verwenden. Wenn die Mehrheit der Datenbanken von Sicherungskomprimierung nicht profitieret, ist es hingegen sinnvoll, die Sicherungskomprimierung auf Serverebene deaktiviert zu lassen und einige Sicherungsaufträge manuell zu ändern, um speziell die Option WITH COMPRESSION zu verwenden.

F Im letzten Jahr haben wir unsere Datenbanken so aktualisiert, dass uns Datenbankspiegelung zur Verfügung steht, damit, wenn ein Fehler auftritt, ein Failover zur Spiegeldatenbank vorgenommen und die Anwendung fortgesetzt werden kann. Als wir das System entwarfen, hatten wir Datenbankfailovers getestet, und alles funktionierte ordnungsgemäß. Letzte Woche hatten wir einen echten Fehler, und ein Datenbankfailover fand statt, aber alle Anwendungstransaktionen wurden beendet, und die Anwendung konnte keine Verbindung zum Failoverserver mehr herstellen. Wie kann ich SQL Server zukünftig so einrichten, dass es die Anwendungsverbindungen während des Failovers nicht trennt, sodass die Transaktionen fortgesetzt werden können?

A Lassen Sie mich dies in zwei Teile aufgliedern – wie können Anwendungen Failovers bewältigen, und wie kann Clientumleitung mit Datenbankspiegelung verwaltet werden.

Wenn sich ein Failover bei Verwendung einer Hochverfügbarkeitstechnologie ereignet, die mit SQL Server verfügbar ist, wird die Clientverbindung zum ausgefallenen Server getrennt, und alle nicht abgeschlossenen Transaktionen gehen verloren. Es ist nicht möglich, eine nicht abgeschlossene Transaktion zwischen mehreren Servern (in einer Failoversituation oder anderweitig) zu migrieren. In Abhängigkeit von der Hochverfügbarkeitstechnologie, wird die nicht abgeschlossene Transaktion entweder überhaupt nicht auf dem Failoverserver vorhanden sein, oder sie existiert als nicht abgeschlossene Transaktion, für die aber im Rahmen des Prozesses, die Datenbank auf dem Failoverserver online zu schalten, ein Rollback durchgeführt wird.

Im Hinblick auf die Datenbankspiegelung, die fortwährend Transaktionsprotokoll-Datensätze vom Hauptserver zum Spiegelserver sendet – meist ist Letzteres der Fall –, wird für alle nicht abgeschlossenen Transaktionen, wenn die Spiegeldatenbank als neuer Prinzipal online geschaltet wird, ein Rollback durchgeführt.

Deshalb gibt es zwei Dinge, die eine Anwendung problemlos bewältigen muss, wenn sie auf einem Server ausgeführt wird, wobei die Durchführung eines Failovers zu einem anderen Server notwendig werden kann:

1. Sie muss problemlos mit der getrennten Serververbindung umgehen können und muss anschließend versuchen, nach einem kurzen Zeitintervall die Verbindung wiederherzustellen.
2. Sie muss problemlos mit einer abgebrochenen Transaktion umgehen können und anschließend versuchen, die Transaktion wieder auszuführen, nachdem eine Verbindung mit dem Failoverserver eingerichtet wurde (eventuell unter Verwendung eines Transaktionsmanagers mittlerer Stufe).

Die einzige Hochverfügbarkeitstechnologie, die hier keine speziellen Clientänderungen erfordert, um eine Umleitung der Clientverbindung nach einem Failover zu ermöglichen, ist das Failoverclustering. Clients stellen eine Verbindung zum Namen eines virtuellen Servers her und werden transparent zu einem der gerade aktiven physischen Clusterknoten umgeleitet.

Bei Hochverfügbarkeitstechnologien wie z. B. Protokollversand und Replikation ist der Servername des Failoverservers anders, was bedeutet, dass eine manuelle Umleitung der Clientverbindungen nach einem Failover erforderlich ist. Diese manuelle Umleitung kann auf mehrere Weisen durchgeführt werden:

• Sie können den Failoverservernamen in den Client hartkodieren, damit die Verbindungswiederherstellungsversuche an den Failoverserver gehen.
• Sie können Netzwerklastenausgleich mit einer 100/0—0/100-Konfiguration verwenden, die ermöglicht, dass die Verbindung zum Failoverserver wechselt.
• Sie können etwa einen Serveraliasnamen oder Wechseleinträge in einer DNS-Tabelle verwenden.

Bei Datenbankspiegelung funktionieren alle diese Optionen. Datenbankspiegelung verfügt aber auch über integrierte Clientleitungsfunktionen. Die Clientverbindungszeichenfolge kann den Namen des Spiegelservers explizit angeben, und wenn keine Verbindung zum Prinzipalserver hergestellt werden kann, wird ein automatischer Kontaktaufbau zum Spiegelserver versucht. Dieser Prozess ist als explizite Umleitung bekannt.

Wenn die Clientverbindungszeichenfolge nicht geändert werden kann, ist die implizite Umleitung möglich, wenn der ausgefallene Server nun als Spiegelserver ausgeführt wird. Alle Verbindungen zu ihm werden automatisch zum neuen Prinzipal umgeleitet, aber dies funktioniert nur, wenn der Spiegelserver ausgeführt wird.

Im Whitepaper zu SQL Server 2005 Implementieren von Anwendungsfailover mit Datenbankspiegelung werden diese Optionen ausführlicher erklärt.

F Als wir auf SQL Server 2005 aktualisierten, haben wir unsere großen Tabellen so für das Partitionieren umgestaltet, dass wir partitionierte Wartung und variable Fenster nutzen konnten. Sie haben dies in der Ausgabe vom August 2008 (Partitionierung, Konsistenzprüfungen und mehr) beschrieben. Aber wir stießen aber auf ein Problem. Gelegentlich werden gleichzeitige Anwendungsabfragen über die gesamte Tabelle hinweg gesperrt, sogar wenn die Abfragen nicht auf dieselben Partitionen zugreifen. Ich habe gehört, dass mit SQL Server 2008 dieses Problem behoben wurde. Können Sie bitte erklären, wie ich diese Sperren verhindern kann?

Abbildung 1 Untersuchen von Sperren in einer partitionierten Tabelle

A Das Problem, das Sie sehen, wird von einem Mechanismus namens „Sperrenausweitung“ verursacht. Mit SQL Server werden Daten gesperrt, um sie zu schützen, während sie durch eine Abfrage gelesen oder geschrieben werden. Es können ganze Tabellen, Datendateiseiten oder einzelne Tabellen-/Indexzeilen gesperrt werden, wobei jede Sperre ein wenig Speicherplatz benötigt.

Wenn eine Abfrage dazu führt, dass zu viele Sperren verhängt werden müssen, kann SQL Server entscheiden, alle Sperren für Zeilen oder Seiten in einer Tabelle durch eine einzige Sperre für die gesamte Tabelle zu ersetzen (der Schwellenwert liegt dabei ungefähr bei 5000 Sperren, aber der genaue Algorithmus ist kompliziert und konfigurierbar). Dieser Prozess wird Sperrenausweitung genannt.

Wenn in SQL Server 2005 die Abfrage A für eine einzige Partition einer Tabelle ausgeführt wird und genug Sperren verursacht, sodass eine Sperrenausweitung ausgelöst werden kann, wird die gesamte Tabelle gesperrt. Dies kann die Abfrage B daran hindern, für eine anderen Partition derselben Tabelle ausgeführt zu werden. Deshalb wird die Abfrage B gesperrt, bis Abfrage A abgeschlossen ist und alle zugehörigen Sperren aufgehoben sind.

In SQL Server 2008 ist der Sperrenausweitungsmechanismus verbessert worden, damit in einer Tabelle eine Sperrenausweitung auf Partitionsebene möglich ist. Im oben beschriebenen Beispiel bedeutet dies, dass die Sperrenausweitung, die von der Abfrage A verursacht wird, nur die von der Abfrage A verwendete Partition sperren würde, und nicht die gesamte Tabelle.

Abfrage B kann dann für eine andere Partition ausgeführt werden, ohne dass sie gesperrt wird. Abfrage B könnte sogar selbst die Sperrenausweitung auslösen, die anschließend nur die Partition sperren würde, für die die Abfrage B ausgeführt wird, und nicht die gesamte Tabelle.

Dieses Sperrenausweitungsmodell kann unter Verwendung der folgenden Syntax festgelegt werden:

ALTER TABLE MyTable SET (LOCK_ESCALATION = AUTO);
GO

Diese Syntax weist den SQL Server-Sperren-Manager an, Sperrenausweitung auf Partitionsebene zu verwenden, wenn die Tabelle partitioniert ist, und reguläre Sperrenausweitung auf Tabellenebene, wenn die Tabelle nicht partitioniert ist. Das Standardverhalten ist, Sperrenausweitung auf Tabellenebene zu verwenden. Beim Festlegen dieser Option sollte aber mit Umsicht vorgegangen werden, da es in Abhängigkeit vom Verhalten Ihrer Abfragen zu Deadlocks kommen kann.

Wenn die Abfragen A und B zum Beispiel Sperrenausweitung für verschiedene Partitionen einer Tabelle verursachen, anschließend aber beide versuchen, auf die Partition zuzugreifen, die von der jeweils anderen Abfrage gesperrt wurde, wird eine der Abfragen vom Deadlocküberprüfungsprozess abgebrochen.

Die Abbildung zeigt ein Beispiel für das Abfragen der sys.partitions-Systemkatalogsicht (der erste Satz von Ergebnissen) und der sys.dm_os_locks-DMV (der zweite Satz von Ergebnissen) zum Untersuchen der Sperren, die für Abfragen einer partitionierten Tabelle aufrechterhalten werden, wobei die Sperrenausweitung auf Partitionsebene erfolgte. In diesem Fall gibt es zwei exklusive Sperren auf Partitionsebene (die HOBT-Sperren in der Ausgabe), aber die Tabellensperren (die OBJECT-Sperren in der Ausgabe) sind nicht exklusiv, und somit können mehrere Abfragen auf Partitionen zugreifen, obwohl eine Sperrenausweitung erfolgt ist. Beachten Sie, dass die Ressourcen-IDs dieser beiden Partitionssperren den Partitions-IDs der ersten zwei Partitionen der Tabelle in der Ausgabe von sys.partitions entsprechen.

Anfang dieses Jahres habe ich in einem Blog ein Beispielskript vorgestellt, in dem ich die Funktionsweise der Sperrenausweitung auf Partitionsebene sowie das Potential für Deadlocks demonstrierte. In der SQL Server 2008-Onlinedokumentation finden Sie unter Sperren im Datenbankmodul eine detaillierte Erklärung aller Aspekte des Sperrens in SQL Server 2008.

F Bei einem unserer Server gab es einige Probleme mit dem Datenträger, auf dem das Transaktionsprotokoll für eine Datenbank gespeichert ist, und die Datenbank wurde fehlerverdächtig. Die letzte vollständige Sicherung fand vor fünf Wochen statt, und es hätte viel zu lange gedauert, alle Protokollsicherungen wiederherzustellen. Das Problem trat außerhalb der Arbeitszeit auf, weshalb wir das unterbrochene Transaktionsprotokoll neu erstellten, um Ausfallzeiten zu vermeiden. Unter bestimmten Umständen könnte dies Probleme verursachen. Wenn aber kein Zugriff auf die Daten stattfand, denke ich, sind wir sicher. Haben wir richtig gehandelt?

A Einfach gesagt, ist die einzige Zeit, zu der ich die Neuerstellung eines Transaktionsprotokolls in Betracht ziehen würde, die Zeit, wenn es unmöglich ist, die Wiederherstellung über Sicherungen vorzunehmen. Obwohl Sie sich der Gefahren, ein Transaktionsprotokoll neu zu erstellen, bewusst sind (für die Leser, die es nicht sind, empfiehlt es sich, meinen Blogbeitrag Last resorts that people try first... zu lesen), bedeutet die Tatsache, dass die Datenbank fehlerverdächtig wurde, dass die Wiederherstellung fehlschlug – entweder bei der Ausführung der Notfallwiederherstellung oder während eines Rollback für eine Transaktion. Dies bedeutet, dass wirklich die Möglichkeit einer Datenbeschädigung in der Datenbank besteht.

Haben Sie, auch wenn das Problem außerhalb der Arbeitszeit auftrat, geplante Aufträge und Hintergrundaufgaben in Betracht gezogen? Als das Protokoll beschädigt wurde, könnte ein Wartungsauftrag ausgeführt worden sein, der einen gruppierten Index neu erstellte oder neu organisierte. Eine Hintergrundaufgabe könnte eine Schattenkopienbereinigung auf Seiten in einem Heap- oder Clusterindex ausgeführt haben. Beide Aktionen könnten Änderungen an gruppierten Indexstrukturen vorgenommen haben, und dies würde, wenn kein ordnungsgemäßes Rollback durchgeführt wird, zur Beschädigung der Datenbank und einem möglichen Datenverlust führen.

Das Fazit ist, dass die Neuerstellung eines Transaktionsprotokolls immer die absolut letzte Option in jedem Notfallwiederherstellungsszenario sein sollte, da äußerst leicht noch mehr Beschädigungen und Datenverluste verursacht werden können. Zumindest sollten Sie ein vollständiges DBCC CHECKDB für diese Datenbank ausführen, um zu prüfen, ob eine Beschädigung vorliegt.

Künftig sollten Sie Ihre Sicherungsstrategie ändern, damit Sie rechtzeitig Wiederherstellungen durchführen können und nicht zu drastischen Maßnahmen greifen müssen, wie z. B. zur Neuerstellung des Transaktionsprotokolls. Die Schritte zum Entwerfen einer Sicherungsstrategie gehen weit über den Rahmen dieses Artikels hinaus, aber ich plane, dieses Thema irgendwann im kommenden Jahr in einem eigenen Artikel ausführlich zu behandeln. Bleiben Sie also dran!

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 weltweit. Sein Blog befindet sich unter SQLskills.com/blogs/paul.