Glorf IT

In der Serie “Vorschläge zur Datensicherung mit SQL-Server” gab es bisher folgende Beiträge:

1. Teil – Informationen zum Umfeld und zum Verständnis
2. Teil – Vorgehen beim Offline-Backup
3. Teil – Online-Vollsicherung
4. Teil – differentielle bzw. inkrementelle Online-Sicherung

In diesem letzten – längst überfälligen – Artikel aus der Serie stelle ich die “Snapshot”-Sicherung vor. Es gibt zwei Arten einen Datenbank-Snapshot zu erzeugen, mir geht es primär um die zweite:

• Eine Online-Kopie im laufenden Betrieb, die einen "Snapshot" der Datenbank erstellt. Dazu werden interne Mechanismen des SQL-Servers verwendet.
• Ein Backup-Programm zieht im laufenden Betrieb eine Kopie der Datenbank-Dateien. Das Backup-Programm nutzt dazu den "Volume Shadow Copy Service" (VSS), der sich mit dem SQL-Server koordiniert.

SQL-Server-Snapshot-Backup

Ab der Version 2005 kann die Enterprise-Edition des SQL-Servers im laufenden Betrieb eine Kopie der Datenbank ziehen, die dann für rein lesende Zugriffe genutzt werden kann. Das ist natürlich auch so eine Art von Sicherung, die aber dann doch auf der gleichen Kiste gespeichert wird, wie das Original. Daher fällt das für mich eher in die Kategorie "schnelle Verfügbarkeit".

Wer sich für das Thema interessiert dem empfehle ich den Artikel "SQL Server 2005 Snapshots" von Andrew Calvett oder SQL Server 2005: When and how to use Database Snapshots von Greg Robidoux.

Volume Shadow Copy Service (VSS)

Im laufenden Betrieb können Datenbank-Dateien auf Datei-Ebene nicht kopiert werden. Die Dateien sind vom SQL-Server exklusiv geöffnet. Daher kann eine normale Sicherung die Dateien nicht kopieren. Das führt in der Vergangenheit dazu, dass die Datenbank-Dateien gerne vergessen wurden. Ich erlebe es leider regelmäßig, dass Kunden zwar fleißig die Bänder wechseln, aber nicht das Sicherungslog ansehen und daher gar nicht wissen, dass deren SQL-Server-Datenbanken nicht gesichert werden konnten. Bis zu mir oder meinen Kollegen kommen die Kunden übrigens nur dann, wenn deren Datenbanken defekt sind. Dann ist es natürlich schon zu spät…

Um dem Kunden das Leben zu erleichtern führte Microsoft mit Windows 2003 den schicke "Volume Shadow Copy Service" (VSS) ein. Dieser Dienst ermöglicht das Kopieren von geöffneten Dateien der SQL-Server-Datenbanken. Dazu muss man in der Regel nichts weiter tun, das Bindeglied zwischen dem SQL-Server und dem VSS kommt bereits bei der Installation mit: der VSS-Writer. Jede moderne Sicherungssoftware sollte das Feature bereits unterstützen.

Der interne Ablauf ist etwa so: Die Sicherungsoftware wendet sich an den VSS und sagt, dass bitte alle Dienste (die das unterstützen) die Daten sichern sollen. Der VSS schaut daraufhin seine Liste der registrierten "VSS-Writer" durch und benachrichtigt sie. Der VSS-Writer des SQL-Servers weist den SQL-Server an eine Kopie der Datenbanken zu erstellen. Das geht vergleichsweise schnell, weil genaugenommen nur die Unterschiede in den Dateien (eine Art DIFF) seit der letzten Sicherung rausgeschrieben werden. Intern speichert der VSS in seinen "shadow copy cache" maximal 64 Snapshots, aber auch nur, wenn auf dem Laufwerk genug Platz bereitgestellt wurde.

Die genauen Abläufe werden sehr detailliert im MSDN-Artikel "SQL Writer in SQL Server 2005: A Guide for SQL Server Backup Application Vendors" beschrieben. Das Handbuch ist auch ganz gut.

Vorteile

• Diese Methoden sind sehr schnell.
• Diese Methoden ermöglichen einen 7×24-Stunden-Betrieb.
• Jede normale Sicherungssoftware unterstützt das Verfahren.
• Die Rücksicherung ist einfach.

Risiken und Nebenwirkungen

