Microsoft SQL Server

PowerShell dla administratorów SQL Server. Wybrane przykłady zastosowań – cz.1 Udostępnij na: Facebook

Opublikowano: 22 kwietnia 2009
Autor: Damian Widera

Zawartość strony
Krótki wstęp  Krótki wstęp
Obiekty SMO a PowerShell  Obiekty SMO a PowerShell
Podsumowanie  Podsumowanie

Krótki wstęp

PowerShell to nowy język skrytpowy firmy Microsoft, którego istnienia nie sposób pominąć. Można coprawda zadać sobie pytanie – po co uczyć się i poznawać kolejny język programowania? Czy administratorowi taka wiedza jest potrzebna? Odpowiedź na to pytanie jest w zasadzie prosta – za poznaniem PowerShell przemawia fakt, że język ten będzie interpretowany przez wszystkie platformy serwerowe firmy Microsoft. Drugą ważną zaletą jest, że PowerShell operuje na obiektach, a nie na łańcuchach tekstowych, jak ma to miejsce w przypadku innych powłok systemowych. Dobrym przykładem obiektu może być tabela, która posiada swoje własności, metadane oraz przechowuje informacje – dane. Dostęp do jej struktur oraz do danych może być bardzo prosty dzięki zastosowaniu języka PowerShell.

Konsola PowerShell może być bardzo przydatna administratorom baz danych i to nie tylko tych, którzy mają pod opieką najnowszą wersję SQL Server, ponieważ używając PowerShell można sprawnie zarządzać także wcześniejszymi wersjami systemu.

Artykuł ten jest kierowany do osób, które wiedzą co to jest konsola PowerShell, jaka jest składnia komendy i znają podstawy skryptowania w tym języku. W ramach Akademii SQL publikowanej na stronach TechNet można zapoznać się z podstawowymi informacjami, bez których zrozumienie materiału zaprezentowanego w tym artykule będzie niemożliwe.

Artykuł jest pierwszym z serii artykułów poświęconych jęzkowi PowerShell i jego zastosowaniom dla efektywnej pracy z SQL Server.

 Do początku strony Do początku strony

Obiekty SMO a PowerShell

SMO (ang. server management objects) to biblioteki napisane w języku .NET, dzieki którym uzyskać można dostęp do serwera bazy danych. Programista może korzystać z obiektów SMO używając wielu języków programowania, a PowerShell jest jednym z nich. Chcąc sprawnie posługiwać się obiektami SMO nie sposób obyć się bez dokumentacji, którą w najprostszym przypadku jest Books Online.

Niewątpliwą zaletą korzystania z bibliotek SMO jest, że można uzyskiwać informacje, zarządzać oraz programować zadania dla serwera SQL od wersji 7 wzwyż. Oznacza to, że skrypt napisany Pierwszą operacją, która musi zostać wykonana w konsoli PowerShell to załadowanie bibliotek SMO. Można to zrobić na kilka sposobów, ale trzy podstawowe to:

  1. wstawienie odpowiednich dyrektyw do profilu,
  2. umieszczenie komend w pliku skryptu, który zostanie uruchomiony,
  3. wpisanie komend w konsoli przed uruchomieniem „właściwych” skryptów:
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement ")

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.WmiEnum")

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Dmf")

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Sdk.Sfc")

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.PolicyEnum")

Umieszczenie powyższych komend w profilu spowoduje, że nie będzie potrzeby wpisywania ich ponownie po uruchomieniu konsoli. Zakładając, że załadowane zostały odpowiednie biblioteki, można uruchomić pierwszy skrypt, którego zadaniem będzie wypisanie wszystkich baz danych, które znajdują się w danej instancji serwera SQL. Skrypt przyjmuje trzy parametry wejściowe: nazwę serwera\instancji, nazwę użytkownika oraz hasło, a jego postać jest następująca:

param (

[string] $server,

[string] $user,

[string] $password

)

write-host "server "  $server

write-host "user "  $user

write-host "password "  $password



$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server=$server;Database=master;user=$user;password=$password"



$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = "select 'Servername: '+@@servername as Result union Select 'Version:'+

@@version as Result union select 'Database:' +name from sysdatabases as Result

order by Result desc "



