SQL Server Management mit PowerShell und dbatools (Part1): Installation und Datenbank-Migration

dbatools ist ein praktisches OpenSource PowerShell Modul, mit dem administrative Aufgaben des Microsoft SQL Server Managements bequem aus der PowerShell erledigt werden können. Mit den mehr als 500 Befehlen, die dbatools beherrscht, ist der Einsatz für eine Vielzahl von Aufgaben und Szenarien denkbar.

In diesem ersten Artikel unserer mehrteiligen Reihe durchlaufen wir die Installation der dbatools und untersuchen einige der Befehle, die für eine vollständige Server-Migration unverzichtbar sind. Im Fokus stehen vor allem das Migrieren der Konfigurationseinstellungen, Logins sowie von Datenbanken.

Folgender Vergleich aus dem offiziellen GitHub der dbatools beschreibt die Funktionalität sehr passend:
„think of it like a command-line SQL Server Management Studio”.

Aufbau unserer Umgebung

In unserem Szenario laufen zwei Virtuelle Maschinen (VMs) OPWin22Sql17 sowie OPWin22Sql19 auf einem privaten Cloud Cluster. Sie befinden sich in derselben Domäne. Arbeitsumgebung von der administriert wird ist eine weitere VM, die sich ebenfalls in dem Cluster-Netzwerk befindet. Alle Befehle werden von dort aus in der PowerShell ausgeführt.

Auf der VM OPWin22Sql17 läuft ein SQL 2017 Server mit einer AdventureWorks2017 Beispieldatenbank und diversen Logins. Diesen Server migrieren wir auf den frisch eingerichteten SQL 2019 Server der OPWin22Sql19 VM. Beide SQL Server sind über Windows Authentication gesichert.

Installation der dbatools

Die ersten beiden Schritte müssen nur ausgeführt werden, falls in der Vergangenheit noch kein PowerShell Modul aus der PowerShellGallery, der zentralen Repository für PowerShell-Inhalte, installiert wurde.

Zunächst müssen wir sichergehen, dass wir remote signierte Zertifikate ausführen können. Dazu ändern wir die ExecutionPolicy in der PowerShell folgendermaßen.

Und bestätigen mit A. Nun haben wir die Berechtigung remote signierte Skripte vertrauenswürdigen Herausgeber, sowie unsere eigenen lokalen Skripte auszuführen.

Als nächstes erlauben wir Installationen aus der PowerShell Gallery.

Nun zur eigentlichen Installation. Wir installieren das Modul mit dem install-Module Befehl:

und bestätigen anschließend in der Eingabeaufforderung mit A, sodass wir dbatools als Publisher immer vertrauen. Nun können wir mit dem Ausführen von dbatools Befehlen beginnen.

Für eine detailliertere Schritt-für-Schritt Installation unter Windows empfehlen wir diesen offiziellen Artikel von dbatools.

Verbindung testen (Optional)

Nachdem dbatools eingerichtet ist, möchten wir uns zunächst Informationen über die Verbindung zur SQL Instanz ausgeben lassen. Das geht mit dem Test-DbaInstance. Gleichzeit stellen wir sicher, dass wir mit der PowerShell remote Ausführungsrechte auf den Servern haben. Um den Befehl auszuführen, setzen wir die beiden Namen unserer SQL Server hinter den -SqlInstance Parameter in dem Test-DbaInstance Befehl.

Nach dem Ausführen erhalten wir folgende Ausgabe:

Bei diesem dbatools Befehl ist besonderes Augenmerk auf den [-SqlInstance] Parameter zu richten. Er ist fast immer ein Pflichtparameter und wird uns noch häufiger über den Weg laufen. In diesem Beispiel übergeben wir in einem einzigen Befehl sogar zwei verschiedene SQL Server in den Parametern. Damit wird einer der Gründe deutlich, wieso dbatools so nützlich ist, denn mit fast jedem dbatools Befehl lassen sich mehrere SQL Instanzen gleichzeitig ansprechen!

Bevor wir mit der Migration loslegen, ist es eine gute Idee zu überprüfen, ob wir uns mit unseren SQL Servern verbinden können. Das Prinzip des Verbindungsaufbaus ist das gleiche wie bei SSMS. Wir benutzen dazu den Connect-DbaInstance Befehl.

Verbindungen lassen sich auch auf Instanzen mit vom Standard abweichenden Port Nummern herstellen. Ebenso ist es möglich sich mit SQL Server Authentication, Azure Active Directory (AAD) oder Azure SQL Database zu verbinden, und das sogar bei Nutzung von Multifactor authentication (MFA). Für diese Szenarien schaut man gegebenenfalls in die offizielle Dokumentation.

An dieser Stelle bietet sich noch ein Datenbank-Check zum Beispiel mit CheckDB an, um die Integrität unserer Datenbank zu überprüfen. Wie man CheckDB richtig verwendet und Datenbanken gegebenenfalls reparieren kann in diesem Artikel nachlesen.

Migration der Datenbank

1. SQL SERVER Konfigurationseinstellungen überprüfen und kopieren

Um sicherzugehen, dass unser Zielserver korrekt konfiguriert ist, möchten wir uns die SQL Server Konfigurationseinstellungen des Ursprungservers ausgeben lassen und gegebenenfalls mit denen des Zielservers vergleichen.

