SQL – Fragen und Antworten: Notfallwiederherstellung und Datenbankspiegelung

  • Artikel

Was Sicherungen, Notfallwiederherstellung und Datenbankspiegelung anbelangt, gibt es endlose Variationen, die alle für endlos viele Szenarios geeignet sind.

Paul S. Randal

Temporäre Lösung

F: Ich habe gelesen, dass viele widersprüchliche Ratschläge über wie viele Dateien auf meinem Server konfigurieren sollte ich für Tempdb zu PAGELATCH Konflikte verringern. Können Sie alle diese beleuchten?

**A.**Du bist richtig – es gibt viele schlechte Ratschläge gibt über die Tempdb-Konfiguration. PAGELATCH Behauptung in Tempdb kommt von Arbeitslasten wo viele gleichzeitige Verbindungen erstellen und Löschen von kleine Tabellen. Diese Vorgänge erfordern zuordnen und freigeben Datei Datenseiten in Tempdb. Dies erfordert wiederum exklusiven Zugriff auf Speicherreservierung in Bitmap-Daten-Datei-Seiten (spezielle Seiten, die machen, beachten Sie die Datendatei Seiten verwendet werden).

Wenn es viele gleichzeitige Verbindungen versuchen gibt, gleichzeitig zuordnen und freigeben, kann nur eine Verbindung auf eine Zuteilung Bitmap auf einmal zugreifen. Dies führt zu Konflikten und eine Verringerung der Leistung.

Eine Möglichkeit zur Linderung ein wenig diese Behauptung ist, aktivieren Sie das Ablaufverfolgungsflag 1118 (erfahren Sie mehr über diese auf meinem Blog SQLskills.com). Eine effektivere Möglichkeit ist es, mehrere Tempdb-Datendateien erstellen. Wenn Sie mehrere Datendateien erstellen, führt SQL Server Zuweisungen (und De-allocations) Round Robin über die Datendateien. Auf diese Weise nimmt die Zahl der Zuteilung Bitmaps (mindestens eine pro Datei) und die gesamte System-Behauptung sinkt.

Die Frage ist: Wie viele Dateien sollten Sie erstellen? Für die längste Zeit, die beste Ratschläge, die Menschen geben konnte war, dass die offizielle Microsoft-Haltung der Schaffung einer Tempdb-Daten für jeden logischen Prozessor-Kern-Datei (z. B. zwei CPUs mit jeweils vier Kernen und Hyperthreading aktiviert gleich acht logische Kerne) war falsch. Dieser Ansatz kann auf Servern mit mehr als acht Cores zu mitlaufen mit Speicher Leckagen führen. Eine andere verbreitete Überzeugung war, dass ein Viertel bis die Hälfte der Anzahl der Prozessorkerne ab ein guter Anfang war.

Dann präsentierte auf der SQL PASS Summit Konferenz Ende 2011, Bob Ward von Microsoft Product Support Services eine elegante Formel für die Bestimmung der Anzahl der Dateien, die Sie erstellen, sollte. Wenn Ihr Server weniger als acht logische Kerne hat, verwenden Sie die Anzahl der logischen Kernen als die Anzahl der Tempdb-Datendateien. Wenn Ihr Server mehr als acht logische Kerne hat, mit acht Tempdb-Datendateien beginnen Sie und dann fügen Sie vier weitere gleichzeitig hinzu, wenn Konflikte weiter.

Halten Sie im Verstand das ist generalisierte Beratung. Es gibt mindestens drei Gelegenheiten wo Server mit 64 Prozessorkerne 128 Tempdb-Datendateien benötigen — zweimal die Anzahl der Kerne — Konflikte zu lindern. Auf jeden Fall wird Ihre tatsächliche Laufleistung variieren.

Der perfekte Plan

F: Ich vor kurzem überprüft unser Disaster Recovery-Pläne und fand, dass wir keine regelmäßige Sicherungen unsere Systemdatenbanken machst. Raten Sie, dies zu tun? Was ist das Schlimmste, die was passieren könnte, wenn wir es nicht tun?

**A.**Es ist eine gute Idee, Ihre Disaster Recovery-Pläne regelmäßig zu überprüfen. Es ist sogar besser, diese Pläne zu üben. Eines der Dinge, die Sie entdeckt haben würde, wenn Sie durch eine Praxis-bare-Metal-Wiederherstellung ausgeführt ist, dass die SQL Server-Umgebung auf volle Funktionalität, zurückkommen haben würde nicht weil Sie Systemdatenbanken fehlen würde.

Viele DBAs die Systemdatenbanken (Master, Model, Msdb und Replikation Verteilungsdatenbanken) wann halte nicht planen oder ein Desaster-Recovery-Verfahren zu testen. Dies ist ein großer Fehler. Diese Datenbanken sind entscheidend für die Instanzen von SQL Server. Sie müssen diese schützen und ihre Integrität zu überprüfen, ebenso wie Sie mit Ihrem Benutzer-Datenbanken zu tun.

Es gibt keinen Sinn, dass Ihre Daten zur Verfügung, wenn Sie keine Verbindung zu einer Instanz von SQL Server herstellen können.

