Статья
04/01/2012

Использование поставщика SQL Server для PowerShell

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

Иерархия SQL Server в PowerShell

Продукты, в которых модели данных или модели объектов можно представить в иерархическом виде, используют для представления таких иерархий поставщики Windows PowerShell. Иерархия отображается при помощи диска и структуры пути, похожей на ту, которая используется в файловой системе Windows.

Каждый поставщик Windows PowerShell реализует один или несколько дисков. Каждый диск является корневым узлом в иерархии связанных объектов. В поставщике SQL Server реализован диск «SQLSERVER:» . На диске SQLSERVER: есть четыре основные папки. Каждая папка и вложенные в нее папки представляют набор объектов, к которым можно получить доступ с помощью модели управляющих объектов SQL Server. Если выделена вложенная папка в пути, начинающемся с одной из этих папок, можно использовать методы из связанной объектной модели для выполнения действий с объектом, который представлен вложенной папкой. Папки Windows PowerShell, реализуемые поставщиком SQL Server 2008 R2, перечислены в следующей таблице.

Папка	Пространство имен объектной модели SQL Server	Объекты
SQLSERVER:\SQL	Microsoft.SqlServer.Management.Smo Microsoft.SqlServer.Management.Smo.Agent Microsoft.SqlServer.Management.Smo.Broker Microsoft.SqlServer.Management.Smo.Mail	Объекты базы данных, такие как таблицы, представления и хранимые процедуры.
SQLSERVER:\SQLPolicy	Microsoft.SqlServer.Management.Dmf Microsoft.SqlServer.Management.Facets	Объекты управления на основе политик, такие как политики и аспекты.
SQLSERVER:\SQLRegistration	Microsoft.SqlServer.Management.RegisteredServers Microsoft.SqlServer.Management.Smo.RegSvrEnum	Зарегистрированные объекты серверов, такие как группы серверов и зарегистрированные серверы.
SQLSERVER:\Utility	Microsoft.SqlServer.Management.Utility	Вспомогательные объекты, такие как управляемые экземпляры компонента Database Engine.
SQLSERVER:\DAC	Microsoft.SqlServer.Management.DAC	Объекты приложения уровня данных, такие как пакеты DAC, и операции, такие как развертывание DAC.
SQLSERVER:\DataCollection	Microsoft.SqlServer.Management.Collector	Объекты сборщика данных, такие как наборы сбора и хранилища конфигураций.

Например, папку SQLSERVER:\SQL можно использовать, чтобы начинать пути, которые могут представлять любой объект, поддерживаемый объектной моделью SMO. Начальная область пути SQLSERVER:\SQL — SQLSERVER:\SQL\ComputerName\InstanceName. Необходимо указать имя компьютера. Для локального компьютера можно указать как localhost, так и `(local`). Имя экземпляра необходимо указывать всегда, даже для экземпляров по умолчанию. Для экземпляров по умолчанию указывается слово DEFAULT. Узлы, расположенные после имени экземпляра, представляют чередующуюся последовательность классов объектов (например, Database и View) и имен объектов (например, AdventureWorks2008R2). Схемы не представляются в качестве классов объектов. Если указывается узел для объекта верхнего уровня в схеме, такого как таблица или представление, необходимо указать имя объекта в формате ИмяСхемы.ИмяОбъекта.

Путь к таблице Vendor в схеме Purchasing базы данных AdventureWorks2008R2 в экземпляре компонента Database Engine по умолчанию на локальном компьютере.

SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Tables\Purchasing.Vendor

Дополнительные сведения об иерархии модели объектов SMO см. в разделе Схема модели объектов SMO.

Узлы классов объектов на пути связаны с классом коллекции в связанной модели объектов. Узлы имен объектов связаны с классом объектов в связанной модели объектов, как в следующей таблице.

Путь	Класс SMO
SQLSERVER:\SQL\МойКомпьютер\DEFAULT\Databases	DatabaseCollection
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks2008R2	Database

Каждый раз, когда в пути упоминается экземпляр компонента Database Engine, поставщик SQL Server использует объект SMO, чтобы установить с экземпляром соединение, использующее проверку подлинности Windows. Соединение устанавливается с помощью данных учетной записи Windows, с которой работает сеанс Windows PowerShell. Поставщик SQL Server не использует проверку подлинности SQL Server.

