Использование SQL Server Management Objects с PowerShell

С момента выхода в свет оболочки PowerShell появилась возможность использовать пространство имен SMO и создавать замечательные сценарии с помощью PowerShell.

В наше время большинство серверных продуктов компании Microsoft выпускаются в сопровождении соответствующего модуля команд Windows PowerShell, используемых для управления этими продуктами. Так, наборы команд существуют для систем Microsoft Exchange Server, Microsoft SharePoint и Microsoft Lync. Однако для системы SQL Server их создано очень мало. На самом деле, для большинства задач, выполняемых профессионалами при работе с базами данных SQL Server, команд не существует. По умолчанию нет команд для управления ядром базы данных, работы с базами данных и таблицами или для управления заданиями SQL Server Agent.

Пока не появились доступные команды PowerShell для системы SQL Server, вы можете использовать объекты SQL Server Management Objects (SMO). Появившиеся в системе SQL Server 2005 как часть пакета SQL Server Features Pack, объекты SMO были разработаны для управления системой SQL Server посредством программирования. Пространство имен SMO содержит набор классов Microsoft. NET Framework для основных объектов SQL Server Database Engine. До появления оболочки PowerShell эти классы. NET предназначались для использования в приложениях. NET, написанных на языках Visual Basic. NET или C#. Но с момента появления PowerShell существует возможность использования пространства имен и создания замечательных сценариев с помощью оболочки PowerShell.

Изучение принципов использования объектов SMO – важное дополнение к набору ваших навыков. Разобраться в технологиях. NET Framework тоже необходимо. Если когда-нибудь вам придется создавать функцию SQL Server Common Language Runtime (CLR) или пакет SQL Server Integration Services (SSIS), требующий использования компонента Script Task, вам пригодится понимание основ технологии. NET. Знание основ помогает даже в работе с языком PowerShell, так как он является языком написания сценариев, построенным на платформе. NET. Специалисты Microsoft хорошо потрудились над документированием объектов SMO и технологии. NET Framework на ресурсе MSDN. Врезка «Маршрут обучения» содержит ссылки на некоторые из документов.

Набор SMO имеет логическую структуру, которую большинство специалистов по работе с базами данных SQL Server найдут понятной и относительно простой в использовании. Далее я покажу несколько примеров использования объектов SMO с помощью PowerShell.

Знакомство с требованиями

Если вы новичок в работе с PowerShell, я рекомендую вам использовать версию PowerShell 2.0 или выше, так как она включает среду подготовки сценариев Windows PowerShell Integrated Scripting Environment (ISE). Данный механизм позволяет вам запускать команды и писать, редактировать и отлаживать сценарии в графической оболочке. Однако прежде чем вы сможете начать запускать команды и писать сценарии, нужно убедиться, что оболочка PowerShell готова. Вот несколько рекомендаций, как подготовить окружение, если вы используете версию PowerShell 2.0 или выше.