• Für diese Sicherungsmethode muss man erst mal den VSS konfigurieren.
• Man benötigt Windows Server 2003.
• Der SQL-Server muss so konfiguriert sein, dass jeder im System-Kontext laufende Dienst SysAdmin-Rechte hat. Das ist allerdings nach der Default-Installation sowieso der Fall und stört vermutlich nur die wenigen, die besonders viel Wert auf Sicherheit legen.

Mein persönliches Resümee:

Diese Sicherungsmethode eignet sich vor allem für professionelle Nutzer, die viel Wert auf eine aktuelle Datensicherung legen. Man kann damit den Rund-um-die-Uhr-Betrieb realisieren und kann den Standard-Ablauf der gängigen Datensicherungssoftware nutzen.

Beim letzten Regionaltreffen der PASS-Franken erwähnte Robert das schicke Tool "Log Parser" von Microsoft. Microsoft nennt es das "schweizer Taschenmesser" unter den Werkzeugen. Damit kann man mit einer SQL-artigen Syntax das Eventlog auswerten. Hier ein Beispiel:

C:\>LogParser "SELECT TimeGenerated, SourceName,
EventCategoryName, Message INTO report.txt FROM Security WHERE
EventID = 528 AND SID LIKE '%TESTUSER%'" -resolveSIDs:ON

Weitere Beispiele finden sich hier.

Anbei noch ein paar Links, die zum größten Teil von Klaus Oberdalhoff stammen (danke!):

• Homepage des Tools im ScriptCenter "Log Parser 2.2"
• Grafische Oberfläche für den Log Parser
• Bedienungshinweise von der University of Delaware Police
• Buch: "Microsoft Log Parser Toolkit" (16$)
• TechNet Webcast: IIS Data Mining with Log Parser 2.X – Level 300 – April 2004
• TechNet Webcast: Efficient Data Mining with Log Parser 2.2 (Level 300) – October 15, 2007

Heute schickte mir ein Kollege eine ganze Reihe von SQL-Statements und bat um Unterstützung. Dabei handelte es sich um vergleichsweise komplizierten Code mit mehreren Joins, derived-Tables usw. Besonders interessant war, dass er pro SQL-Statement genau eine Zeile benötigte.

Nun bin ich schon so lange im Geschäft, dass ich mich noch gut daran erinnere, dass es früher mal C-Programmierer gab, die ihren ganzen Ehrgeiz darein legten möglichst den ganzen Algorithmus in eine Zeile C-Code zu packen. Aber auch damals gehörte ich schon zu denjenigen, die so einen Code nicht lesen konnten. Also machte ich mich heute daran die Befehle zu verhübschen (Zeileneinschübe, Tabs einfügen, …). Das hielt ich immerhin 4 Statements lang durch (danach benötigten die Befehle meist 20-25 Zeilen). Es lohnte sich immerhin, weil mir danach sofort ein paar Ungereimtheiten auffielen, aber spaßig war das nicht.

Deswegen fand ich nach kurzer Suche einen Online-Dienst, der einem dabei hilft. Es handelt sich um ein Java-Applet, dass komplett am Browser ausgeführt wird. Es wird also kein Code an einen Server geschickt. Ich persönlich finde das Ergebnis prima – kein Vergleich zu dem Spaghetti-Code…

Hier ist er: SQL Formatter

Das JAR-File kann man auch downloaden und lokal nutzen. Das habe ich aber noch nicht ausprobiert. Ein API ist in Vorbereitung.

Eine andere Alternative findet man unter SQL-Parser (ebenfalls online und als Desktop-Version, lezeres ebenfalls nicht von mir ausprobiert).

Für alle, die letzten Dienstag auf dem PASS-Treffen in Nürnberg waren: Wir rätselten, wie man für eine Sperre feststellen kann, welche Ressource betroffen ist. Es geht hier um die Info, die z.B. mit sp_lock angezeigt wird.

Ich fand dazu doch keine Info im SQL-Server-Magazine. Aber das ist recht gut und ausführlich in den Books-Online bei "sys-dm_tran_locks" beschrieben. Wenn man die View abruft, dann kann man anhand der Datenbank–ID und der Ressource-ID (steht in "resource_description") feststellen, welches Objekt genau gesperrt wurde. Dazu benötigt man noch die Datenbank-ID (siehe unten) und ggf. die ID der übergeordneten Ressource (resource_associated_entity_id ), die enthält z.B. meist die "heap or b-tree ID" falls eine Seite gesperrt wurde.
Wegen der vielfältigen Möglichkeiten ist es nicht ganz unaufwändig dafür eine allgemeine Interpretationsfunktion zu schreiben.