Переход по путям SQL Server

В Windows PowerShell реализованы командлеты для перемещения по иерархиям поставщиков и выполнения основных операций с текущим объектом. Поскольку командлеты используются часто, они обладают краткими каноническими псевдонимами. Также существует один набор псевдонимов, сопоставляющий командлеты с похожими командами командной строки, и другой набор для команд оболочки UNIX.

Поставщик SQL Server реализует подмножество командлетов поставщика, приведенных в следующей таблице.

Командлет	Канонический псевдоним	Псевдоним командной строки	Псевдоним оболочки UNIX	Описание
Get-Location	gl	pwd	pwd	Возвращает текущий узел.
Set-Location	sl	cd, chdir	cd, chdir	Изменяет текущий узел.
Get-ChildItem	gci	dir	ls	Перечисляет объекты, хранящиеся в текущем узле.
Get-Item	gi			Возвращает свойства текущего элемента.
Rename-Item	rni	rn	ren	Переименовывает объект.
Remove-Item	ri	del, rd	rm, rmdir	Удаляет объект.

Например, чтобы получить список экземпляров SQL Server, доступных путем перехода к папке SQLSERVER:\SQL и запроса списка дочерних элементов для папки, можно использовать один из следующих наборов командлетов или псевдонимов.

С помощью полных имен командлетов:
```
Set-Location SQLSERVER:\SQL
Get-ChildItem
```
С помощью канонических псевдонимов:
```
sl SQLSERVER:\SQL
gci
```
С помощью псевдонимов командной строки:
```
cd SQLSERVER:\SQL
dir
```

С помощью псевдонимов оболочки UNIX:

cd SQLSERVER:\SQL
ls

Важно!
Некоторые идентификаторы SQL Server (имена объектов) содержат символы, которые не поддерживаются в именах путей Windows PowerShell. Дополнительные сведения об использовании имен, содержащих такие символы, см. в разделе Использование идентификаторов SQL Server в PowerShell.

Некоторые идентификаторы SQL Server (имена объектов) содержат символы, которые не поддерживаются в именах путей Windows PowerShell. Дополнительные сведения об использовании имен, содержащих такие символы, см. в разделе Использование идентификаторов SQL Server в PowerShell.

Использование Get-ChildItem

Данные, возвращаемые командлетом Get-ChildItem (или его псевдонимами dir и ls), зависят от текущего расположения в SQLSERVER: пути SQL.

Положение на пути	Результаты выполнения Get-ChildItem
SQLSERVER:\SQL	Возвращает имя локального компьютера. Если соединения с экземплярами компонента Database Engine на других компьютерах устанавливалось с помощью объектов SMO или инструментария WMI, также будут приведены имена этих компьютеров.
SQLSERVER:\SQL\ComputerName	Список экземпляров компонента Database Engine на компьютере.
SQLSERVER:\SQL\ComputerName\InstanceName	Список типов объектов верхнего уровня в экземпляре, таких как «Конечные точки», «Сертификаты» и «Базы данных».
Узел класса объектов, например «Базы данных»	Список объектов этого типа, например список баз данных: master, model, AdventureWorks20008R2.
Узел имени объекта, например AdventureWorks2008R2.	Список типов объектов, содержащихся в этом объекте. Например, для базы данных будет выведен список типов объектов, таких как таблицы и представления.

По умолчанию командлет Get-ChildItem не выводит системные объекты. Чтобы отобразить системные объекты, например объекты из схемы sys, используйте параметр Force.

В этом примере выводится имя локального компьютера и всех компьютеров, с которыми установлено соединение с помощью объектов SMO или WMI-соединение:

Set-Location SQLSERVER:\SQL
Get-ChildItem

В этом примере перечисляются экземпляры компонента Database Engine на локальном компьютере:

Set-Location SQLSERVER:\SQL\localhost
Get-ChildItem

В этом примере выводятся основные классы объектов, доступных в экземпляре компонента Database Engine по умолчанию. Список содержит имена типа «Конечные точки», «Сертификаты» и «Базы данных»:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT
Get-ChildItem

В этом примере выводятся базы данных, доступные в экземпляре компонента Database Engine по умолчанию. Параметр Force используется для включения системных баз данных, например master и model.

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-ChildItem -force

