PowerShell dla SQL Server

PowerShell dla SQL Server automatyzuje zadania administracyjne, np. backupy, wdrożenia i kontrolę stanu instancji.

PowerShell i moduł SqlServer w środowisku SQL Server

PowerShell dla SQL Server służy do automatyzowania czynności wykonywanych wobec instancji, baz danych i obiektów zarządzanych przez ten silnik. Współczesne skrypty korzystają przede wszystkim z modułu SqlServer, który zawiera polecenia oparte między innymi na bibliotece SQL Server Management Objects, znanej jako SMO. Moduł można załadować poleceniem Import-Module SqlServer, a dostępne elementy sprawdzić przez Get-Command -Module SqlServer. W środowisku, w którym zainstalowano także starszy moduł SQLPS, należy zwrócić uwagę na wersję ładowanego modułu, ponieważ różnice w nazwach parametrów i zachowaniu poleceń mogą zmienić wynik skryptu.

Jednym z podstawowych poleceń jest Invoke-Sqlcmd. Pozwala ono wykonać instrukcję Transact-SQL albo zawartość pliku skryptowego na wskazanej instancji SQL Server. Parametry -ServerInstance, -Database, -Query i -InputFile określają odpowiednio serwer, bazę, tekst zapytania oraz plik wejściowy. Przy połączeniu z użyciem uwierzytelniania systemu Windows można użyć -TrustServerCertificate wyłącznie wtedy, gdy świadomie akceptuje się brak pełnej weryfikacji certyfikatu. W przeciwnym razie należy skonfigurować poprawny łańcuch zaufania i szyfrowanie połączenia zgodnie z polityką danego środowiska.

Wykonywanie zapytań i obsługa obiektów SMO

Invoke-Sqlcmd jest przydatne przy pojedynczych operacjach i powtarzalnych skryptach wdrożeniowych. Przykładowo, zapytanie SELECT name, state_desc FROM sys.databases odczytuje nazwy baz oraz ich stany z widoku katalogowego instancji. Wynik zwracany do PowerShella można filtrować poleceniami tego środowiska, ale trzeba pamiętać, że dane są wtedy przekształcane do obiektów PowerShell, a nie pozostają już wyłącznie tekstem SQL. Błędy wykonania powinny być jawnie obsługiwane, na przykład przez ustawienie -ErrorAction Stop i zastosowanie bloku try, catch oraz finally. Dzięki temu skrypt może przerwać dalsze działania, zapisać szczegóły wyjątku i zwolnić użyte zasoby.

SMO udostępnia obiektowy model administracji SQL Server. Po utworzeniu obiektu Microsoft.SqlServer.Management.Smo.Server można odczytywać właściwości instancji, kolekcję baz danych, informacje o tabelach, zadaniach agenta i innych obiektach obsługiwanych przez SMO. Ten model jest wygodny podczas budowania narzędzi, które mają przejść przez wiele obiektów i wykonywać warunkowe operacje. Przykładowo, skrypt może sprawdzić, czy baza istnieje, odczytać jej stan, rozmiary plików oraz model odzyskiwania, a następnie wykonać określoną czynność tylko wtedy, gdy spełnione są ustalone warunki. Takie sprawdzenie ogranicza ryzyko wielokrotnego tworzenia obiektów lub uruchamiania operacji na bazie w nieprawidłowym stanie.

Backupy, wdrożenia i kontrola stanu instancji

Automatyzacja kopii zapasowych SQL Server wymaga rozróżnienia kopii pełnej, różnicowej oraz kopii dziennika transakcji. PowerShell może uruchamiać odpowiednie instrukcje BACKUP DATABASE i BACKUP LOG przez Invoke-Sqlcmd, natomiast SMO udostępnia obiekty przeznaczone do wykonywania operacji backupu. Samo zakończenie polecenia bez błędu nie jest wystarczającym dowodem poprawności procesu. Skrypt powinien sprawdzić komunikaty SQL Server, kod błędu, lokalizację pliku kopii oraz możliwość odczytania metadanych kopii przez instrukcję RESTORE HEADERONLY. W procedurze kontrolnej można także użyć RESTORE VERIFYONLY, pamiętając, że weryfikuje ona strukturę kopii, lecz nie zastępuje rzeczywistego testu odtworzenia bazy.

PowerShell może również porównywać stan instancji z oczekiwanym opisem wdrożenia. Dotyczy to między innymi obecności baz, schematów, tabel, indeksów, użytkowników bazodanowych i uprawnień. Bezpieczny skrypt powinien działać możliwie idempotentnie, czyli po ponownym uruchomieniu nie powodować niezamierzonych zmian, jeżeli stan docelowy został już osiągnięty. Przed zmianą należy sprawdzić wersję obiektu lub zawartość katalogu systemowego, a operacje zależne od siebie wykonywać w kontrolowanej kolejności. Przy migracjach schematu istotne jest rejestrowanie wykonanych kroków, ponieważ częściowo zastosowany skrypt może pozostawić bazę w stanie pośrednim i wymagać osobnej procedury wznowienia albo wycofania.

Uprawnienia, diagnostyka i bezpieczne uruchamianie skryptów

Skrypt PowerShell wykonujący operacje w SQL Server działa w kontekście określonego konta i dziedziczy jego uprawnienia. Zakres dostępu powinien odpowiadać wykonywanej czynności: odczyt metadanych wymaga innych praw niż tworzenie kopii, zmiana schematu lub modyfikacja konfiguracji instancji. Nie należy umieszczać haseł bezpośrednio w pliku skryptowym ani przekazywać ich jako zwykłego tekstu w parametrach. W zależności od środowiska można użyć uwierzytelniania Windows, bezpiecznie przechowywanych poświadczeń PowerShell albo mechanizmów uwierzytelniania obsługiwanych przez konkretną wersję sterownika i modułu. Przed uruchomieniem automatyzacji warto ustalić, czy polityka wykonywania skryptów, profil użytkownika i wersja PowerShell nie zmieniają jej zachowania.

Diagnostyka problemów powinna obejmować zarówno PowerShell, jak i SQL Server. Należy odróżnić błąd rozwiązywania nazwy serwera, odrzucenie połączenia, brak uprawnień, błąd składni Transact-SQL, timeout oraz problem z miejscem docelowym kopii. Pomocne są jawne logi zawierające czas rozpoczęcia i zakończenia operacji, nazwę instancji, nazwę bazy, typ czynności oraz komunikat błędu, bez zapisywania haseł i innych sekretów. W przypadku opóźnień trzeba sprawdzić wartości timeoutów, blokady i obciążenie instancji, a nie tylko ponawiać polecenie. Tak przygotowana automatyzacja może wykonywać powtarzalne zadania administracyjne w sposób przewidywalny, pod warunkiem że jej zakres, uprawnienia, warunki przerwania i procedura weryfikacji są opisane przed wdrożeniem.