Das gleiche gilt, wenn Sie die Instanz in einem funktionsfähigen Zustand bringen können nicht, wenn der Meister fehlt, weil Sie nicht alle erforderlichen Anmeldeinformationen. Ohne Sicherung des Meisters betrachten Sie die Login-Informationen für alle Datenbanken neu erstellen, bevor die Anwendungen online geschaltet werden können.

Es ist entscheidend für die Msdb-Datenbank sichern, da es alle Ihre SQL-Agent-Aufträge (z.B. Sicherungen und Konsistenzprüfungen), enthält SQL Agent-Warnungen (z. B. hohem Schweregrad Fehler und Frühwarnungen , dass Ihre Subsystem e/A schief läuft), SSIS-Pakete und Ihre Tabellen mit Sicherungsverläufen. Haben Sie jede Art von automatisierten System, die einen Satz generiert der Wiederherstellung Datenbank Anweisungen einfach zu erleichtern Disaster-Recovery, es ist wahrscheinlich verwenden die Tabellen mit Sicherungsverläufen in Msdb um zu tun. Ohne eine Kopie von Msdb (wenn die Katastrophe Ihren gesamten Subsystem der e/A nahm) müssten Sie die RESTORE-Anweisungen Stück zusammen mit der hand ist die mühsame Arbeit, die Ausfallzeiten hinzufügt.

Die Model-Datenbank ist kritisch, wenn Sie mit einer Konfiguration kommen haben Sie auf alle neuen Datenbanken replizieren möchten. Beispielsweise haben Sie eine Umgebung wo jeder gehosteten Client verfügt über eine eigene Datenbank, müssten Sie das Modell. Ohne ihn müssten Sie wieder die Konfigurationsoptionen festlegen.

Replikation Verteilungsdatenbanken sind wichtig zum Wiederherstellen von Daten-Streams Replikation ohne zeitraubende Re-initializations der Abonnierbare Datenbanken durchführen zu müssen. Insgesamt haben Sie keine Disaster-Recovery-Strategie, wenn Sie Ihre Systemdatenbanken sowie der Benutzerdatenbanken sichern sind.

Um Ihnen den Einstieg zu erhalten, überprüfen Sie heraus diese SQL Server-Onlinedokumentation über System-Datenbank-Backup und Wiederherstellung:

Wachsen, wachsen, wachsen

F: Haben wir Schwierigkeiten zu verstehen, ein Problem wo wächst das Transaktionsprotokoll, obwohl wir es manuell nach unten schrumpfen. Verpflichtet wir sind, die Arbeit in unserem inneren Transaktionen und leistungsstarke Protokollsicherungen, also warum das Protokoll weiter wachsen?

**A.**Das Problem scheint zu sein, dass Ihre Entwickler verwenden geschachtelte Transaktionen im Code, ohne zu wissen, dass sie nicht die Art und Weise Verhalten, wie, die Sie aussehen. Ein Beispiel-Code-Flow, der zeigt, was du tust ist:

BEGIN TRAN; Do some work … BEGIN TRAN; Do some more work … COMMIT TRAN Continue with more work …

Die zweite BEGIN TRAN, die eine geschachtelte Transaktion beginnt, wirklich startet keine Schwachstelle, was das Speichermodul anbelangt. Alles, was sie tut ist die Schrittweite @@ TRANCOUNT um 1. Es gibt nichts in der Transaktion Protokoll angibt, dass eine neue Transaktion gestartet hat geschrieben. Alle die Arbeit, die von der geschachtelten Transaktion ist eigentlich Teil der ursprünglichen Transaktion.

Das bedeutet, wenn die COMMIT TRAN für die geschachtelte Transaktion ausgegeben wird, passiert nichts außer dem Dekrementieren die @@ TRANCOUNT, weil es nicht wirklich eine geschachtelte Transaktion. Nichts hat sich verpflichtet, bis die erste Transaktion Commits, @@ TRANCOUNT zurück auf NULL zu bringen. Deshalb das Transaktionsprotokoll wächst. Sie haben noch eine einzelne, lang andauernde Transaktion.

Außerdem sollten nicht Sie reguläre Transaktion Protokoll Verkleinerungsvorgänge durchführen. Wenn das Transaktionsprotokoll hat zu wachsen, hat der neue Teil des Protokolls auf 0 (null) initialisiert werden. Es ist überschrieben mit Nullen in was vorher der Teil des NTFS-Datenträgers war. Dies geschieht, so dass alle nachfolgenden Absturz-Wiederherstellungsvorgang fehlschlagen nicht (siehe meinem SQLskills.com-Blog eine Erklärung).

Während der neue Teil des Transaktionsprotokolls NULL initialisiert ist, wird alle Protokollaktivität für diese Datenbank angehalten. Ihre Arbeit wird vorübergehend beendet. Diese Pause könnte ziemlich lang, wenn Sie den Transaktionsbetrag Protokoll automatische Vergrößerung ganz groß eingestellt haben.

