SQL Server Instanz Historisierung

Im Rahmen einer Migration, dem Aufspielen neuer Updates oder dem Ausrollen von Anwendungen auf einer SQL Server Instanz, tritt oft eine nicht all zu untypische Situation ein: das Auftreten eines Problems sorgt dafür, dass der Vorgang abgebrochen und alle getätigten Änderungen rückgängig gemacht werden müssen. Was das für die SQL Server Instanz mit all ihren Datenbanken, Logins, Agent Jobs, Ansichten, Prozeduren, Funktionen, etc. bedeutet, ist dabei oftmals alles andere als trivial: strukturelle Änderungen an allen Objekten müssen explizit nachvollzogen und entsprechend umgesetzt werden.

In diesem Artikel möchten wir Ihnen unseren Umgang mit einer solchen Situation (gerade im direkten Einsatz bei unseren Kunden) im Zusammenhang mit einem von uns entwickelten Script vorstellen und bestimmte Aspekte genauer betrachten.

Wie immer sind alle wichtigen Informationen und Quelltexte in unserem Github Repository zu finden.

Die Idee
Eine Möglichkeit, die zuvor beschriebene Situation richtig zu behandeln, ist die Historisierung der SQL Server Instanz. Existiert ein Abbild des Zustandes der Instanz zum Zeitpunkt vor der problematischen Änderung, kann dieser Zustand beim Auftreten eines Problems wiederhergestellt werden.

Doch auch die Historisierung bzw. das Erzeugen eines solchen Zustandes ist keine triviale Aufgabe, weshalb wir uns dafür entschieden haben, eine Prozedur zu entwickeln, die diesen Vorgang automatisiert und einen reproduzierbaren Zustand der SQL Server Instanz mit Hilfe eines simplen Scripts realisiert.

Die Prozedur soll dabei die folgenden Aufgaben und Anforderungen erfüllen:

1. Zum Start der Prozedur müssen alle Objekte der Instanz erfasst und inventarisiert werden.
2. Die Inventarisierung einer Instanz muss mit einer eindeutigen ID versehen werden.
3. Mit dem Beenden der Prozedur müssen erneut alle auf der Instanz befindlichen Objekte erfasst und inventarisiert werden.
4. Basierend auf den erfassten Zuständen muss ein Delta der Objekte und ihrer strukturellen Beschaffenheit erzeugt und verarbeitet werden.
5. Das verarbeitete Delta muss als Script in der Ausgabe erscheinen. Basierend auf diesem erzeugten Script soll es möglich sein, den initialen Zustand (also den Zustand zum Zeitpunkt, an dem die Historisierung gestartet wurde) wiederherzustellen.

Die Umsetzung dieser Anforderungen wird in den folgenden Abschnitten genauer betrachtet.

Die Umsetzung
In der Umsetzung der Anforderungen sind die t-SQL Prozeduren sp_hist_start und sp_hist_stop entstanden, wobei sp_hist_start für den initialen Aufbau der Inventarisierung und sp_hist_stop für das Verarbeiten des Objekt-Deltas genutzt wird.

Für die Erfassung der strukturellen Zustände der Objekte im Bezug auf SQL Server Logins haben wir auf die von Microsoft hier bereitgestellten Prozeduren sp_hexadecimal und sp_help_revlogin zurückgegriffen. Bezüglich der Tabellen haben wir eine Prozedur zur Erzeugung der Struktur verwendet. Sie steht hier zum Download zur Verfügung.

Die Prozedur sp_hist_start
Mit dem Ausführen der Prozedur sp_hist_start beginnt die Inventarisierung: alle

• Datenbanken,
• SQL Server Logins,
• Agent Jobs,
• Linked Server,
• Ansichten jeder Datenbank,
• Prozeduren jeder Datenbank,
• Funktionen jeder Datenbank und
• Tabellen jeder Datenbank

werden erfasst und in einer zugehörigen Tabelle hinterlegt.

Nach dem Start der Prozedur hat die Historisierung begonnen und es können alle benötigten Änderungen an der Instanz getätigt werden.

In der Ausgabe der Prozedur werden einige Informationen bereitgestellt: der Status der Prozedur, der Benutzer, der die Prozedur ausgeführt hat, die zugehörige, eindeutige ID (genannt DeploymentId) und das Statement zum Beenden dieses speziellen Historisierungs-Durchlaufes.
Das Statement kann so abgeändert werden, dass zusätzliche Debug-Informationen angezeigt werden. Dies geschieht, indem die 0 am Ende des Statements durch eine 1 ersetzt wird.

Die Prozedur sp_hist_stop
Mit Hilfe der Prozedur sp_hist_stop kann die Historisierung beendet werden. Hierfür wird eine eindeutige Identifikation des Historisierungs-Durchlaufes (die DeploymentId) als Parameter benötigt.

Die Prozedur sp_hist_stop beginnt ihre Verarbeitung analog zu sp_hist_start: der Zustand der Instanz wird erfasst. Basierend auf den zwei Messpunkten muss nun das Delta der Objekte erzeugt werden.
Mit diesem Delta wird anschließend in der Ausgabe ein Script erzeugt, welches die Änderungen an jedem Objekt rückgängig macht und neu erzeugte Objekte entfernt. Also, den initialen Zustand der Instanz wiederherstellt.

An dieser Stelle ist die Handlung des Benutzers beliebig: ist ein Problem aufgetreten, kann das erzeugte Script ausgeführt werden, um den Vorgang abzubrechen. Das Skript kann verworfen werden, sobald alle Änderungen an der Instanz erfolgreich verarbeitet wurden.

Fazit

Der Wunsch, den Zustand einer Datenbank oder einer Instanz auf einen früheren Zeitpunkt zurück setzen zu können, ist Ihnen vermutlich bekannt, sollten Ihnen diese Methoden bekannt sein. Haben Sie ähnliche Probleme oder wollen sich an der Historisierung einer Instanz versuchen, finden Sie die Quelltexte der Prozeduren in unserem Github Repository.