PowerShell für SQL Server Schulung: Automatisierung mit dbatools

PowerShell und SQL Server

• SQL-Server verwalten mit PowerShell: Inventar, Prüfungen, Sicherungen und Migration
• PowerShell ist nicht die Lösung für alles: Abfragelogik bleibt in T-SQL, Wartung oft im SQL Server Agent
• Wann PowerShell nützlich ist: viele Instanzen, wiederkehrende Prüfungen, Übergaben an andere Systeme
• Unterschiede der Module: altes SQLPS, aktuelles Modul SqlServer und SMO als gemeinsame Basis
• Invoke-Sqlcmd aus SqlServer und Invoke-DbaQuery aus dbatools im direkten Vergleich
• Open-Source-Lösungen: dbatools installieren, Befehle finden mit Find-DbaCommand
• Weitere Open-Source-Werkzeuge: dbachecks und Install-DbaFirstResponderKit

Verbinden mit SQL Server von PowerShell aus

• Verbindungsobjekte deklarieren: Connect-DbaInstance und SMO-Objekt Server
• Verbinden mit Windows-Authentifizierung, SQL-Logins und Microsoft Entra ID
• Verschlüsselung und Zertifikate: -TrustServerCertificate und Set-DbatoolsInsecureConnection nur im Labor
• Anmeldedaten aus SecretManagement statt Kennwörtern im Skript
• Parameter in PowerShell und SQL: -SqlParameter statt zusammengesetzter Abfragetexte
• ADO.NET mit Microsoft.Data.SqlClient: Verbindung öffnen, Befehl ausführen, schließen
• Verbindungen beseitigen: Dispose() im finally-Block und Verbindungspooling
• Verbindungsobjekte über mehrere Abfragen und Funktionen wiederverwenden

Datenbankobjekte in SQL Server mit PowerShell erstellen

• Datenbanken erstellen mit New-DbaDatabase und Einstellungen prüfen
• Tabellen erstellen per T-SQL-Skript oder mit New-DbaDbTable
• Ansichten und Prozeduren aus versionierten .sql-Dateien bereitstellen
• Wiederholbar bereitstellen: CREATE OR ALTER und Existenzprüfung vor dem Anlegen
• Vorhandene Objekte mit Export-DbaScript als Skript sichern und vergleichen

Daten in SQL Server mit PowerShell abfragen

• Daten in Tabellen einfügen mit Write-DbaDbTableData, Massenimport aus CSV mit Import-DbaCsv
• Rückgabe von Daten als Objekte: Invoke-DbaQuery -As PSObject oder DataTable
• Read-SqlTableData und Write-SqlTableData aus dem Modul SqlServer
• Gespeicherte Prozeduren mit Eingabe- und Ausgabeparametern ausführen

Mehrere SQL Server mit PowerShell verwalten

• Schleifen über Instanzlisten mit foreach und ForEach-Object
• Server speichern: registrierte Server, Central Management Server oder Konfigurationsdatei
• Instanzen finden mit Find-DbaInstance und Inventar mit Get-DbaInstanceProperty
• Konzepte der Parallelität: Threads, Runspaces und Hintergrundjobs im Vergleich
• Befehle für Threading: ForEach-Object -Parallel -ThrottleLimit und Start-ThreadJob
• Authentifizierung und Threading: Anmeldedaten mit $using: in parallele Blöcke übergeben
• Piping zwischen Objekten: dbatools-Befehle über die Pipeline verketten
• Prüfungen über alle Instanzen: Patchstand mit Test-DbaBuild, Speicherplatz, Agent-Jobs
• Export von CSV-Dateien für CMDB, Tickets und Tabellenkalkulation
• Import von CSV-Dateien mit Prüfung von Spalten und Datentypen vor dem Laden
• XML-Daten exportieren mit Export-Clixml und ConvertTo-Xml
• XML-Daten importieren mit [xml], Select-Xml und Import-Clixml

Sicherungen, Wiederherstellungstests und Migration

• Sicherungen auslösen mit Backup-DbaDatabase oder Backup-SqlDatabase und protokollieren
• Sicherungshistorie über alle Instanzen auswerten mit Get-DbaDbBackupHistory
• Wiederherstellungstests automatisieren mit Test-DbaLastBackup
• Instanzen migrieren mit Start-DbaMigration oder einzeln mit Copy-DbaLogin und Copy-DbaAgentJob
• Datenbanken umziehen mit Copy-DbaDatabase per Sicherung und Wiederherstellung
• Sicherungsstrategie und Wiederherstellungsmodelle: Verweis auf das Administrationsseminar

PowerShell-Remoting und SQL Server

• Was ist Remoting? Befehle dort ausführen, wo die SQL-Server-Instanz läuft
• Fernwartung über HTTP-Port 5985 und HTTPS-Port 5986: Listener und Firewall
• Fernwartung aktivieren mit Enable-PSRemoting
• Remoting über GPO verteilen: WinRM-Dienst, Listener und Firewallregel
• Remotesitzung starten mit Enter-PSSession und New-PSSession
• Remotesitzung beenden und aufräumen mit Exit-PSSession und Remove-PSSession
• Alternative Anmeldedaten und Double-Hop-Problem beim Zugriff auf Sicherungsfreigaben
• Remotebefehl aufrufen mit Invoke-Command auf allen SQL-Server-Hosts gleichzeitig

Instanzkonfiguration mit Pester testen

• Pester-Tests für Sollwerte wie maximaler Arbeitsspeicher, MAXDOP und Sortierung
• dbachecks als fertige Prüfsammlung anpassen und regelmäßig ausführen
• Testergebnisse als JUnit-XML für die CI-Pipeline oder als HTML-Bericht

SQL-Server-Skripte mit Coding-Agenten

• Agent verbindet sich nur mit Test-Instanzen von SQL Server über ein Login mit VIEW SERVER STATE, nicht als sysadmin
• Copy-DbaLogin oder Start-DbaMigration aus Agentenvorschlägen zuerst nur mit -WhatIf
• Agent schreibt zuerst Pester-Prüfungen für die SQL-Server-Zielkonfiguration, dann das Änderungsskript
• Vorgeschlagene Invoke-Sqlcmd-Aufrufe mit eingebetteten Variablen auf Invoke-DbaQuery -SqlParameter umstellen
• Parallele Agentenläufe auf wenige Instanzen und ein kleines -ThrottleLimit begrenzen

Praxisprojekt: tägliche Betriebsprüfung für alle Instanzen

• Instanzliste aus dem Central Management Server lesen und parallel abfragen
• Letzte Sicherungen, fehlgeschlagene Agent-Jobs und freien Speicher prüfen
• Wöchentlichen Wiederherstellungstest mit Test-DbaLastBackup einplanen
• Ergebnis als CSV und HTML-Bericht, Pester-Tests für die Prüfungslogik