• Убедитесь, что все необходимые компоненты PowerShell установлены. Список компонентов может сильно отличаться в зависимости от версии операционной системы. Информацию о компонентах и других требованиях к оболочке PowerShell 2.0 вы найдете в статье «Windows Management Framework (Windows PowerShell 2.0, WinRM 2.0, and BITS 4.0)» (http://support.microsoft.com/kb/968929). Информацию о компонентах и других требованиях к оболочке PowerShell 3.0, можно найти в статье «Windows Management Framework 3.0» (http://www.microsoft.com/en-us/download/details.aspx?id=34595).
• Проверьте, установлен ли компонент.NET Framework 3.5, благодаря которому вы сможете использовать среду Windows PowerShell ISE. Без данного компонента среда работать не будет.
• Если это не было сделано ранее, запустите следующую команду из консоли (данное действие позволит вам выполнять сценарии):

Set-ExecutionPolicy RemoteSigned

• Убедитесь, что установлен компонент SQL Server Management Studio (SSMS) версии 2005 или выше.

Если вы работаете с экземплярами, использующими различные версии системы SQL Server, я рекомендую воспользоваться последней версией SMO. Если вы не хотите устанавливать на компьютер полную версию системы SQL Server, но хотите работать с базой данных посредством сценариев, можно установить следующие компоненты из пакета SQL Server Feature Pack в указанном порядке.

1. Установите шрифты Microsoft System CLR Types for Microsoft SQL Server 20xx.
2. Установите объекты Microsoft SQL Server 20xx Shared Management Objects.
3. Установите расширения Microsoft Windows PowerShell Extensions for Microsoft SQL Server 20xx. Это компонент стал доступен после выхода системы SQL Server 2008, но версия SQL Server 2012 включает новый модуль PowerShell SQLPS, отсутствовавший в предыдущих версиях.

Создание первого сценария PowerShell SMO

Убедившись, что все необходимые условия выполнены, вы можете переходить к созданию своего первого сценария PowerShell SMO. Во-первых, необходимо открыть сеанс консоли PowerShell и загрузить сборку SMO, которая позволит компилировать объекты SQL Server. NET. Существует три способа загрузки сборки Microsoft.SqlServer.Smo, в зависимости от используемой оболочки.

Метод 1. Если вы используете оболочку PowerShell 1.0, вы можете загрузить сборку.NET для данной версии PowerShell с помощью команды:

[System.Reflection.Assembly]::LoadWithPartialName
(«Microsoft.SqlServer.Smo»);

Хотя эта команда в статье разделена на несколько строк, в консоли PowerShell необходимо вводить ее целиком в одной строке. Эта команда соберет последние объекты SMO, установленные в системе. Вы также можете использовать этот метод для оболочки PowerShell 2.0 и более новых версий.

Метод 2. В версиях PowerShell 2.0 и выше вы можете задействовать команду Add-Type для загрузки сборки Microsoft.SqlServer.Smo:

Add-Type -AssemblyName «Microsoft.SqlServer.Smo»;

К сожалению, эта команда не работает, если на ваших компьютерах установлено несколько версий системы SQL Server. Кроме того, вы можете столкнуться с сообщением об ошибке, при запуске команды для отдельных экземпляров с системой SQL Server 2012. Это известная ошибка, информация о которой передана в компанию Microsoft.

Метод 3. Это обходной путь в случае, когда используется версия PowerShell 2.0 или выше, но метод 2 не срабатывает. При использовании метода 3 в команде используется полный путь к сборке SMO:

Add-Type -path `
«C:WindowsassemblyGAC_MSILMicrosoft.SqlServer.Smo
10.0.0.0__89845dcd8080cc91Microsoft.SqlServer.Smo.dll»;

Хотя эта команда в статье разделена на несколько строк, в консоли PowerShell ее необходимо вводить также целиком в одной строке.

После загрузки сборки требуется создать объект SQL Server. NET, который будет подключаться к экземпляру SQL Server. Вы можете создать такой объект с помощью кода:

$SQLSvr = «.";
$MySQLObject = new-object `
Microsoft.SqlServer.Management.Smo.Server `
$SQLSvr;

Этот код подключается к локальному экземпляру с помощью символа точки (».«) и создает новый объект с именем $MySQLObject. Кроме того, вы можете подключаться к экземпляру, указывая имя сервера в одинарных или двойных кавычках (например,»YourSqlServer1«, 'YourSqlServer1') или полное имя экземпляра в одинарных или двойных кавычках (например,»YourSqlServer1InstanceXX«, 'YourSqlServer2InstanceYY').

На примере имени сервера и полного имени экземпляра видно, что вы можете использовать как одинарные, так и двойные кавычки для выделения строк в оболочке PowerShell. Однако они не всегда взаимозаменяемы. Лучше всего проиллюстрировать данную мысль на примере. Предположим, вы хотите сохранить пароль $GalaPrem1r! в переменной $MyPassword, а потом просмотреть его. Если вы используете одиночные кавычки для выделения строки ('$GalaPrem1r!') и запускаете команду

$MyPassword = '$GalaPrem1er!'; $MyPassword;

оболочка PowerShell возвращает корректное значение $GalaPrem1er!. Однако если вы используете двойные кавычки для выделения строки (»$GalaPrem1r!«) и запускаете команду:

$MyPassword =»$GalaPrem1er!«; $MyPassword;

оболочка PowerShell возвращает только восклицательный знак (!).

Почему оболочка PowerShell возвращает различные значения, когда вы заключаете пароль в одиночные ('$GalaPrem1er!') и двойные (»$GalaPrem1er!«) кавычки? Когда для выделения строки используются одиночные кавычки, оболочка PowerShell игнорирует любые специальные символы в этой строке, в том числе специальные символы, зарезервированные для нужд PowerShell. В данном примере оболочка игнорирует знак доллара ($) в строке $GalaPrem1er!. В синтаксисе PowerShell доллар – это специальный символ, используемый как префикс для идентификации имен переменных. Когда для выделения строки используются двойные кавычки, оболочка PowerShell не игнорирует специальные символы в этой строке. Поэтому когда для выделения строки $GalaPrem1er! мы используем двойные кавычки, оболочка PowerShell рассматривает выражение $GalaPrem1er как имя переменной, а не как строку, и возвращает только восклицательный знак. Как видно из этого примера, необходимо уделить повышенное внимание тому, каким образом вы описываете и используете строки.

Теперь вы подключились к экземпляру SQL Server и можете извлечь из него информацию. Но как получить доступ к информации объекта $MySQLObject системы SQL Server? Вот здесь магия объектов SMO и вступает в силу.

Магия объектов SMO

Чтобы увидеть содержимое объекта $MySQLObject, нужно разобраться с командой PowerShell Get-Member, имеющей псевдоним gm. Вы можете просмотреть документацию по данной команде, запустив следующую команду:

Get-Help Get-Member –full;

После того, как вы разберетесь с командой Get-Member, выполните ее в связке с объектом $MySQLObject следующим образом:

$MySQLObject | gm | more;

Эта команда отображает все объекты. NET, хранящиеся в вашей переменной PowerShell — в данном случае, все объекты экземпляров SQL Server. Вы увидите все свойства и методы объекта. NET. Вы можете рассматривать свойство как контейнер, содержащий одно или несколько статических значений, а метод – как функцию, изменяющую поведение объекта.

Команды такого типа часто называют»однострочными«. Горизонтальную линию называют «конвейером», и ее задачей является передача значений. В данном случае первый конвейер передает объект $MySQLObject в команду Get-Member. Второй конвейер передает результат выполнения команды Get-Member в команду More. Как показано на экране 1, команда More ограничивает отображение информации одним экраном и добавляет запрос More, позволяющий просматривать результаты «поэкранно».

Экран 1. Использование команды more

Символ «двоеточие» (;) указывает на конец строки, его использовать необязательно.

Если вы не хотите перегружать свою консоль лишней информацией, то можете заменить команду More на команду Out-Gridview:

$MySQLObject | gm | Out-Gridview;

Как показано на экране 2, результаты отображаются в интерактивной таблице, расположенной внутри окна-таблицы. Допускается использование и команды More, и команды Out-Gridview, так что вы можете задействовать тот механизм, который вам больше подходит.

Экран 2. Вывод результатов в таблицу

После просмотра свойств и методов, полагаю, вы захотите отобразить значения, хранимые в свойстве Information. Для этого воспользуйтесь командой:

$MySQLObject.Information;

Как можно понять, глядя на экран 3, вы только что открыли «сундук» с информацией о вашем экземпляре SQL Server, с помощью всего пары строчек кода.

Экран 3. Информация об экземпляре SQL Server

Если вы хотите выбрать определенные фрагменты и структурировать их в таблице, вы можете использовать команды оболочки PowerShell Select-Object (псевдоним select) и Format-Table (псевдоним FT). Например, вы можете задействовать следующую команду для отображения значений Parent, Product, Edition и VersionString в формате таблицы:

$MySQLObject.Information `
| Select Parent, Product, Edition, VersionString `
| FT -auto;

Параметр -auto команды Format-Table автоматически настраивает ширину и количество столбцов, исходя из размера данных. Результат показан на экране 4.

Экран 4. Параметр -auto команды Format-Table автоматически настраивает ширину и?количество столбцов

Получение информации от нескольких экземпляров SQL Server

До сих пор речь шла о том, как получить данные из одного экземпляра SQL Server. Теперь я покажу, как получить информацию из нескольких экземпляров SQL Server.

В листинге 1 в качестве примера приведен сценарий, демонстрирующий, как можно использовать объекты SMO для получения списка всех экземпляров SQL Server и определенной информации по каждому из них. Для получения списка всех экземпляров сценарий использует метод EnumAvailableSqlServers() класса SmoApplication, который является частью пространства имен Microsoft.SqlServer.Management.Smo. Далее сценарий использует выражение Foreach оболочки PowerShell для циклической обработки списка и получения определенной информации о каждом экземпляре. На экране 5 приведен пример результата выполнения данного сценария.

Экран 5. Получение информации о нескольких экземплярах

Листинг 2 представляет собой измененный вариант предыдущего сценария. Вместо сбора информации из всех экземпляров SQL Server сценарий в листинге 2 собирает информацию из экземпляров, указанных в списке. Информация от каждого сервера сохраняется в переменной. Содержимое переменной выводится на экран в конце. На экране 6 приведен пример результатов выполнения данного сценария.

Экран 6. Результаты работы сценария 2

Получение информации о базах данных и заданиях SQL Server Agent

Как я отмечал выше, система SQL Server не поставляется со встроенными командами оболочки PowerShell для работы с базами данных и управления заданиями SQL Server Agent. Давайте посмотрим, как использовать объекты SMO для устранения этих недоработок.

Листинг 3 описывает сценарий, получающий информацию о базах данных. Хотя он возвращает значения только трех свойств (например, Owner, Recovery Model и Status), переменная $MySQLObject будет иметь больше свойств, чем вы когда-либо посчитаете нужным вывести на экран. Имейте в виду, что этот сценарий использует команду Out-Null для удаления результатов команды, загружающей сборку. NET. Это позволяет получить более «чистые» результаты (см. экран 7).

Экран 7. Информация о базах данных

В Листинге 4 приведен сценарий, возвращающий информацию о заданиях SQL Server Agent. Этот сценарий использует инкапсуляцию объекта —($MySQLObject.JobServer.Job), предоставляющую возможность навигации внутри набора объектов. NET. В данном случае переменная $MySQLObject имеет свойство-объект с именем JobServer. Объект JobServer также имеет свойства-объекты, один из которых получил имя Job. Помимо возможности навигации, вы можете использовать инкапсуляцию объекта для того, чтобы помочь избежать распада главного объекта переменной ($MySQLObject) на другие нежелательные переменные. Использовать круглые скобки не обязательно, но они делают более понятным факт инкапсуляции объекта.

Как показано на экране 8, сценарий в Листинге 4 возвращает лишь некоторые из доступных свойств объекта Job.

Экран 8. Часть информации о задании SQL Server Agent

При желании вы можете использовать команду Get-Member для просмотра всех доступных свойств и методов:

($MySQLObject.JobServer.Job) | Get-Member;

После того, как вы решите, какие свойства хотите отображать в консоли, можно ввести команду Select-Object для получения этих свойств.

Приобретайте новые навыки

Использование объектов SMO при помощи оболочки PowerShell дает вам возможность создавать сценарии для документирования экземпляров SQL Server и управления ими. Почти любой процесс, выполняемый вручную, может быть конвертирован в автоматизированную задачу PowerShell. Вы даже можете задействовать планировщик Windows Task Scheduler или службу SQL Server Agent для планирования выполнения своих сценариев. Изучение совместного использования оболочки PowerShell и объектов SMO даст вам ряд новых навыков, благодаря которым вы станете более сильным специалистом в области работы с базами SQL Server.

## Загрузка сборки. NET.
[System.Reflection.Assembly]::LoadWithPartialName( `
»Microsoft.SqlServer.Smo«);
## Построение списка экземпляров SQL Server.
$SQLSvr = `
[Microsoft.SqlServer.Management.Smo.SmoApplication] `
: :EnumAvailableSqlServers($false) | Select name;
$SQLSvr;
## Для каждого экземпляра SQL Server отобразить информацию.
foreach($svr in $SQLSvr)
{
## Сборка объекта. NET для экземпляра SQL Server.
$MySQLObject = `
new-object Microsoft.SqlServer.Management.Smo.Server `
$svr.Name;
## Работаем с SMO и базами данных.
$MySQLObject.Information | `
Select Parent, Product, Edition, VersionString `
| FT -auto;
};