Es ist immer besser, das Transaktionsprotokoll müssend automatische Vergrößerung möglichst zu vermeiden. Wenn das Transaktionsprotokoll wächst wieder jedes Mal, wenn Sie es verkleinern, lassen Sie es in Ruhe. Es sollte offensichtlich größer als die Größe sein, denen Sie es verkleinern sind.

Spiegel

F: Wir haben nur implementiert, Datenbankspiegelung und gefunden, dass wir nicht mehr Index-Umbauten für einige unserer Tabellen durchgeführt werden können. Die riesige Menge des Transaktionsprotokolls erstellt Überladungen, die unser Netzwerk und die Datenbankspiegelung verlangsamt. Warum geschieht dies und wie können wir es umgehen?

**A.**Dieses Problem wird von vielen angetroffen, die Datenbankspiegelung umsetzen. Es ergibt sich aus der Tatsache, dass Leistung und Zuverlässigkeit testen mit der Datenbankspiegelung getan Leben beinhalten nicht die normalen Datenbankpflege.

Viele Menschen nutzen das massenprotokollierte Wiederherstellungsmodell, wenn Index Neuerstellung Operationen ausführen. Dies begrenzt die Menge des Transaktionsprotokolls generiert, sodass das Transaktionsprotokoll nicht während der Operation wächst. Datenbankspiegelung erlaubt nur das vollständige Wiederherstellungsmodell, wo Index Neuerstellung Operationen vollständig protokolliert werden. Dann erzeugen sie soviel Transaktion Log-Volume als die Größe des Index neu erstellt wird.

Die Höhe der zusätzlichen Transaktionsprotokoll-Datensätze als Performance-Index in das vollständige Wiederherstellungsmodell Umbauten könnte wirklich groß sein und die Netzwerkverbindung zwischen den Haupt- und gespiegelte Datenbankservern zu sättigen. In diesem Fall kann eine Warteschlange für die Prinzipaldatenbank aufzubauen. Dies könnte Transaktion Verarbeitung für jede gleichzeitige Anwendung Arbeitsauslastung Verzögerungen.

Das bedeutet, dass für viele Menschen, Index Neuerstellung Operationen möglich nicht, wenn Sie die Datenbankspiegelung verwenden. Dies gilt auch bei der Protokolldatenstroms, die im Lieferumfang von SQL Server 2008 und späteren Versionen der Datenbankspiegelung.

Eine alternative Index Instandhaltungsstrategie ist, verwenden Sie ALTER INDEX... REORGANISIEREN Sie statt ALTER INDEX... NEU ERSTELLEN. Das Neuorganisieren eines Indexes werden nur vorhandene Indexfragmentierung. Sie können es ohne Verlust von bereits abgeschlossenen Arbeit unterbrechen. Neuerstellen ein Index, baut auf der anderen Seite, immer einen neuen Index unabhängig vom Umfang der Fragmentierung. Wenn Sie es unterbrechen, bekommen Sie nichts. Alles wird zurückgesetzt.

Für größere Indizes sind nicht so praktisch, um neu zu erstellen, führen Sie die folgenden Schritte aus:

  • **Tag eins:**Starten Sie ALTER INDEX... Während Ihr Wartungsfenster zu REORGANISIEREN. Für eine Stunde oder so laufen lassen. Den Befehl zu töten. Es wird nicht alles zurücksetzen und Sie werde haben einige Fortschritte im Hinblick auf das Beseitigen der Fragmentierung aus dem Index.
  • **Tag 2:**Starten Sie die wieder neu ordnen. Er erinnert sich nicht an Tag eins, sondern sollte die Arbeit, die am ersten Tag und beseitigen der Fragmentierung von den nächsten Teil des Index Start schnell durchlaufen. Nach einer Stunde oder so wieder zu töten.
  • **Nach Tag zwei:**Wiederholen bis die Fragmentierung Ebene Tropfen unterhalb welcher Sie festgelegt haben, oder nur dem Fortfahren von Tag auf unbestimmte Zeit.

Dadurch können Sie die Begrenzung des Transaktionsprotokolls generiert (und daher mit der Datenbankspiegelung übertragen) durch Ihre reguläre Indexwartung. Wollen Sie mehr erweiterte, statt töten den Prozess neu ordnen nach einer gewissen Zeit, können Sie überwachen, wie viel Transaktionsprotokoll generiert wird und es zu töten, sobald er eine bestimmte Schwelle erreicht (siehe meinem SQLskills.com-Blog für weitere Details).

Paul S. Randal

Paul S. Randal ist der Geschäftsführer von SQLskills.com, Microsoft regional Director und SQL Server MVP. Er arbeitete an der SQL Server-Speicher-Engine-Team bei Microsoft von 1999 bis 2007. Er schrieb DBCC CHECKDB/Repair für SQL Server 2005 und war verantwortlich für das Kernspeichermodul während der Entwicklung von SQL Server 2008. Paul Randal ist Experte für Notfallwiederherstellung, hohe Verfügbarkeit und Datenbankwartung und regelmäßiger Referent bei Konferenzen in aller Welt. Er Blogs auf SQLskills.com/blogs/paul, und Sie finden ihn auf Twitter bei twitter.com/PaulRandal.

Verwandter Inhalt