Fragen und Antworten zu SQL: Verschieben, Leistungsoptimierung, Sichern und Spiegeln von Datenbanken

  • Artikel

Paul S. Randal

Neuer Array-Umzugstermin

Frage: Bei unserem aktuellen RAID ist der Speicherplatz schneller erschöpft, und daher müssen wir einige SQL Server 2005-Datenbanken daraus verschieben. Das neue Array steht bereit, und ich bereite das Verschieben der Datenbanken vor. Nun habe ich entdeckt, dass eine dieser Datenbanken ein Transaktionsreplikationsverleger ist, und das bedeutet meines Wissens, dass ich die Datenbank nicht verschieben kann. Was soll ich tun?

Antwort: Es gibt gute Neuigkeiten: nur SQL Server 2000 (und frühere Versionen) verfügten über die Einschränkung, die bewirkte, dass eine Verlegerdatenbank nicht ohne eine Neuinitialisierung der Transaktionsreplikation oder eine direkte Änderung der verschiedenen Systemtabellen verschoben werden konnte.

Bei SQL Server 2005 und SQL Server 2008 gibt es einen dokumentierten Prozess, der es erlaubt, eine Datenbank zu verschieben, ohne sich um die Transaktionsreplikation kümmern zu müssen, sofern die Datenbank derselben SQL Server-Instanz zugeordnet bleibt. Sie müssen eine kurze Ausfallzeit in Kauf nehmen, da es keine Möglichkeit gibt, eine Datenbankdatei, die noch online ist, zu verschieben. Gehen Sie folgendermaßen vor:

Zuerst versetzen Sie die Datenbank mit dem nachstehenden Code in den Offlinestatus. Falls Benutzer mit der Datenbank verbunden sind, müssen Sie diese Verbindungen zuerst trennen, da der Vorgang sonst nicht ausgeführt werden kann:           

ALTER DATABASE MyDatabaseName SET OFFLINE;

Danach kopieren Sie die Datendateien an den neuen Speicherort. Kopieren Sie die Daten, statt sie zu verschieben, damit Sie sie schnell wiederherstellen können, falls etwas misslingt (andernfalls müssen Sie eine Systemwiederherstellung durchführen). Dann teilen SQL Server mit dem folgenden Code die neuen Speicherorte der einzelnen Dateien mit:

ALTER DATABASE MyDatabaseName 
MODIFY FILE
   (NAME = N'LogicalFileName',
   FILENAME = N'pathname\filename');

Nachdem Sie alle Dateien physisch an einen anderen Speicherort kopiert und ihren Speicherort in SQL Server geändert haben, versetzen Sie die Datenbank mit diesem Code wieder in den Onlinestatus:

ALTER DATABASE MyDatabaseName SET ONLINE;

Schließen des Seitenlatch

Frage: Ich verstehe einige Konzepte im Zusammenhang mit der Leistungsoptimierung nicht ganz. Ich habe verschiedentlich gelesen, ich müsse "Seitenlatch"-Probleme vermeiden. Ich weiß weder, was hier mit "Seite" gemeint ist, noch was "Latch" ist oder warum ein Seitenlatch überhaupt ein Problem darstellen sollte. Können Sie das erklären?

Antwort: Sämtliche Daten einer SQL Server-Datenbank werden in Dateien gespeichert. Intern sind diese Dateien in Folgen von 8 KB großen Blöcken unterteilt, die Seiten genannt werden. Eine Seite ist eine grundlegende Speicher- und E/A-Einheit, die SQL Server verwalten kann. Die Seiten befinden sich in der Regel in den Datendateien auf dem Datenträger, und bevor Abfragen ausgeführt werden können, müssen sie in den Cache von SQL Server (auch Pufferpool genannt) eingelesen werden.

In SQL Server werden verschiedene Arten von Seiten zum Speichern verschiedener Arten von relationalen Daten (wie Tabellenzeilen, Zeilen eines nicht gruppierten Index oder Text/LOB-Daten) verwendet. Es gibt auch Seiten, in denen Teile interner Datenstrukturen gespeichert werden, die SQL Server benötigt, um die Seiten mit relationalen Daten organisieren und darauf zugreifen zu können.

Ein Latch ist ein einfacher interner Mechanismus, der in SQL Server verwendet wird, um den Zugriff auf eine Seite im Cache zu synchronisieren. Es gibt zwei Arten von Seitenlatches, auf die Sie achten müssen: gewöhnliche Seitenlatches und Seiten-E/A-Latches. Wenn ein SQL Server-Thread warten muss, bis er einen diese Latches erhält, dann ist dies ein Anzeichen für ein Leistungsproblem.

