Wykonywanie zapytań w SQL Server z poziomu PowerShell

PowerShell jako dodatkowe narzędzie

Programiści i administratorzy SQL Server z pewnością wiedzą, co to jest Management Studio. To rozbudowane narzędzie pozwalające nie tylko na wykonywanie zapytań pisanych w języku SQL, ale również na wyklikanie pewnych podstawowych zadań związanych z bazami danych. Narzędzie jest na tyle dobre, że nie ma potrzeby szukania innego. Do czasu. Problemy zaczynają się pojawiać, gdy zachodzi potrzeba automatyzacji, przetwarzania danych pochodzących z niekonwencjonalnych źródeł lub wyjścia poza relacyjny model bazy danych. Są wprawdzie narzędzia typu Agent, są usługi SSIS (SQL Server Integration Services), ale warto poznać jeszcze jedno potężne narzędzie. Na tyle rozbudowane, że stosuje się je do zarządzania Active Directory, internetowymi usługami informacyjnymi (IIS), maszynami wirtualnymi. Mowa oczywiście o narzędziu PowerShell. Narzędzie to integruje się z bazą danych od wersji SQL Server 2005, a z każdą kolejną wersją, swoją oraz SQL Server, ma coraz większe możliwości. Co trzeba zrobić, aby z tych możliwości skorzystać?

Konfiguracja PowerShell do współpracy z SQL Server

Zanim będzie możliwe uruchomienie jakiejś funkcji PowerShell na silniku bazy danych należy przygotować sobie środowisko. Proces można opisać w trzech krokach:

• instalacja PowerShell,
• zdefiniowanie uprawnień,
• zaimportowanie modułu SQLPS.

Instalacja PowerShell może być zbędna. Serwerowe wersje systemu Windows będą już w ten ten komponent wyposażone. W przypadku braku, można go pobrać bezpłatnie - wpisując w wyszukiwarkę odpowiednie słowa kluczowe. Zaskakujące może być tylko to, że PowerShell przed instalacją nosi nazwę Windows Management Framework. Drugi krok to zdefiniowanie uprawnień. Znów może się okazać, że krok nie jest potrzebny. Domyślnym poziomem zabezpieczeń jest poziom Restricted, który nie pozwoli na wczytywanie konfiguracji i uruchomienie jakiegokolwiek skryptu. Import modułu SQLPS wymaga uprawnienia RemoteSigned. To uprawnienie pozwala na wczytanie konfiguracji uruchomienie komponentu pochodzącego z zewnątrz, ale podpisanego przez zaufaną instytucję. Aby zmienić poziom zabezpieczeń należy uruchomić następujący skrypt:

Set-ExecutionPolicy RemoteSigned

Po wpisaniu polecenia i naciśnięciu klawisza Enter otrzymamy komunikat:

Execution Policy Change
The execution policy helps protect you from scripts that you do not trust. Changing the execution policy might expose
you to the security risks described in the about_Execution_Policies help topic at
http://go.microsoft.com/fwlink/?LinkID=135170. Do you want to change the execution policy?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"):

Podsystem PowerShell informuje nas, że zmieniając poziom zabezpieczeń narażamy się na większe ryzyko i powinniśmy być świadomi zagrożeń. Aby skorzystać z modułu SQL Server należy zmianę zaakceptować klawiszem Y.

Czas na trzeci etap - do powłoki należy włączyć moduł. Robi się to poleceniem Import-Module SQLPS:

Import-Module SQLPS

Znów możemy otrzymać zaskakujący komunikat:

WARNING: The names of some imported commands from the module 'SQLPS' include unapproved verbs that might make them less
 discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose
parameter. For a list of approved verbs, type Get-Verb.

Komunikat oznacza, że polecenia wewnątrz modułu nie stosują przyjętej powszechnie konwencji nazewniczej. Nie należy się tym przejmować. Aby pominąć tego typu ostrzeżenia można dorzucić do poplecenia importu dodatkowy parametr:

Import-Module SQLPS -DisableNameChecking

Jeżeli wszystko się powiedzie, można przejść do konkretnej pracy z funkcjami SQL Server w PowerShell.

Wykonywanie zapytań na bazie

Podstawową funkcją wykorzystywaną do wykonywania zapytań jest Invoke-SqlCmd. W najprostszej konfiguracji przyjmuje ona dwa argumenty:

• Query - określa treść zapytania,
• Database - wskazuje bazę danych, na której zostanie wykonane zapytanie

Zapytanie będzie ponadto wykonane na domyślnej instancji serwera SQL. Gdybyśmy zechcieli wykonać polecenie na instancji nazwanej, należy dodatkowo przekazać parametr ServerInstance. Popatrzmy na przykładowe zapytanie:

Invoke-Sqlcmd -Query "SELECT * FROM Countries" -Database Test

Zakładając, że na domyślnej instancji mamy bazę Test i tabelę Countries (tabela pokazana w tym artykule: LIKE w Entity Framework), otrzymalibyśmy taki oto rezultat:

