Frank Kalis

Zweiter Beitrag in der "Best Practice" Serie. Gleichzeitig ein Thema, wo große Unsicherheit herrscht. Aber gerade DBA's sollten das Konzept des Transaktionsprotokolls unbedingt verstehen.

Dieser Beitrag geht auf ein Posting von Jonathan van Houtte zurück, das er freundlicherweise für diesen Zweck hier noch einmal überarbeitet hat. Jonathan hat gebeten, darauf aufmerksam zu machen, daß das Thema nicht komplett ist. Trotzdem ist es jetzt schon sehr ausführlich.

Empfehlungen für Log Files für OLTP Datenbanken:

1: OLTP Produktionsdatenbanken sollten so gut wie nie die Datenbankoption "trunc. log on chkpt." verwenden. 
Die einzigen Vorteile dieser Einstellung sind:

• daß man sich keine Gedanken über die Wartung (Backup) des Transaktionsprotokolls zu machen braucht
• daß die "Gefahr", kein Speicherplatz mehr zur Verfügung zu haben, weniger wahrscheinlich wird

Nachteile dieser Einstellung sind

• Schlechtere Performance aufgrund des laufenden Truncate Prozesses
• keine Point in time Wiederherstellungsmöglichkeit
• keine Möglichkeit, den Zustand bis zum Zeitpunkt des Fehlers wiederherzustellen

Trifft man die Entscheidung, daß eine Wiederherstellung des letzten vollständigen oder differentiellen Backup's ausreichend ist, dann gibt man Erfahrung zugunsten von Bequemlichkeit auf.

2: Wird das Transaktionsprotokoll nicht gesondert gesichert, kann es auch nicht verkleinert werden. Ein vollständiges oder differentielles Backup verkleinert nicht das Transaktionsprotokoll.

3: Transaktionsprotokoll Backup's sind losgelöst von anderen Backup's und können unabhängig davon wiederhergestellt werden. Angenommen, Sie führen ein vollständiges Backup jede Nacht durch und sichern das Transaktionsprotokoll zweimal im Laufe des Tages. Finden Sie jetzt heraus, daß das letzte nächtliche Backup korrupt ist, können Sie das aus der Nacht zuvor wiederherstellen (WITH NORECOVERY!) und anschliessend die Log Backup's wiederherstellen, um einen möglichst aktuellen Stand zu erreichen.

4: Das erste, was Sie in einem Disaster Fall machen sollten, ist, versuchen das Transaktionsprotokoll zu sichern. DBA's, die ich unterrichte, werden darauf gedrillt.

5: Verwenden Sie SQL Server 7 und setzen die Einstellung "select into/bulkcopy" auf Wahr, um beispielsweise Daten aus einer Textdatei schnell zu laden, sollten Sie direkt danach die Einstellung wieder auf Falsch setzen, das Transaktionsprotokoll und dann die Datenbank sichern. SQL Server 2000 and höher können die Änderungen mit ihrem Bulk-Logged Recovery Model wiederherstellen. Allerdings nicht auf der einzelnen Vorgangsebene. Die Verwendung dieses Modells und Durchführung einer Bulk-Logged Operation verhindert Point in time Wiederherstellung (mit der dafür notwendigen Transaktionsprotokollsicherung) und limitiert Ihre Möglichkeiten, auf eine Korrumpierung Ihrer Datenbankdateien zu reagieren. Speichern Sie in der Datenbank unternehmenskritische dynamische Daten, sollten Sie sich genau überlegen, ob Sie die Fehlertoleranz des Full Recovery Models aufgeben zugunsten der Performance des Bulk-Logged Modells. Bedenken Sie dabei auch, daß auch SELECT INTO, CREATE INDEX, WRITETEXT und UPDATETEXT minimal protokollierte Operationen sind, genauso wie die offensichtlicheren BULK COPY und bcp.

6: Führen Sie die Transaktionsprotokollsicherung vor einem vollständigen oder differentiellen Backup durch statt danach. Sichern Sie das Transaktionsprotokoll nach der Datenbank, müssen Sie dieses Log Backup zusätzlich wiederherstellen, wenn Sie das Datenbankbackup wiederherstellen.

7: Verkleinern Sie nicht das Transaktionsprotokoll nur um des Verkleinerns willen. Die Protokolldateien sollten so dimensioniert sein, daß sie die maximale Menge an protokollierten Informationen zwischen zwei Backup Vorgängen aufnehmen können. Bestehen Sie auf der Verkleinerung, werden die Dateien vergrößert, wenn dies notwendig ist. Dies wiederum geht auf die Performance des Gesamtsystems, da Prozessorleistung für den Autogrow Vorgang benötigt wird und führt ferner zu Dateifragmentierung. (Gleiches gilt übrigens auch für Datendateien. Erwarten Sie, daß die Datenbank auf eine Größe von 20 GB anwachsen wird, sollten Sie auch diesen Speicherplatz direkt bei Erstellung der Datenbank allokieren).