Выполнение действий в узлах пути

После перехода к узлу в пути Windows PowerShell можно выполнять действия двух типов.

Можно запускать командлеты Windows PowerShell, работающие с узлами, такие как Rename-Item.
Можно вызывать методы из соответствующей модели управляющих объектов SQL Server, например SMO. Например, если перейти в пути к узлу Databases, то можно использовать методы и свойства класса Database.

Поставщик SQL Server используется для управления объектами в экземпляре компонента Database Engine. Он не предназначен для работы с данными в базах данных. Если выбрана таблица или представление, нельзя использовать поставщик для выбора, вставки, обновления или удаления данных. Чтобы запросить или изменить данные в таблицах и представлениях из среды Windows PowerShell, используйте командлет Invoke-Sqlcmd. Дополнительные сведения см. в разделе Использование командлета Invoke-Sqlcmd.

Список методов и свойств

Командлет Get-Member используется для просмотра методов и свойств, доступных для определенных объектов или классов объектов.

В этом примере задается переменная Windows PowerShell для класса Database модели SMO и выводятся методы и свойства.

$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar | Get-Member –Type Methods
$MyDBVar | Get-Member -Type Properties

Командлет Get-Member также можно использовать, чтобы вывести методы и свойства, связанные с конечным узлом пути Windows PowerShell.

В следующем примере осуществляется переход к узлу Databases в SQLSERVER: path и выводится список свойств коллекции:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-Item . | Get-Member -Type Properties

В следующем примере осуществляется переход к узлу AdventureWorks2008R2 в SQLSERVER: path и выводится список свойств объекта:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2
Get-Item . | Get-Member -Type Properties

Использование методов и свойств

В командах Windows PowerShell можно упоминать свойства объектов SMO. В этом примере свойство Schema модели SMO используется для получения списка таблиц из схемы Sales в базе данных AdventureWorks2008R2.

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Tables
Get-ChildItem | where {$_.Schema -eq "Sales"}

В этом примере с помощью метода Script модели SMO создается сценарий, содержащий инструкции CREATE VIEW, необходимые для повторного создания представлений в базе данных AdventureWorks2008R2.

Remove-Item C:\PowerShell\CreateViews.sql
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Views
foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }

В этом примере используется метод Create модели SMO, чтобы создать базу данных, а затем используется свойство State, чтобы показать, существует ли эта база данных:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar.Parent = (Get-Item ..)
$MyDBVar.Name = "NewDB"
$MyDBVar.Create()
$MyDBVar.State

Определение нестандартных дисков

Windows PowerShell позволяет определять виртуальные диски, которые называются дисками PowerShell. Они сопоставляются начальным узлам в указании пути. Обычно они используются в качестве краткой записи для часто используемых путей. Пути SQLSERVER: могут оказаться длинными, занимать много места в окне Windows PowerShell и требовать много времени на ввод с клавиатуры. Если планируется выполнить большой объем работы для некоторого узла пути, для него можно определить нестандартный диск Windows PowerShell. Например, если выполняется много действий в базе данных AdventureWorks2008R2, можно создать диск AWDB следующим образом:

New-PSDrive -Name AWDB -Root SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2

Затем можно использовать этот диск AWDB, чтобы сократить путь к объектам AdventureWorks2008R2, таким как таблица Purchasing.Vendor.

Set-Location AWDB:\Tables\Purchasing.Vendor

Управление соединениями с проверкой подлинности SQL Server

По умолчанию поставщик SQL Server использует учетную запись Windows, с которой он работает, чтобы установить соединение с компонентом Database Engine с использованием проверки подлинности Windows. Чтобы установить соединение с проверкой подлинности SQL Server, необходимо связать учетные данные для входа на SQL Server с виртуальным диском, а затем использовать команду перехода в каталог (cd) для соединения с этим диском. В Windows PowerShell учетные данные безопасности можно связывать только с виртуальными дисками.

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

function sqldrive
{
    param( [string]$name, [string]$login = "MyLogin", [string]$root = "SQLSERVER:\SQL\MyComputer\MyInstance" )
    $pwd = read-host -AsSecureString -Prompt "Password"
    $cred = new-object System.Management.Automation.PSCredential -argumentlist $login,$pwd
    New-PSDrive $name -PSProvider SqlServer -Root $root -Credential $cred -Scope 1
}