Wenn SQL Server darauf wartet, dass ein Teil einer Datendatei vom Datenträger gelesen wird, kann dies einen Seiten-E-/A-Latchwartevorgang verursachen. Nimmt ein Seiten-E-/A-Latch übermäßig viel Zeit in Anspruch, dann ist dies in der Regel ein Anzeigen für ein Leistungsproblem beim zugrunde liegenden Datenträgersubsystem (d. h. es ist überlastet).

Wenn mehrere Threads in SQL Server auf dieselbe 8 KB große Datendateiseite im Speicher zuzugreifen versuchen und Konflikte bezüglich des Zugriffs auf die Seite auftreten, dann kann dies einen Seitenlatch-Wartevorgang verursachen. Meist tritt dies auf, wenn die kleinen temporären Objekte der tempdb-Datenbank häufig verwendet werden.

Eine eingehende Erläuterung dazu, wie Seitenlatch-Wartevorgänge überwacht und verringert werden können, würde den Rahmen dieses Artikels sprengen, in folgenden Quellen finden Sie jedoch weitere Informationen:

Sichten von Datenbanksnapshots

Frage: Ich habe gerade Datenbanksnapshots entdeckt. Nun ziehe ich sie als Alternative zum Wiederherstellungsmodell der vollständigen Sicherungen und Protokollsicherungen in Betracht. Ich erstelle etwa jede Stunde einen Snapshot, und wenn etwas nicht stimmt, kann ich die beschädigten Daten zurückziehen. Das ist anscheinend viel problemloser und eine viel schnellere Wiederherstellungsmethode. Ist diese Umstellung möglicherweise problematisch?

Antwort: Ja, Datenbanksnapshots sind weder ein praktischer noch ein brauchbarer Ersatz für eine umfassende Notfallwiederherstellungsstrategie. Ein Datenbanksnapshot bietet im Hinblick auf eine Notfallwiederherstellung nicht dieselben Möglichkeiten wie eine Transaktionsprotokollsicherung. Der Datenbanksnapshot enthält nicht Kopien aller Seiten der Datenbank, sondern nur Kopien derjenigen Seiten, die seit ihrer Erstellung geändert wurden. Wenn die Datenbank auf irgendeine Weise beschädigt wird, ist der Datenbanksnapshot ohne die zugrunde liegende Datenbank daher nutzlos. Er ist einfach eine Sammlung unterschiedlicher Seiten aus der Datenbank und kann nicht zur Wiederherstellung verwendet werden.

Mit dem Datenbanksnapshot können Sie Daten zurückholen, die versehentlich aus der Datenbank gelöscht wurden, sofern die Datenbank an sich noch verfügbar ist. Wenn eine aus der Datenbank gelöschte Tabelle beispielsweise im Snapshot noch vorhanden ist, können Sie diese gelöschte Tabelle anhand des Snapshots wiederherstellen.

Davon abgesehen ist es wegen der potenziellen Leistungsprobleme nicht empfehlenswert, zu viele Snapshots einer Datenbank (als Ersatz für eine halbstündige Transaktionsprotokollsicherung) zu erstellen. Bevor Sie eine Datenbankseite austauschen können (siehe die Erläuterung in der Antwort im Abschnitt "Schließen des Seitenlatch"), müssen Sie die betreffende Seite synchron in alle vorhandenen Datenbanksnapshots kopieren, die noch keine Version dieser Seite enthalten. Je mehr Datenbanksnapshots erstellt werden, desto mehr Kopien der Seite müssen erstellt werden, und dadurch wird die Leistung beeinträchtigt.

Ein weiterer Grund, der gegen die Erstellung zu vieler Datenbanksnapshots spricht, ist die Tatsache, dass jeder Snapshot Kopien der ursprünglichen Datenbankseiten enthält. Je mehr von der Datenbank geändert wird, desto größer werden diese Snapshots. Dies kann zu Problemen mit dem Festplattenspeicherplatz und der Leistung führen.

Datenbanksnapshots sind nicht als Ersatz für häufige Protokollsicherungen vorgesehen. Sie finden eine eingehende Studie der Auswirkungen, die Datenbanksnapshots auf das Leistungsverhalten haben können, im Whitepaper “Database Snapshot Performance Considerations Under I/O-Intensive Workloads (Leistungsgesichtspunkte von Datenbanksnapshots bei E/A-intensiver Arbeitsauslastung).”

