Zugriff auf SQL-Server per SMO mit Powershell - ohne SMO-Installation!

SQL-Server lassen sich mit Hilfe der SMO-Erweiterungen (Server Management Objects) des SQL-Servers per Powershell wunderbar verwalten. Einen ganzen Satz von Beispielen finden Sie z.B. auf unserer Website unter Dokus. Für den Einsatz von SMO muß SMO allerdings erst einmal installiert werden. Dies geschieht entweder bei der SQL-Server Installation, indem Sie unter der Feature-Auswahl "Client Tools SDK" auswählen, oder indem Sie von der Microsoft-Website aus dem Feature-Pack Download "SharedManagementObjects.msi" und "SQLSysCLRTypes.msi" herunterladen und installieren. Oder Sie bauen sich alternativ ein Powershell-Modul, dass die notwendigen dll´s enthält und von selbst nachlädt. Alles, was Sie hierzu benötigen, sind die beiden Downloads aus dem Feature Pack, und eine Powershell Modul-Datei. Hierfür bietet sich passenderweise eine Manifest-Datei (.psd1) an. 

Aber fangen wir von vorne an. SMO ist ein Feature, dass Microsoft eingeführt hat, um die Verwaltung von SQL-Servern aus dem .NET-Framework zu erleichtern. Anstatt über SQL-Befehle kann man per SMO programmatisch über Objekte auf den SQL-Server zugreifen. Und da man mit Powershell auf alles zugreifen kann, was das .Net-Framework zur Verfügung stellt, kann man natürlich auch SMO nutzen. Tatsächlich tun Sie das sogar bereits, wenn Sie mit der SQL-Server Konsole für Powershell (SQLPS) arbeiten, denn das besondere an dieser Konsole ist vor allem, dass beim Starten von SQLPS automatisch die SMO-Assemblies geladen werden. 

Unschön an SMO ist, dass auf jedem Rechner, auf dem Sie Ihre SQL-Server Powershell-Scripte laufen lassen möchten, SMO installiert sein muß. Allerdings läßt sich dieses Problem mit einem kleinen Trick umgehen, denn tatsächlich muß die SMO-Funktionalität gar nicht installiert werden - Sie benötigen nur die dll-Dateien, die die SMO-Funktionalität zur Verfügung stellen. Und die erhalten Sie, wenn Sie die beiden oben angegebenen msi-Pakete auf einem beliebigen Rechner installieren. Die SMO-Assemblies finden Sie nach der Installation standardmässig im Ordner %ProgramFiles% unter "Microsoft SQL Server\<Version>\SDK\Assemblies", wobei Version der internen Versionsnummer des SQL-Servers entspricht. Bei einer Standardinstallation von SMO für SQL Server 2014 wäre das also Beispielsweise:

C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\

Kopieren Sie den Ordner Assemblies. Weiterhin benötigen Sie die "Microsoft.SqlServer.SqlClrProvider.dll", an die allerdings nicht so einfach heran zu kommen ist. Sie ist im Global Assembly Cache installiert und wird standardmässig aus der Explorer-Ansicht gefiltert. Sie müssen den Filter deaktivieren. Starten Sie hierfür den Registieriungseditor und setzen Sie die folgenden Wert: 

Schlüssel: HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Fussion
Name: DisableCacheViewer
Wert: 0x1
Typ: Dword

Danach finden Sie im Ordner "%Windir%\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider\" einen Ordner entsprechend Ihrer SQL-Server Version, in dem Sie die ""Microsoft.SqlServer.SqlClrProvider.dll" finden. Kopieren Sie die Datei in die Kopie Ihres Assemblies-Ordners. (Eine genaue Anleitung finden Sie noch einmal hier.)

Der aufwendige Teil ist erledigt. Alles, was Sie jetzt noch tun müssen, ist meine Modulvorlage herunterzuladen. Die Vorlage enhält eine einfache .ps1-Datei und eine .psd1 Datei. Die PS1-Datei enthält eine Demo-Funktion zum Auflisten von Instanzen, die .psd1-Datei ist eine Manifest-Datei, die die dlls referenziert. Entpacken Sie die ZIP-Datei in direkt in Ihrem Powershell-Modul-Ordner in einen Unterordner mit gleichem Namen wie die ZIP-Datei und kopieren Sie den Ordner Assemblies als Unterordner in den Ordner SQLManagement. Danach steht Ihnen SMO als Modul zu Verfügung. Von hier aus können Sie das Modul jetzt einfach per Kopieren und Einfügen auf beliebige Rechner verteilen.

Ich plane, in Zukunft weitere Powershell-Funktionen in das Modul einzubauen, aber derzeit können Sie sich einfach selber austoben. Eine Reihe von Beispielen, was man mit SMO so alles machen kann, finden Sie in diesem Beispielskript.