Dazu nutzen wir den Get-DbaSpConfigure Befehl, der uns die Konfigurationseinstellungen aus der Configuration.Properties Eigenschaft des SQL Server Management Objekts (SMO) ausliest.

Die Konfigurationseinstellungen vom OPWIN22SQL17 werden uns in der Kommandozeile ausgegeben.

Praktischer können wir uns diese Ausgaben auch direkt in eine Datei speichern lassen. Dazu benutzen wir den Redirection Operator > und übergeben damit die Ausgabe von Get-DbaSpConfigurean eine Textdatei.

Den selben Befehl lassen wir für unseren Ziel Server OPWin22Sql19 laufen.

Nun können wir bequem die Konfigurationsdateien mit dem Texteditor unserer Wahl vergleichen. Da wäre das kleine Opensource Tool WinMerge zu erwähnen, das speziell zu Zwecken der Differenzierung von Textdateien entwickelt wurde.

Konfigurationseinstellungen kopieren

Sollten wir nach der Prüfung feststellen, dass wir die Konfigurationsdatei auf den neuen SQL Server kopieren möchten, können wir dies mit dem Copy-DbaSpConfigure Befehl tun.

Copy-DbaSpConfigure migriert Konfigurationseinstellungen von einem SQL Server auf den anderen.

Damit alle Konfigurationseinstellungen vom SQL Server übernommen werden machen wir einen Neustart mit dem Restart-DbaService Befehl.

2. Logins übertragen

Ebenfalls notwendig bei unserer Migration ist es, Logins zu übertragen. Hierbei hilft uns der CopyDbaLogin Befehl.

Bevor wir unsere Logins migrieren, möchten wir einen Blick auf diese werfen. Mit dem dbatools Befehls Get-DbaLogin lassen sich die Logins eines SQL Servers ausgeben. Um die Ausgabe des Get-DbaLogin in einem Fenster zu betrachten, benutzen wir den Pipe Befehl | der PowerShell, sowie Out-GridView in Kombination mit -Passthru. Damit lassen sich die Logins in einem Tabellenfenster ausgeben und bequem inspizieren. Dieser Schritt ist natürlich optional.

In dem neuen Fenster werden alle Logins unseres Server dargestellt.

Nachdem wir uns einen Überblick über die Logins verschafft haben, schauen wir uns zunächst den Copy-DbaLogin Befehl, den wir für das Migrieren der Logins verwenden werden, an.

Pflichtparameter sind erwartungsgemäß [-Source] und [-Destination].

Von Interesse für uns sind die beiden optionalen Parameter [-Force]und [-KillActiveConnection].

Wird der Parameter [-Force] gesetzt, werden Logins, die auf dem Zielserver bereits existieren, gelöscht und dann neu angelegt. Logins, die Agent Jobs besitzen, können allerdings nicht gelöscht werden.

Mit [-KillActiveConnection] werden alle aktiven Verbindungen und Sessions auf der Zielinstanz gestoppt. Das kann z.B. beim Kopieren von Logins wichtig sein, da Logins nicht gelöscht werden können, solange sie in einer Session verwendet werden.

Um sicherzugehen, dass wir auf der Zielinstanz bereits vorhandene Logins überschreiben, benutzen wir die beiden erwähnten Parameter [-Force] und [-KillActiveConnection].

Zu beachten ist, dass der Login Algorithmus seit der SQL Server 2012 Version nicht mehr rückwärtskompatibel ist. Das migrieren der Logins auf ältere SQL Server ist hiermit also nicht möglich.

Der Copy-DbaLogin hat noch weitere spezifische Parameter. Für eine detaillierte Beschreibung des Befehls und aller Parameter empfehlen wir einen Blick in die Dokumentation.

3. Datenbank kopieren

Nun bleibt uns noch, die Datenbank selbst zu kopieren. Dabei gibt es mehrere Möglichkeiten, wir haben uns dafür entschieden, den Copy-DbaDatabase zu verwenden.

Schließlich bleibt uns noch die Datenbank selbst zu kopieren. Zum einen gibt es die Möglichkeit, ein Backup von OPWIN22SQL17 zu erstellen und dann über den Restore-DbaDatabase auf OPWin22Sql19 wiederherzustellen.

Mit dem Copy-DbaDatabase Befehl erfolgt das Kopieren über Backup / Restore sogar in einem Schritt. dbatools erstellt für uns ein Backup, welches automatisch auf dem Zielserver wiederhergestellt wird. Dazu müssen wir nur -BackupRestore sowie den -SharedPath Parameter zusammen mit einem Pfad, auf den beide Server zugreifen können, übergeben.

Alternativ lässt sich auch mit Detach / Attach die Datenbank kopieren.

Statt von -BackupRestore muss hierzu der -DetachAttach Parameter gesetzt werden. Wieso wir uns für -BackupRestore entschieden haben erläutern wir in den nächsten Teilen unserer Artikelreihe.

Fazit

Wir hoffen wir konnten Sie von der Mächtigkeit des kleinen Tools überzeugen und Ihnen die Installation der dbatools näherbringen. Im nächsten Teil unserer Artikelreihe machen wir weiter mit der Migration von Server Agent Jobs und zeigen dann, wie man Cluster mit AlwaysOn High Availability Groups migriert und das alles selbstverständlich mithilfe der dbatools.

Noch offene Fragen? Unsere Expert:innen helfen Ihnen gerne weiter! Kontaktieren Sie uns gerne einfach über das Kontaktformular.