## Загрузка сборки. NET.
[System.Reflection.Assembly]::LoadWithPartialName( `
»Microsoft.SqlServer.Smo«);
## Загрузка списка имен экземпляров SQL Server.
$SQLSvr =»BIZTALK2K10DEV«,"WIN2K3SQL1»,«WIN1PROUS01»;
## Для каждого экземпляра SQL Server отобразить информацию
## и сохранить ее в переменной PowerShell.
$Results = $null;
$Results = foreach($svr in $SQLSvr)
{
## Сборка объекта. NET для экземпляра SQL Server..
$MySQLObject = `
new-object Microsoft.SqlServer.Management.Smo.Server `
$svr;
## Работаем с SMO и базами данных.
$MySQLObject.Information | `
Select Parent, Product, Edition, VersionString;
};
## Отображение результатов на экране
$Results | ft -autosize;

## Загрузка сборки. NET.
[System.Reflection.Assembly]::LoadWithPartialName( `
«Microsoft.SqlServer.Smo») | Out-Null;
## Построение списка имен экземпляров SQL Server.
$MySQLObject = `
new-object Microsoft.SqlServer.Management.Smo.Server `
«SQL01»;
$MySQLObject.databases `
| Select parent, name, Owner, `
RecoveryModel, Status | FT -AutoSize;

## Загрузка сборки. NET.
[System.Reflection.Assembly]::LoadWithPartialName( `
«Microsoft.SqlServer.Smo») | Out-Null;
## Построение списка имен экземпляров SQL Server.
$MySQLObject = `
new-object Microsoft.SqlServer.Management.Smo.Server `
«SQL01»;
($MySQLObject.JobServer.jobs) `
| Select Parent, Name, isEnabled, `
lastRunDate, lastRunOutCome `
| ft -Auto;