Name             FullName                        Population      Area
----             --------                        ----------      ----
Congo            Republic of the Congo              4366266    342000
Germany          Federal Republic of Germany       80523700    357021
Poland           Republic of Poland                38544513    312679
Spain            Kingdom of Spain                  46704314    505992
United Kingdom   United Kingdom of Great Br...     63181775    243610
USA              United States of America         316962000   9826675

Dane, mimo iż zostały wyświetlone w postaci tekstowej, są w rzeczywistości przekazywane jako obiekty i mogą być w dowolny sposób przetwarzane przez dowolne funkcje PowerShell.

Kopiowanie/eksport tabeli SQL do pliku

Mogliśmy się przekonać, w jaki sposób wykonać zapytanie SQL poprzez PowerShell, ale pokazany przykład nie pokazał drzemiących w powłoce możliwości. Pokażę to na przykładzie eksportu danych tabeli do pliku. Istnieje naturalnie polecenie bcp, którym można taki eksport wykonać, ale nie daje ono aż takiej swobody, jak PowerShell. O czym mówię? Popatrzmy na przykładowy skrypt:

Invoke-Sqlcmd -Query "SELECT * FROM Countries" -Database Test|Export-Csv "C:\Countries.csv"

Polecenie wygeneruje na dysku C plik z następującą zawartością:

"Name","FullName","Population","Area"
"Congo","Republic of the Congo","4366266","342000"
"Germany","Federal Republic of Germany","80523700","357021"
"Poland","Republic of Poland","38544513","312679"
"Spain","Kingdom of Spain","46704314","505992"
"United Kingdom","United Kingdom of Great Britain and Northern Ireland","63181775","243610"
"USA","United States of America","316962000","9826675"

A gdybyśmy teraz zechcieli wyeksportować dane w postaci XML? Nic prostszego:

Invoke-Sqlcmd -Query "SELECT * FROM Countries" -Database Test|Export-Clixml "C:\Countries.xml"

Otrzymamy plik z następującą zawartością:

<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>System.Data.DataRow</T>
      <T>System.Object</T>
    </TN>
    <ToString>System.Data.DataRow</ToString>
    <Props>
      <S N="Name">Congo</S>
      <S N="FullName">Republic of the Congo</S>
      <I32 N="Population">4366266</I32>
      <I32 N="Area">342000</I32>
    </Props>
  </Obj>
  ...
Pozostałe rekordy...
</Objs>

Nie jest to najlepiej sformatowany XML na świecie, ale jest. Aby osiągnąć lepiej sformatowany plik można się pogimnastykować w oknie PowerShell, ale można też nieco przerobić zapytanie:

SELECT * FROM Countries Country
FOR XML AUTO, ROOT('Countries')

Wartością zapytania będzie gotowy XML. Wystarczy go przekierować do pliku:

(Invoke-Sqlcmd
  -Query "SELECT * FROM Countries Country FOR XML AUTO, ROOT('Countries')" -Database Test)[0]
  | Out-File "C:\Countries.xml"

To nie wszystko! Gdybyśmy teraz zechcieli opublikować gotowy rezultat na stronie HTML, mozemy to zrobić w taki oto sposób:

Invoke-Sqlcmd -Query "SELECT * FROM Countries" -Database Test
  | Select -Property Name,FullName,Population,Area
  | ConvertTo-Html

Otrzymamy gotową stronę HTML:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>HTML TABLE</title>
</head><body>
<table>
<colgroup><col/><col/><col/><col/></colgroup>
<tr><th>Name</th><th>FullName</th><th>Population</th><th>Area</th></tr>
<tr><td>Congo</td><td>Republic of the Congo</td><td>4366266</td><td>342000</td></tr>
<tr><td>Germany</td><td>Federal Republic of Germany</td><td>80523700</td><td>357021</td></tr>
<tr><td>Poland</td><td>Republic of Poland</td><td>38544513</td><td>312679</td></tr>
<tr><td>Spain</td><td>Kingdom of Spain</td><td>46704314</td><td>505992</td></tr>
<tr><td>United Kingdom</td><td>United Kingdom of Great Britain and Northern Ireland</td><td>63181775</td><td>243610</td
></tr>
<tr><td>USA</td><td>United States of America</td><td>316962000</td><td>9826675</td></tr>
</table>
</body></html>

Strona będzie zawierała tylko tabelkę, którą przy okazji przedstawię:

Wystarczy do tego dodać jakiś styl i mamy gotowy mechanizm raportowania!

Podsumowanie

PowerShell to potężne narzędzie. W połączeniu z SQL Server te możliwości są jeszcze większe. Wspomniałem na początku, że IIS również ma swój moduł PowerShell. To z kolei pozwala na automatyczne publikowanie wygenerowanych stron HTML szerszej grupie odbiorców. Sam moduł SQL w PowerShell również pozwala na znacznie więcej niż zostało to tutaj pokazane. Można skryptować tworzenie kopii zapasowych, przebudowę i reorganizację indeksów, zarządzać kontami użytkowników w bazie. Same krypty PowerShell mogą być wykorzystywane przez drugą stronę - przez agenta SQL Server. Możliwości współpracy tych dwóch aplikacji, odpowiednio wykorzystane, dają wielką władzę.

Kategoria:SQL ServerPowerShell