Затем можно создать виртуальный диск с именем SQLAuth: путем запуска следующей команды:

sqldrive SQLAuth

Функция sqldrive запрашивает ввод пароля для имени входа, при этом символы пароля при вводе скрываются. Затем при использовании команды перехода в каталог (cd) для соединения с путем при помощи SQLAuth: drive все операции осуществляются при помощи учетных данных для входа проверки подлинности SQL Server, указанных при создании диска.

Использование пространства имен Microsoft.SqlServer.Managment.Smo.Wmi

Помимо пространств имен модели управляющих объектов SQL Server, связанных с папками \SQL, \SQLPolicy и \SQLRegistration, можно использовать классы в пространстве имен Microsoft.SqlServer.Management.Smo.Wmi. Чаще всего это пространство имен используется, чтобы запрашивать и управлять состоянием служб, в которых реализуется каждый экземпляр компонента Database Engine или хранилище политик.

В этом примере показано, как с помощью класса ManagedComputer остановить или запустить службу, выполняющую экземпляр компонента Database Engine по умолчанию.

# Get a reference to the ManagedComputer class.
cd SQLSERVER:\SQL\localhost
$Wmi = (get-item .).ManagedComputer
# Display the object properties.
$Wmi
# Get a reference to the default instance of the Database Engine.
$DfltInstance = $Wmi.Services["MSSQLSERVER"]
# Display the state of the service.
$DfltInstance
# Stop the service.
$DfltInstance.Stop(); write-host "Stopped"
# Refresh the cache and look at the state.
$DfltInstance.Refresh(); $DfltInstance
# Start the service again.
$DfltInstance.Start(); write-host "Started"

Примечание
Чтобы использовать классы в этом пространстве имен для удаленных компьютеров, в брандмауэре Windows необходимо разрешить соединения WMI DCOM. Дополнительные сведения см. в разделе Настройка Брандмауэра Windows для разрешения доступа к SQL Server.

Чтобы использовать классы в этом пространстве имен для удаленных компьютеров, в брандмауэре Windows необходимо разрешить соединения WMI DCOM. Дополнительные сведения см. в разделе Настройка Брандмауэра Windows для разрешения доступа к SQL Server.

Работа с завершением по клавише TAB

Функция завершения по клавише TAB в Windows PowerShell сокращает объем вводимых с клавиатуры данных. После того как набрана часть имени пути или командлета, можно нажать клавишу TAB, чтобы получить список элементов, имена которых совпадают с уже набранным текстом. Затем можно выбрать нужный элемент из списка, не набирая остальную часть его имени.

При работе с базой данных, содержащей большое количество объектов, списки завершения по клавише TAB могут стать очень большими. Кроме того, для некоторых типов объектов SQL Server, например для представлений, существует большое число системных объектов.

В оснастках SQL Server существуют три системные переменные, которые используются для управления объемом данных, выводимых функцией завершения по клавише TAB и командлетом Get-ChildItem.

**$SqlServerMaximumTabCompletion =**n
Указывает максимальное число объектов, включаемых в список завершения по клавише TAB. Если нажать клавишу TAB в узле пути, для которого существует более n подходящих объектов, список завершения будет усечен до n объектов. Параметр n является целым числом. 0 — значение по умолчанию, которое означает, что число перечисляемых объектов не ограничено.
**$SqlServerMaximumChildItems =**n
Указывает максимальное количество объектов, отображаемых командлетом Get-ChildItem. Если командлет Get-ChildItem выполняется в узле пути, для которого существует более n объектов, список будет усечен до n объектов. Параметр n является целым числом. 0 — значение по умолчанию, которое означает, что число перечисляемых объектов не ограничено.
$SqlServerIncludeSystemObjects = { $True | $False }
Если указано значение $True, функция завершения по клавише TAB и командлет Get-ChildItem отображают системные объекты. Если выбрано значение $False, системные объекты не отображаются. Значение по умолчанию равно $False.

В следующем примере задаются все три переменные и выводятся их значения:

$SqlServerMaximumTabCompletion = 20
$SqlServerMaximumChildItems = 10
$SqlServerIncludeSystemObjects = $False
dir variable:sqlserver*