Compartilhar via

  • Artigo

Executando o SQL Server PowerShell

O SQL Server instala o Windows PowerShell e um conjunto de snap-ins do SQL Server que expõe a funcionalidade do SQL Server no Windows PowerShell. Você pode codificar os scripts do Windows PowerShell que funcionam com objetos do SQL Server. Os scripts podem ser executados no ambiente Windows PowerShell, no SQL Server Management Studio e como trabalhos do SQL Server Agent.

Instalando o suporte do SQL Server PowerShell

Você instala o software necessário para executar os scripts do Windows PowerShell usando o programa de instalação do SQL Server. A partir do SQL Server 2008, quando você seleciona o software cliente ou os nós de Serviços do Banco de Dados, o programa de instalação instala os seguintes componentes do Windows PowerShell:

  • Windows PowerShell 1.0, caso o Windows PowerShell ainda não esteja instalado em seu computador.

  • Snap-ins do SQL Server. Os snap-ins são arquivos dll que implementam dois tipos de suporte do Windows PowerShell ao SQL Server:

    • Conjunto de cmdlets do SQL Server. Os cmdlets são comandos que implementam uma ação específica. Por exemplo, Invoke-Sqlcmd executa um script Transact-SQL ou XQuery que também pode ser executado usando o utilitário sqlcmd, e Invoke-PolicyEvaluation relata se os objetos SQL Server são compatíveis com as diretivas de gerenciamento baseado em diretivas.

    • Um provedor do SQL Server. O provedor permite navegar pela hierarquia dos objetos do SQL Server usando um caminho semelhante a um caminho de sistema de arquivos. Cada objeto é associado a uma classe de modelos de objeto de gerenciamento do SQL Server. Você pode usar os métodos e as propriedades da classe para realizar trabalhos nos objetos. Por exemplo, se você usar cd para um objeto do banco de dados em um caminho, poderá usar os métodos e as propriedades da classe Microsoft.SqlServer.Managment.SMO.Database para gerenciar o banco de dados.

  • Utilitário sqlps, que é usado para executar sessões do Windows PowerShell que incluem os snap-ins do SQL Server.

A partir do SQL Server 2008, o SQL Server Management Studio permite que as sessões do Windows PowerShell sejam iniciadas a partir da árvore do Pesquisador de Objetos. A partir do SQL Server 2008, o SQL Server Agent oferece suporte às etapas de trabalho do Windows PowerShell.

Se o Windows PowerShell for desinstalado após a conclusão da Instalação, os recursos do SQL Server para Windows PowerShell não funcionarão. O Windows PowerShell pode ser desinstalado pelos usuários do Windows, e a desinstalação pode ser exigida por algumas atualizações do sistema operacional Windows. Se o Windows PowerShell tiver sido desinstalado e você desejar usar os recursos do SQL Server PowerShell, será preciso realizar um destes procedimentos:

  • Baixar e reinstalar manualmente o Windows PowerShell 1.0 a partir do Centro de Download da Microsoft. Baixar as instruções que constam no Windows Server 2003site.

  • Se você estiver executando o Windows Server 2008, o Windows PowerShell 1.0 permanecerá presente no sistema operacional, mas, por padrão, estará desabilitado. O Windows PowerShell pode ser reabilitado a partir do Windows Server 2008.

Versões com suporte do SQL Server

Você deve usar os componentes de cliente do SQL Server 2008 para executar o Windows PowerShell. O Windows PowerShell pode se conectar a instâncias de SQL Server 2000 ou posterior. A versão mais antiga do SQL Server 2005 que você pode usar é SP2. A versão mais antiga do SQL Server 2000 que você pode usar é SP4. Quando o Windows PowerShell se conecta ao SQL Server 2005 e do SQL Server 2000, ele fica limitado à funcionalidade disponível nessas versões do SQL Server.

Usando o utilitário sqlps

O sqlps é um utilitário que cria um ambiente Windows PowerShell e, em seguida, carrega e registra os snap-ins do SQL Server PowerShell. O sqlps pode ser usado para:

  • Executar comandos do Windows PowerShell de forma interativa.

  • Executar arquivos de script do Windows PowerShell.

  • Executar cmdlets do SQL Server.

  • Usar os caminhos de provedor do SQL Server para navegar pela hierarquia de objetos do SQL Server.

Por padrão, o sqlps é executado em conjunto com a política de execução de scripts definida como Restrita, evitando a execução de qualquer script do Windows PowerShell. Você pode usar o cmdlet Set-ExecutionPolicy para ativar a execução de scripts assinados ou de quaisquer outros scripts. Somente os scripts de origem confiável devem ser executados e é preciso verificar se todos os arquivos de entrada e de saída estão usando as permissões NTFS adequadas. Para obter mais informações sobre como habilitar scripts do Windows PowerShell, consulte Executando scripts do Windows PowerShell.