Dabei muss man beachten, dass die Objekt-IDs jeweils auf eine Datenbank bezogen sind. Aber das kann man mit einem neuen Feature des SQL-Servers-2005 lösen. Während man früher für die Funktion "object_name()" immer sicherstellen musste, dass man in der richtigen DB war, kann man heutzutage die relevante Datenbank angeben.

Bisher (geht immer noch):

use northwind
select object_name(21575115);
–> Orders
go
use Adventureworks
select object_name(21575115);
–> uspLogError

Jetzt möglich:
use master
select object_name(21575115, db_id('Northwind'));
–> Orders

Damit kann man eine Auswertung schreiben, die neben der internen Angabe der Sperren auch das Objekt im Klartext nennt: Fleißige vor…

In der September-Ausgabe von TecChannel-Compact (04/2007) ist ein sehr interessanter Test über die Zuverlässigkeit von externen Festplatten.
Der gleiche Artikel findet sich auch online unter dem Titel "Sicherheitsrisiko: Externe Festplatten im Test" (http://www.tecchannel.de/storage/komponenten/451265/).

Insgesamt schneiden die externen Platten gar nicht so schlecht ab, aber in Punkto Hitze wurden sehr bedenkliche Werte gemessen. Laut Datenblättern laufen die Festplatten im Bereich von 5 bis 35Grad Celsius einwandfrei. Darüber erhöht sich die Ausfallrate erheblich, ab etwa 60Grad ist dann auf jeden Fall Sense. Vermutlich steigt ihnen die Hitze zu Kopf. 😉
In dem Artikel wurden einige externe Platten bei verschiedenen Umgebungstemperaturen betrieben und deren Temperatur gemessen. Dabei kamen schon bei 30Grad Umgebungstemperatur etliche Festplatten an oder über die magische 60Grad-Grenze.
Mein Resümee war bei externen Platten für die Datensicherung auf Geräte mit Gehäuselüfter zu setzen…

Im SQL-Server-2005 wird zwischen Sessions und Connections unterschieden. Als ich das in dieser Woche bemerkte, musste ich ziemlich lange suchen bis ich den Unterschied bzw. die Zusammenhänge verstand:

Eine Session in SQL-Server-2005 ist das, was "früher" der System-Prozess war, den man in der Systemtabelle "sysprocesses" beobachten konnte. Jede Session hat eine Session-ID, die exakt dem entspricht, was bisher die SPID (system process ID) war. Neben den Session, die von Benutzern kommen, gibt es auch eine ganze Reihe von Sessions, die vom SQL-Server zur internen Verwaltung genutzt werden ("internal tasks"). Wie bisher sind sie daran erkennbar, dass deren Session-ID kleiner als 50 ist.
Informationen über Sessions kann man aus der Dynamic-Management-View (DMV) sys.dm_exec_sessions erfahren. Darin stehen neben den "bisherigen" Infos auch jede Menge Eigenschaften der Session, z.B. die aktuell gesetzten Werte der einschlägigen Settings, z.B. den Isolation-Level oder den Lock-Timeout. Sehr praktisch!

Jede Verbindung, die von "außen" zum SQL-Server aufgebaut wird, ist eine "Connection". Jeder Connection ist genau eine Session zugeordnet. Ich habe lange rumgerätselt, ob eine Session auch mehrere Connections haben kann, das ist zwar laut Doku möglich, ich fand aber keinen Hinweis darauf in welchen Fällen das wie gehen soll. Aber es scheint so zu sein, dass eine Connection nacheinander mehrere Sessions haben kann. Das scheint bei SOAP-Verbindungen relevant zu sein.
Connection-Informationen stehen in der DMV sys.dm_exec_connections. Hier finden sich dann auch die Netzadresse des Clients, das Login und solche Dinge. Schick ist, dass es pro Connection eine eindeutige unique ID gibt. Damit hat man endlich eine brauchbare ID, die nicht nach Beenden der Connection sofort wieder verwendet wird, wie das bei der SPID ist.

Daneben gibt es übrigens auch noch den Request. Jede Session kann mehrere Request ausführen. Darin wird das auszuführende SQL-Statement hinterlegt, die Transaktion, der Zugriffsplan usw.
Diese Infos stehen unter sys.dm_exec_requests. Neben den oben genannten Dingen stehen hier auch Status, Blockierungsinformationen und I/O-Informationen.

Für weitere Infos zu dem Thema empfehle ich den Artikel "Dynamic Management Views" auf SQLTeam.com.

Alle, die sich selber eine Meinung zu dem sehr umstrittenen "Open XML" bilden wollen, können das kostenlose eBook "Open XML – The markup explained" von Wouter van Vugt jetzt bei Microsoft downloaden. Er selber schreibt:

In this book you will be provided a detailed overview of the three major markup languages in Open XML. This book is written for those who have a basic understanding of XML or HTML. If you are a software architect or developer who needs to build document-centric solutions you can learn about how to build your value-added solutions based on the Open XML platform. Those new to document markup languages as well as those more experienced in document markup but new to Open XML will benefit from this book.

Ich habe es mal überflogen, es scheint sich gut zu lesen.

Ich bin mir noch nicht ganz schlüssig, was ich davon halten soll: entweder ist das völliger Blödsinn oder einfach genial.
"Ja, ich habe meine Präsentation dabei. Nein, ich habe keinen Stick. Ich habe sie auf dieser Maus mitgebracht."
Das ist eines der von Microsoft beworbenen Szenarien. Allerdings fände ich es dann besser, wenn man einen Presenter mit Speicher hätte. Fallen Euch Einsatzgebiete ein?

Hier steht mehr: Mouse With 1 GB of Flash Memory Built Into Transceiver.

Wenn man mit Identity-Werten arbeitet, dann kann man ganz bequem über die Funktionen @@IDENTITY und SCOPE_IDENTITY() den zuletzt vergebenen Wert erfragen. Wenn man mit Triggern arbeitet, dann kann man auf dabei auf unerwartete Schwierigkeiten stoßen. Wird in dem Trigger beispielsweise in einer Protokoll-Tabelle ein Eintrag gemacht, dann liefern die beiden genannten Funktionen unterschiedliche Werte zurück:

• @@IDENTITY liefert den zuletzt vergebenen Identity-Wert zurück. In diesem Fall die ID der Protokoll-Tabelle.
• SCOPE_IDENTITY() liefert hingegen die ID aus der „richtigen“ Tabelle.

Das ist ziemlich einleuchtend, kann aber noch komplizierter werden, wenn man mit Instead-Of-Triggern arbeitet. Für unser Beispiel stelle ich mit eine View vor, die in deren Instead-Of-Trigger die Aktion auf die Basistabelle weiterleitet, also INSERT, UPDATE oder DELETE und dann einen Eintrag in eine Protokoll-Tabelle macht.
In dieser fall sind die Ergebnisse der Funktionen anders:

• @@IDENTITY liefert den zuletzt vergebenen Identity-Wert zurück. In diesem Fall die ID der Protokoll-Tabelle. Falls auf der Tabelle ein Trigger liegt, dann diese, sonst der aus dem Trigger der View.
• SCOPE_IDENTITY() liefet hingegen NULL, weil in dem Kontext ja gar kein INSERT durchgeführt wurde, sondern nur im Instead-Of-Trigger.
• Will man sich mit der OUTPUT-Klausel behelfen, dann wird für inserted.ID immer der Wert 0 ausgegeben.

Die Schlüsse daraus mag nun jeder selber ziehen. Ich für meinen Teil lasse die Finger lieber von Triggern und setze statt dessen Stored-Procedures ein.

Anbei ein paar Samples zum selber ausprobieren:

if object_id ('protokoll') IS NOT NULL
drop table protokoll
go
if object_id ('protokoll') IS NULL
create table protokoll (
id integer identity(1,1) NOT null primary key nonclustered,
ts datetime NOT NULL default getdate(),
spid integer NOT NULL default @@SPID,
[user] sysname NOT NULL default user,
pid integer NULL,
uid integer NULL,
stmt varchar(100) NOT NULL,
comment varchar(300) NULL)
go

INSERT INTO MyTriggView(info)
OUTPUT inserted.ID INTO @outtable
VALUES ('View-Test');
SELECT 'INSERT INTO MyTriggView' as "Statement",
ID as "OUTPUT",
SCOPE_IDENTITY() as "SCOPE_IDENTITY()", –> liefert null
@@IDENTITY as "@@IDENTITY" –> liefert ID aus Protokoll-Tabelle
FROM @outtable
go
SELECT * FROM MyTriggView;
SELECT * FROM MyTriggTab
SELECT * FROM protokoll

Wenn man "select *" verwendet, dann erwartet man immer alle Attribute einer Tabelle in der Ergebnismenge. Das ist für AdHoc-Abbfragen sehr praktisch, aber für den Einsatz in Programmen ungeeignet.

• Wenn man später die Tabelle um eine weitere Spalte ergänzt, dann leifert der Befehl plötzlich eine um das Feld erweiterte Ergebnismenge, mit der die Anwendung nicht rechnet.
• Außerdem erwartet meine Anwendung die Spalten in einer bestimmten Reihenfolge. Nach einer Datenbankänderung kann sich die Reihenfolge aber ändern.

In beiden Fällen muss ich meine Anwendung ändern und das ist sicher mehr Aufwand als die Spalten auszuformulieren, zumal das Visual-Studio for DB-Pros jetzt auch im Refactoring das "*" durch die aktuellen Spalten ersetzen kann.

In Views führt ein "select *" zu einem "eigenwilligen" Verhalten. Es werden hier nämlich nicht immer alle Attribute einer Tabelle in der Ergebnismenge angezeigt!

• Wenn eine neue Spalte in einer Basistabelle hinzukommt, dann ändert sich die View-Definition bzw. die Struktur seiner Ergebnismenge dadurch nicht. Es werden danach weiterhin nur die "alten" Attribute angezeigt.
• Wenn eine vorhandene Spalte geändert wird, dann ändert sich die View nicht, es werden die gleichen Spalten (inkl. Typen) zurückgeliefert, wie vor der Änderung. Die Werte werden jedoch aus dem neuen in den alten Datentyp konvertiert.
• Wird eine vorhandene Spalte entfernt und die View enthält nun mehr Spalten als die Tabelle, dann kommt eine Fehlermeldung.
• Wird hingegen eine neue Spalte angefügt und eine vorhandene Spalte entfernt, dann wird die View nicht compiliert und die Werte werden aus den falschen Spalten angezeigt. Die Neue wird hinten angefügt und die Fehlende einfach weggelassen.

Blöderweise wird darauf in den Books-Online nicht explizit hingewiesen. Stattdessen wird in allen Beispielen bei "CREATE VIEW" das "*" verwendet! Bei MS liest sich das so:

If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

Hier ein paar Samples, zum selber ausprobieren:

use tempdb
go

– Tabelle ändern: noch eine Spalte entfernen (Nun sind weniger Spalten in der Tabelle als in der bisherigen View-Definition)
ALTER TABLE blabla DROP COLUMN muh
go
select * from vlavla
–> Jetzt kommt ein Fehler!

Und da ist es wieder: SQL-Injection. Das beliebte Spiel mit den Eingabefeldern. Werfen Sie verschiedene "Daten" ein und warten sie was sie bekommen! Das beste ist, es geht nicht nur mit grafischen Oberflächen, sondern auch mit anderen Eingabemöglichkeiten. Probieren Sie es aus!

Allen die jetzt hämisch Lachen rate ich dazu dringend mal alle Schnittstellen der eigenen Anwendungen zu prüfen. In diesem Fall war die XMLRPC-Schnittstelle das Sicherheitsloch.

In Versionen vor WordPress 2.2.3 können Angreifer durch SQL-Injection-Lücken an Benutzerdaten gelangen.

Mehr dazu bei Heise.de im Artikel "WordPress 2.2.3 schließt Sicherheitslücken".

Diesmal erwischte es also WordPress. Und das nachdem ich gerade erst die Version 2.2.2 auf vier Weblogs installiert hatte! Damit meine kleinen Erweiterungen, z.B. ein wenig SQL-Tuning und eine eigene Zählung), nicht verloren geht, kann ich die neue Version nicht einfach nur drüber klatschen… 🙁

OK, wahrscheinlich wissen es sowieso schon alle, aber ich habe mich immer noch nicht durch alle Blogs-Postings meiner 4 Urlaubswochen durchgeackert: Paul Randal ("Mr DBCC") ist nicht mehr bei Microsoft. Wie schade. Er sucht sein Auskommen jetzt bei SQLskills mit Schulungen, in denen er sein bei MS erworbenes Wissen weiter gibt. Naja, wenigstens erhöht das die Chancen ihn auch mal auf ein paar europäischen Konferenzen zu sehen…

In dem Zuge hat er auch ein neues Weblog begonnen: "In Recovery…". Sein erster Artikel ist eine aktualisierte Zusammenfassung von Artikeln seines alten Weblogs über defekte Datenbanken. Lesenswert.