$SqlCmd.Connection = $SqlConnection



$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)



$SqlConnection.Close()

$DataSet.Tables[0]

Po uruchomieniu skryptu w ekranie konsoli powinna zostać wyświetlona lista baz danych:

PowerShell dla administratorów SQL Server

Rysunek 1: Lista baz danych uzyskana po uruchomieniu skryptu.

Przy tej okazji warto zwrócić uwagę na dwie istotne :

  • położenie serwera baz danych,
  • mechanizmy bezpieczeństwa

Serwer, który został wskazany w pierwszym parametrze urochomionego skryptu nie musi mieć zainstalowanej konsoli PowerShell. Konsola ta może byc zaisnatlowana na lokalnym komputerze, z którego został uruchomiony skrypt. Jedynym warunkiem, który musi być spełniony w tym przypadku jest, aby serwer miał zainstalowane biblioteki SMO, które są częścią instalacji Native Client. Jeżeli nie zostały one zainstalowane, to można zrobić to w dowolnym momencie, np. używając płyty instalacyjnej SQL Server.

Bezpieczeństwo operacji, które zostały wykonane w skrypcie, jest gwarantowane poprzez wskazanie loginu i hasła. Uzytkownik, który nie będzie miał odpowiednich uprawnień, nie będzie mógł wykonać zamierzonej operacji. Przy okazji warto wspomnieć, że korzystając z obiektów SMO można wybrać pomiędzy uwierzytelnianiem Windows lub SQL.

Działanie kodu pokazanego w pierwszym przykładzie jest następujące:

  1. Tworzony jest obiekt SqlConnection, którego właściwość ConnectionString jest budowana w oparciu o parametry podane przy uruchomieniu skryptu,
  2. Tworzony jest obiekt SqlCommand, który zawiera zapytanie, które zostanie wykonane na serwerze,
  3. Obiekty SqlCommand i SqlConnection zostają ze sobą powiązane w taki sposób, że właściwość Connection obiektu SqlCommand jest obiektem SqlConnection.
  4. Tworzony jest obiekt SqlDataAdapter, który bazując na obiekcie SqlCommand pozwala pobrać interesujące nas dane i przekazać je do obiektu DataSet

Ciekawą odmianą zaprezentowanego powyżej skryptu, byłby analogicznie działający kod, który jednak sprawdzałby listę instancji baz danych przechowywaną w pliku, do którego ścieżkę należy wskazać jako parametru uruchomienia skryptu:

param (

[string] $file

)

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$computers = get-content $file

foreach ($computer in $computers)

{

write-host "Serwer :" $computer

write-host "-----------------------------------"



$SqlConnection.ConnectionString = "Server=$computer;Database=master;Integrated Security=True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = "select 'Servername: '+@@servername as Result union Select 'Version: '+@@version as Result union select 'Database:'

+name from sysdatabases as Result

order by Result desc "

$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

$DataSet.Tables[0]

}

Uruchomienie skryptu mogłoby wyglądać następująco:

DAMIAN-PC

DAMIAN-PC\NAMED1

DAMIAN-PC\SQLEXPRESS

W skrypcie skorzystałem z pętli foreach, w której każda linia pliku zawierającego nazwy instancji była analizowana oddzielnie. Nie wykonywałem żadnych dodatkowych działań polegających na sparwdzeniu, czy w pliku znajdują się rzeczywiście nazwy instancji serwera baz danych.

Trzecim z kolei przykładem, który może przydać się w codziennej pracy administratora jest skrypt, który pobiera z pliku listę instancji i sprawdza, które zadania usługi Agent nie powiodły się. Na rys.2. znajduje się przykładowy ekran po uruchomieniu skryptu:

PowerShell dla administratorów SQL Server

Rysunek 2 Skrypt wyświetlający zadania usługi Agent, które nie zakończyły się sukcesem.

Kod, który pozwala zrealizować to zadanie może mieć następującą postać:

foreach ($svr in get-content "C:\Temp\WRO\servers.txt")

{

write-host $svr

$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"

$srv.jobserver.jobs | where-object {$_.lastrunoutcome -eq "Failed" -and $_.isenabled -eq $TRUE} | format-table name,lastrunoutcome,lastrundate -autosize

}