Wenn Sie ein Wiederherstellungsmodell mit vollständigen Sicherungen und Transaktionsprotokollsicherungen verwenden, dann möchten Sie offensichtlich in der Lage sein, sämtliche Daten bis zu dem Zeitpunkt, an dem der Notfall aufgetreten ist, wiederherzustellen und/oder Zeitpunktwiederherstellungen durchzuführen. (Eine Erläuterung dieser Verfahren finden Sie in meinen Artikeln vom Juli 2009 und November 2009 unter "Grundlagen von SQL Server-Sicherungen" bzw. "SQL Server: Wiederherstellen von Daten mithilfe von Datensicherungen".)

Spieglein, Spieglein

Frage: Ich wurde gebeten, eine Datenbankspiegelung für unsere Datenbank anzulegen, aber ich fürchte, dass diese Datenbankspiegelung unser Problem nicht lösen wird. Wir hatten einige Probleme mit Datenbeschädigungen im SAN, und daher soll die Datenbankspiegelung uns vor Beschädigungen schützen. Aber werden Beschädigungen nicht automatisch in die Spiegelung übertragen? Wie kann die Datenbankspiegelung zur Lösung dieses Problems beitragen?

Antwort: Das ist ein Problem, das zu vielen Missverständnissen führt. Es scheint, als ob jede Technologie, die redundante Kopien einer Datenbank erstellt, dafür anfällig sei, Beschädigungen der Prinzipaldatenbank in der Spiegeldatenbank (um die Begriffe der Datenbankspiegelung zu verwenden) fortzupflanzen, in Wirklichkeit ist dies aber nicht der Fall.

Der springende Punkt ist hier zu verstehen, wie die Spiegeldatenbank verwaltet wird. Beschädigungen würden sicherlich in die Spiegelung weitergegeben, wenn mit dem zugrunde liegenden Synchronisierungsmechanismus ganze Datenbankseiten von der Prinzipal- in die Spiegeldatenbank kopiert würden. Beschädigte Seiten der Prinzipaldatenbank würden dann in die Spiegelung eingefügt.

Bei der Datenbankspiegelung wird dies jedoch ausdrücklich vermieden, weil nicht Datenbankseiten von einer Datenbank in eine andere Datenbank kopiert werden. Bei der Datenbankspiegelung werden Transaktionsprotokolldatensätze von der Prinzipal- in die Spiegeldatenbank kopiert. Transaktionsprotokolldatensätze beschreiben physische Änderungen, die an Datenbankseiten vorgenommen wurden, und die eigentlichen Seiten sind darin nicht enthalten. (Eine umfassende Erläuterung von Transaktionsprotokolldatensätzen, Protokollierung und Wiederherstellung finden Sie in meinem Artikel vom Februar 2009: "Erläuterungen zu Protokollierung und Wiederherstellung in SQL Server.")

Selbst wenn eine Datenbankseite durch das zugrunde liegende E/A-Subsystem der Prinzipaldatenbank beschädigt wird, kann diese Beschädigung nicht direkt in die Spiegeldatenbank übertragen werden. Im schlimmsten möglichen Fall erkennt SQL Server die beschädigte Seite nicht (weil die Seitenprüfsummen nicht überprüft werden), und es wird ein falscher Spaltenwert zur Berechnung eines in der Datenbank gespeicherten Werts verwendet. Das resultierende falsche Ergebnis würde in die Spiegeldatenbank übertragen – eine Beschädigung zweiter Ordnung.  Wie bereits erwähnt, wenn Seitenprüfsummen aktiviert sind, bliebe eine solche Beschädigung unerkannt, wenn die Seite vom Datenträger gelesen wird, und diese Beschädigung zweiter Ordnung würde nicht auftreten.

Dieses Verhalten erklärt auch, warum eine Konsistenzprüfung der Prinzipaldatenbank keine Informationen über die Konsistenz der Spiegeldatenbank liefert und umgekehrt. Es sind zwei getrennte Datenbanken, die miteinander synchronisiert werden, indem Beschreibungen der physischen Datenbankänderungen und nicht die eigentlichen Datenbankseiten übermittelt werden.

Anmerkung des Redakteurs: Unser Dank gilt Kimberly L. Tripp von SQLskills.com für die technische Rezension des Artikels in diesem Monat.

Paul S. Randalist Geschäftsführer von SQLskills.com, einem Microsoft Regional Director und 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. Paul Randal ist Experte für Notfallwiederherstellung, hohe Verfügbarkeit und Datenbankwartung und regelmäßiger Referent bei Konferenzen in aller Welt. Er führt einen Blog unter SQLskills.com/blogs/paul. Sie finden ihn in Twitter unter Twitter.com/PaulRandal.

Verwandter Inhalt