8: Vergewissern Sie sich, daß Sie in einem Disasterfall in der Lage sind, eine Datenbank auf den aktuellsten Stand wiederherzustellen. Erstellen Sie eine Testdatenbank und warten diese exakt so, wie Ihre Produktionsdatenbanken. Führen Sie dann eine Notfallübung durch, indem Sie simulieren, daß die Hardware mit den Datendateien mit einem Fehler ausfällt. (Was ist das Erste, was Sie in einem solchen Fall machen? Punkt 4!). Überprüfen Sie Ihre Bandsicherungen und Wiederherstellungpläne wenigstens vierteljährlich, bzw, immer dann, wenn sich etwas in Ihrem Backup Prozess verändert. Sie können sich nur dann sicher sein, daß Ihre Backup funktionieren, wenn Sie sie ausprobieren und weiderherstellen; und sicherlich wollen Sie das nicht erst auf die harte Tour herausfinden. Üben Sie ebenso die Point in time Wiederherstellung. Ich bin dafür bekannt, die WHERE Klausel im DELETE Statement anzugeben.

9: Festplatten und andere Hardware werden irgendwann ausfallen. Die Frage ist nicht, ob, sondern, wann.

10: Sichern Sie die Datenbankdateien (mdf, ndf und ldf) erst dann, wenn Sie vorher die Datenbank mit sp_detach offline genommen haben oder den SQL Server Dienst gestoppt haben. Diese "Backup's" sind nicht verwendbar mit differentiellen oder Transaktionsprotokollbackup's. Auch wenn Sie später SQL Server verwenden, um sie zu sichern und mit No RECOVERY wiederherzustellen. Müssen Sie ein Backup Utility ohne nativen SQL Server Support verwenden, verwenden Sie SQL Server, um die Datenbanken und Transaktionsprotokolle auf Festplatte zu sichern (aber bitte auf einer separaten physikalischen Platte!) und verwenden anschließend Ihr Backup Utility, um diese Backup Dateien zu sichern. Sichern Sie auf einen anderen Server, beachten Sie bitte, daß der Service Account mit dem SQL Server oder der SQL Server Agent gestartet wird, ein Domänen User sein muß, der auch auf dem Remote Server angelegt sein muß. Zusätzlich sollte ein RESTORE VERFIYONLY angewendet werden, wenn auf einen Remote Server gesichert wird.

11: Löschen oder überschreiben Sie kein Backup, solange Sie nicht wenigstens zwei voneinander unabhängige Wiederherstellungsalternativen haben (siehe Punkt 3:).

12: Logging läßt sich nicht abschalten. Es ist ein integraler Bestandteil von SQL Server und garantiert die ACID Eigenschaften des Systems.

13: Verwenden Sie einen Write-Cache Disk Controller, stellen Sie sicher, daß sein Speicher redundant ist und batteriebetrieben. Lernen Sie, wie man seinen Speicher im Falle eines Disasters wiederherstellt. Deaktivieren Sie Caching auf den Platten selbst.

14: Stellen Sie sicher, daß die Transaktionsprotokolle auf redundanten physikalischen Platten sind und nicht auf dem gleichen Array wie die Daten. RAID 1 ist ideal für Transaktionsprotokolle, da ihr typischer I/O (im Gegensatz zu den Datendateien) sequentiell ist. Haben Sie mehrere Datenbanken gleichzeitig in Benutzung, kann es sinnvoll sein, jedes Transaktionsprotokoll auf einem eigenen RAID1 Platten Paar zu haben; anderenfalls könnte auf dem sequentiellen I/O ein random I/O werden.

Durch den DBCC PAGE Befehl kann man sich den Inhalt jeder Daten- und Indexseite in SQL Server ansehen. Die Verwendung des Befehls ist aber mit einigen Stolperfallen verbunden, die im folgenden erklärt werden sollen. Beispielhaft verwenden wir mal die PUBS Datenbank.

Ganze Geschichte »

Dies ist der Anfang einer Reihe von "Best Practice" Beiträgen. Mehr noch als bei anderen Beiträgen würde ich mir hier Feedback wünschen, das dann in den Beitrag einfließen kann. Ziel soll es sein, einen Rahmen zu bilden mit allgemein anerkannten und zugestimmten Richtlinien, Empfehlungen, Tips. Aus diesem Anspruch heraus wird deutlich, daß dies weder ich, noch irgendjemand anderes allein auf die Beine bringen kann; vielmehr muss dies eine Teamarbeit einer Community werden. Ich mache jetzt einmal lediglich den Anfang mit ein paar Richtlinien zur Indexerstellung.

Ganze Geschichte »

Nun, so etwas wie Set < variable > = Nothing oder ein Äquivalent in einer anderen Programmiersprache gibt es in T-SQL nicht. T-SQL Variablen sind nur lokal im Batch oder in einer Gespeicherten Prozedur gültig, in der sie definiert wurden. Wird der Batch oder die Prozedur beendet, existiert auch die Variable nicht mehr. Beispiel (Query Analyzer):

Ganze Geschichte »