W tym skrypcie można zauważyć, że dozwolone są takie instrukcje jak np. WHERE. Jest to również pierwszy przykład, w którym oprócz użycia obiektów SMO pokazałem w jaki sposób używać składni PowerShell do przekazywania obiektów pomiędzy kolejnymi operacjami na nich wykonywanymi:

obiekt | operacja-obiekt | operacja1-obiekt |.....| operacjaN - obiekt

W omawianym przypadku dla kolekcji jobs obiektu Server.Jobserver, która przechowuje wszystkie zadania usługi Agent, sprawdzany jest warunek:

$_.lastrunoutcome -eq "Failed"

który określa, czy dane zadanie usługi Agent zostało zakończone niepowodzeniem. Operator –eq (ang. equals) oznacza znak równości. Z kolei instrukcja:

$_.isenabled -eq $TRUE

określa, że interesujemy się tylko tymi zadaniami, które są dostępne (włączone). Ostatnią czynnością jest odpowiednie sformatowanie otrzymanych wyników za pomoca funkcji format-table (lub w skrócie ft):

-table name,lastrunoutcome,lastrundate -autosize

Oczywiście, to zadanie można rozwiązać w inny sposób np. konstruując druga pętlę foreach (lub inną), która iteracyjnie pobierałaby kolejne zadania usługi Agent i sprawdzała pozostałe warunki. Widać tutaj analogię do operacji na zbiorach (pierwszy przypadek) i kursorach (pętla).

O tym, jak efektywne jest łączenie obiektów SMO i funkcjonalności język PowerShell niech przekona kolejny skrypt, którego zadaniem jest wyświetlenie listy baz danych (dla podanej tym razem wprost instancji serwera) wraz z datą wykonania ostatniej kopii zapasowej:

$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') "DAMIAN-PC"

$db = $server.databases

$db | select-object Name, LastBackupDate

Przykładem, który nieco rozwija pokazaną powyżej funkcjonalność jest następujący kod:

$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') "DAMIAN-PC"

$db = $server.databases

$db_item = $db["AdventureWorks"]

if ( ((get-date)-($db_item.LastBackupDate)).days -gt 1 )

{

write-host $db_item potrzebuje backupu -background "RED" -foreground "WHITE"

}

else

{

write-host $db_item nie potrzebuje backupu -background "GREEN" -foreground "WHITE"

}

Można tutaj znaleźć zastosowanie trzech ciekawych funkcji oferowanych przez język PowerShell:

  1. Operacje na elementach kolekcji – w tym wypadku zmienna $db zawiera listę baz danych, ale zapis $db["AdventureWorks"] zawiera już tylko wskazaną bazę danych,
  2. Instrukcję if wewnątrz której odczytana jest data wykonania ostatniej kopii zapasowej i jej porównanie z aktualną datą. Jeżeli liczba dni, które upłynęły od wykonania kopii zapasowej jest większa niż 1, to warunek jest prawdziwy i tło konsoli zmieni się na czerwone (patrz punkt poniżej). Operator –gt (ang. greater than) oznacza znak większości, czyli > : ((get-date)-($db_item.LastBackupDate)).days -gt 1
  3. Możliwość zmiany tła i koloru czcionki konsoli – co pozowli natychmiast odnaleźć nieprawidłowe wyniki.

Zaprezentowany kod można zmodyfikować na conajmniej kilka sposobów, co pozwoliłoby na dostosowanie go własnych potrzeb:

  • Pobierać listę instancji z pliku bądź parametru wejściowego
  • Pobierać nazwę bazy danych, która ma zostać sprawdzona, z parametru wejściowego
  • Pobierać listę baz danych i wykonywać powyższy kod dla wszystkich baz wstawiając pętlę foreach.
  • W przypadku, kiedy baza danych nie ma aktualnej kopii zapasowej – wykonać ją.

Celem kolejnego przykładu jest wykonanie kopii zapasowej wybranej bazy danych. Dobry i rozbudowany przykład takiej funkcji, napisany w języku PowerShell, można znaleźć w książce „Serwer SQL 2008: Administracja i programowanie”, natomiast w ramach tego artykułu pokazałem prostszą wersję takiego modułu, który zawsze wykona pełną kopię zapasową. Skrypt ten wymaga podania jednego argumentu, którym będzie nazwa bazy danych:

$file = "C:\Temp\WRO\" + $args[0] + ".bak"

$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') "DAMIAN-PC"

$smo = 'Microsoft.SqlServer.Management.Smo.'



$backup = new-object ($smo + 'backup')

$backup.Database = $args[0]

$backup.Devices.AddDevice($file, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

$backup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database

$backup.SqlBackup($server)

W pierwszej linii skryptu tworzona jest ścieżka do pliku kopii zapasowej. Należy pamiętać, że użytkownik, który będzie wykonywał kopię zapasową, musi mieć uprawnienia do zapisu do folderu, w którym kopia sie utworzy. Kolejnym ciekawym obiektem, który nie został jescze omówiony we wcześniejszych przykładach, jest obiekt $backup, który jest klasy Microsoft.SqlServer.Management.Smo.Backup. Konieczne jest wypełnienie kilku jego własności, np. wskazanie , która baza danych zostanie zachowana, jakiego typu będzie to kopia zapasowa oraz jaki bedzie typ kopii zapasowej. Jako ostatnią akcję należy wykonać metodę SqlBackup, która rozpocznie tworzenie kopii zapasowej bazy danych.

Na kolejnym przykładzie pokazałem, w jaki sposób można w skryptach PowerShell oczekiwać na interakcję osoby uruchamiającej skrypt. W tym przypadku użytkownik musi wprowadzić nazwę serwera SQL, do którego należy się podłaczyć, podać nazwę bazy danych oraz informacje umozliwiajace połączenie. Skrypt, którego zadaniem jest wyświetlenie wszystkich tabel użytkownika dla wskazanej bazy danych ma postać:

$server=Read-Host "Podaj nazwe serwera SQL"

$db=Read-Host "Podaj nazwe bazy danych"

$user=Read-Host "Podaj nazwe uzytkownika"

$pass=Read-Host "Wprowadz haslo"



$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server=$server;Database=$db;user=$user;password=$pass"



$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = "select name from sysobjects where type='u'"

$SqlCmd.Connection = $SqlConnection



$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $SqlCmd



$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)



$SqlConnection.Close()

$DataSet.Tables[0]

W efekcie uruchomienia omawianego skryptu otrzymałem następujący wynik:

PowerShell dla administratorów SQL Server

Rysunek 3 Skrypt PowerShell, w którym wymagana jest interakcja użytkownika.

Każdy obiekt serwera baz danych posiada swoje własności. Warto umieć je bardzo szybko odczytać tak, jak pokazałem to na poniższym przykładzie, w którym odczytałem wszystkie własności tabeli Employees:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$server=New-Object "Microsoft.SqlServer.Management.Smo.Server"   "DAMIAN-PC"

$db = $server.Databases["Northwind"]

$table = $db.tables["Employees"]

$table.Properties

Po uruchomieniu tego fragmentu kodu okaże się, że własności dla tabeli Employees zostały zaprezentowane w formie listy, co znacznie utrudnia ich analizę. Można z tą sytuacją poradzić sobie na kilka sposobów i na przykład wyświetlić tylko generalne informacje o tabeli Employees wpisując w skrypcie:

$table

Można także wyświetlić listę dostępnych metod i własności tabeli używając komendy Get-Member, która przydaje się w każdej sytuacji, w której nie wiadomo co należy zrobić dalej (dodatkowa opcja more wyświetla tylko tyle wyników, ile zmieści się na ekranie monitora):

$table | Get-Member |more

Dopiszmy w takim razie jeszcze jedną linię do omawianego skryptu, w której odczytamy tylko wybrane, najbardziej nas interesujące własności tabeli, takie jak liczba wierszy, datę utworzenia tabeli, statystyki oraz indeksy i przedstawie je w formie tabelarycznej:

$table |ft Name, RowCount, CreationDate, Statistics, Indexes

Liczba wierszy w tabelach jest bardzo często poszukiwaną informacją zarówno dla administratorów, jak i dla programistów, przekazuje bowiem ilościowe informacje na temat obiektu. Korzystając z możliwości języka PowerShell można tę informację uzyskać w bardzo prosty sposób, jak pokazałem na poniższym fragmencie kodu:

$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') "DAMIAN-PC"

$tables = $server.databases["Northwind"].Tables

$tables | sort-object -Property Rowcount -desc| select-object -first 12 | ft Name, RowCount –AutoSize

Zmienna — obiekt $tables przechowuje kolekcje tabels bazy danych Northwind. Następnie korzystam z dwóch komend:

  1. sort-object, która posortuje obiekt przekazany z lewej strony znaku | (a więc kolekcję tabel w bazie Northwind) względem podanych kryteriów. W tym przypadku funkcja posortuje table malejąco względem liczby posiadanych przez niew wierszy,
  2. select-object, która z listy posortowanych obiektów wybierze te obiekty, które spełniają podane warunki. W tym przypadku należało wybrać 10 tabel. Liczba 12 podana w komendzie oznacza 10 tabel oraz 2 wiersze przeznaczone na nagłówek, który zostanie wyświetlony na ekranie.

W ostatnim przykładzie tej części artykułu chciałem pokazać, w jaki sposób można zeskryptować obiekty w SQL Server. Ta operacja powinna zakończyć się powstaniem pliku tekstowego, w którym będzie można znaleźć definicje obiektów. Operacja zapisywania informacji uzyskiwanych za pomocą języka PowerShell nie sprowadza się tylko do utworzenia pliku tekstowego, ponieważ do wyboru jest też możliwość zapisu do pliku csv lub html (można więc generowac proste raporty i umieszczać je bezpośrednio na serwerze www).

Skryptowanie pozwoli odtworzyć strukturę obiektów np. na innym serwerze lub może posłużyć do kontroli zmian w tych obiektach, ponieważ można porównać pliki zawierające definicje obiektów (zobacz komendę Compare-Objects). Za zapis obiektów do pliku, a w tym wypadku są to wszystkie tabele bazy danych Northwind, posłuży komenda out-file, do której w najprostszym przypadku należy podać informację gdzie plik ma powstać. W tym wypadku warto też dodać atrybut –append, który pozwoli na dopisywanie informacji do istniejącego pliku.

$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') "DAMIAN-PC"

$tables = $server.databases["Northwind"].Tables

foreach($tab in $tables)

{

$tab.Script()|out-file -filepath C:\Temp\WRO\TableScript.txt –append

}

Podsumowanie

W artykule przedstawiłem niektóre, aspekty praktycznego zastosowania języka PowerShell do zarządzania i programowania SQL Server. Prezentowane fragmenty kodu były łatwe i niezbyt długie, ale to w zupełności powinno wystarczyć, aby przekonać o możliwościach, które oferuje PowerShell. Nie są to oczywiście jedyne możliwości tego języka. Mając na uwadze przyszłość, jaka otwiera się przed PowerShell należy poważnie zastanowić się nie tylko nad przyswojeniem sobie reguł, które tym językiem rządzą, ale również nauczyć się używac go na co dzień.


Damian Widera Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET)
Od 8 lat zajmuje się projektowaniem, tworzeniem i wdrażaniem aplikacji wykorzystujących platformę .NET, SQL Server oraz Oracle. Obecnie pracuje jako project manager dla LGBS Polska. Pracował także jako trener, programista, administrator baz danych, twórca domumentacji oraz analityk biznesowy. Aktywnie współpracuje z polskim oddziałem Microsoft publikując atykuły, webcasty oraz porady z zakresu SQL Server na stronach TechNet. Jest współautorem książki „Serwer SQL 2008. Administracja i programowanie”.

Speaker na wielu konferencjach, m.in. Microsoft Heroes Happen Here, C2C, European PASS Conference, Microsoft Technology Summit, Energy Launch, TechED. Od 2004 r. posiada certyfikaty firmy Microsoft: MCT, MCITP–DBA oraz MCSD.NET. Jest współtwórcą oraz liderem jednej z najwiekszych grup pasjonatów SQL Server w Polsce – Śląskiej Regionalnej Grupy Microsoft (PLSSUG Katowice). Od listopada 2008 jest prezesem Polish SQL Server Users Group (PLSSUG) w Polsce. W styczniu 2009 nagrodzony tytułem MVP w kategorii SQL Server.
 Do początku strony Do początku strony

Microsoft SQL Server