Para obter mais informações, consulte Utilitário sqlps.

Usando o Windows PowerShell no SQL Server Management Studio

Você pode iniciar sessões do Windows PowerShell no SQL Server Management Studio clicando com o botão direito do mouse nos objetos no Pesquisador de Objetos e selecionando Iniciar PowerShell. O SQL Server Management Studio inicia uma sessão do Windows PowerShell na qual os snap-ins do SQL Server PowerShell são carregados e registrados. O caminho da sessão é predefinido no local do objeto em que você clicou com o botão direito no Pesquisador de Objetos. Por exemplo, se você clicar com o botão direito no objeto do banco de dados AdventureWorks2008R2 no Pesquisador de Objetos e selecionar Iniciar PowerShell, o caminho do Windows PowerShell será definido da seguinte forma:

SQLSERVER:\SQL\MyComputer\MyInstance\Databases\AdventureWorks2008R2>

Usando o Windows PowerShell nas etapas de trabalho do SQL Server Agent

Existem vários tipos de etapas de trabalho do SQL Server Agent. Cada tipo está associado a um subsistema que implementa um ambiente específico, como um agente de replicação ou ambiente de prompt de comando. O subsistema do SQL Server Agent para Windows PowerShell oferece suporte às etapas de trabalho que executam scripts do Windows PowerShell. Você pode codificar os scripts do Windows PowerShell e usar o SQL Server Agent para incluir os scripts em trabalhos que são executados em horários programados ou em resposta a eventos do SQL Server. O subsistema do SQL Server Agent carrega e registra os snap-ins do SQL Server de forma que seja possível executar os scripts do Windows PowerShell.

Para obter mais informações, consulte Subsistemas do SQL Server Agent.

Observação sobre cuidadosCuidado

Cada etapa de trabalho do SQL Server Agent inicia um processo de sqlps que consome, aproximadamente, 20 MB de memória. A execução de um grande número de etapas de trabalho concorrentes do Windows PowerShell pode afetar o desempenho de forma negativa.

Adicionando os snap-ins do SQL Server ao Windows PowerShell

O utilitário sqlps é um minishell do Windows PowerShell. Mini-shells têm certas restrições. Por exemplo, eles são codificados para carregar em um ou mais snap-ins do Windows PowerShell, mas os usuários e os scripts não podem adicionar outros snap-ins. Se você precisar de funcionalidade não tem suporte por um mini-shell, como trabalhar com os snap-ins do SQL Server e os snap-ins de outro produto, poderá adicionar os snap-ins do SQL Server diretamente em um ambiente do Windows PowerShell.

Cole o código a seguir em um Bloco de Notas e salve como um arquivo de script ps1 no computador, como C:\MyFolder\InitializeSQLProvider.ps1:

# Add the SQL Server Provider.

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}


# Set mandatory variables for the SQL Server provider
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

# Load the snapins, type data, format data
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml 
update-FormatData -prependpath SQLProvider.Format.ps1xml 
Pop-Location

Você pode usar o arquivo de script para iniciar o ambiente Windows PowerShell no qual os snap-ins do SQL Server PowerShell são carregados usando este comando:

PowerShell -NoExit -Command "C:\MyFolder\InitializeSQLProvider.ps1"

O comando pode ser executado em um prompt de comando, a partir de um atalho da área de trabalho ou na caixa de diálogo Executar, no menu Iniciar. Por padrão, o Windows PowerShell é executado no modo Restrito que não suporta a execução de scripts. Para obter mais informações sobre como habilitar scripts do Windows PowerShell, consulte Executando scripts do Windows PowerShell.

Carregando os SQL Server Management Objects no Windows PowerShell

O provedor do SQL Server carrega automaticamente os assemblies do SQL Server Management Objects (SMO). Há dois casos em que você pode precisar carregar diretamente os assemblies do SMO:

  • Se o script referenciar um objeto do SMO antes do primeiro comando que referencia o provedor ou cmdlets dos snap-ins do SQL Server.

  • Você deseja portar código do SMO de outra linguagem, como C# ou VB.Net que não usa o provedor ou cmdlets.

O código a seguir carrega os assemblies do SMO:

# Loads the SQL Server Management Objects (SMO)

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}


$assemblylist = 
"Microsoft.SqlServer.Management.Common",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.Instapi ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.SqlTDiagM ",
"Microsoft.SqlServer.SString ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum",
"Microsoft.SqlServer.Management.Dac",
"Microsoft.SqlServer.Management.DacEnum",
"Microsoft.SqlServer.Management.Utility"


foreach ($asm in $assemblylist)
{
    $asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}

Push-Location
cd $sqlpsPath
update-FormatData -prependpath SQLProvider.Format.ps1xml 
Pop-Location