Nun, grundsätzlich würde ich wahrscheinlich eher zu einem anderen Datentyp als VARCHAR tendieren, um IP Adressen zu speichern, aber dennoch fand ich diese "Koproduktion" der beiden SQL Server MVP's Steve Kass und Itzik Ben-Gan in der englischen Newsgroup sehr interessant. Folgender Lösungsansatz kam von Steve Kass

Ganze Geschichte »

Dies ist ein beliebtes Beispiel für Informatikstudenten im Anfangsstadium, um die Auswirkungen effizienter Algorithmen zu demonstrieren. Also auch hier nicht unbedingt etwas, was man zwingend in einer Datenbank machen müßte, das sich aber durchaus mengenbasiert lösen läßt. Zu diesem Algorithmus gibt es eine kleinen Anekdote:

Dem "Entdecker" Carl-Friedrich Gauß wurde in der Schule die Aufgabe gestellt, die Summe aller Zahlen von 1 bis 100 zu berechnen. Alle Kinder rechneten los, Gauß schrieb kurz etwas auf seine Tafel und riss nach kurzer Zeit seinen Lehrer aus dessen Ruhepause. Die Formel stimmte natürlich! Wer es genauer nachlesen möchte, kann sich mal hier umschauen.

DECLARE @n BIGINT
SET @n = 100
SELECT (@n+@n*@n)/2
                     
-------------------- 
5050

(1 row(s) affected)

oder als UDF

CREATE FUNCTION dbo.achtsieben(@n BIGINT) 
RETURNS BIGINT
	AS
		BEGIN
    	RETURN (@n+@n*@n)/2
		END
GO
SELECT dbo.achtsieben(100)
DROP FUNCTION dbo.achtsieben
                     
-------------------- 
5050

(1 row(s) affected)

Zu dem Namen, den ich der Funktion gegeben habe, gibt es auch eine Anekdote:

Als ich unsere Mathematiker nach der "richtigen" Bezeichnung für diese Formel gefragt habe, kam als Antwort:

"Wir nennen das die 78-er Regel, da die Summe der Monate eines Jahres 78 ist."

Ein kurzer Test ergibt:

DECLARE @n BIGINT
SET @n = 12
SELECT (@n+@n*@n)/2
                     
-------------------- 
78

(1 row(s) affected)

Stimmt!

Nachtrag 27.08.2004: Auch im SQL Server kann man damit die Notwendigkeit zum Einsatz von effizienten Algorithmen demonstrieren. Dazu bauen wir uns mal folgendes Testskript zusammen:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
DECLARE @start DATETIME
SET @start = GETDATE()
DECLARE @n BIGINT 
SET @n = 200000 
SELECT (@n+@n*@n)/2
SELECT GETDATE()-@start AS Zeit
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
DECLARE @start DATETIME
DECLARE @n BIGINT 
DECLARE @result BIGINT 
SET @start = GETDATE()
SET @n = 1
SET @result = 0
WHILE @n <= 200000
	BEGIN
		SET @result = @result + @n
		SET @n = @n + 1
	END
SELECT @result
SELECT GETDATE()-@start AS Zeit

Nach Ausführung erhält man folgendes Ergebnis:

...
                     
-------------------- 
20000100000

(1 row(s) affected)

Zeit                                                   
------------------------------------------------------ 
1900-01-01 00:00:00.010

(1 row(s) affected)

...
                     
-------------------- 
20000100000

(1 row(s) affected)

Zeit                                                   
------------------------------------------------------ 
1900-01-01 00:00:01.513

(1 row(s) affected)

Während der Gauß Algorithmus fast augenblicklich das Ergebnis zurückliefern, braucht die iterative Methode deutlich länger!

Vielleicht mag das nicht viel erscheinen, aber jetzt stelle ich mir die Auswirkungen auf ein System vor, in dem ein solcher algorithmus in einer stark frequentierten Prozedur implementiert wurde, die mehrere Tausend Male pro Stunde aufgerufen wird. Nun sieht die Sache schon etwas anders aus. Jetzt mag man natürlich mit Caching argumentieren, aber in diesem Fall wird die Ausführung ohne

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

zwischen zwei Läufen der iterativen Methode auch nicht wirklich schneller

Zeit                                                   
------------------------------------------------------ 
1900-01-01 00:00:01.403

(1 row(s) affected)

Dies ist natürlich kein repräsentativer Test unter sterilen Testbedingungen, aber verdeutlicht doch die Notwendigkeit effizienter Algorithmen, auch, und vielleicht gerade, in T-SQL. 

Normalerweise würde man solche Fragestellungen. welcher Dezimalzahl nun 1011001 entspricht, damit beantworten, in dem man auf den Client verweist. Was aber, wenn man einfach wissen will, wie so etwas in T-SQL aussehen könnte? Ob man es dann später einsetzt, ist ja eine andere Sache:

Ganze Geschichte »

SQL Server MVP Steve Kass hat dieses Beispiel in den englischen Newsgroups gepostet. Es zeigt, daß der Einsatz der Datentypen zur Speicherung monetärer Daten sorgfältig durchdacht sein sollte. Man sollte stets bedenken, welche Operationen mit diesen Daten durchgeführt werden.

Ganze Geschichte »