• 아티클

SQL Server PowerShell 실행

SQL Server는 Windows PowerShell과 Windows PowerShell에서 SQL Server 기능을 제공하는 SQL Server 스냅인 집합을 설치합니다. 그러면 SQL Server 개체와 작동하는 Windows PowerShell 스크립트를 코딩할 수 있습니다. 작성한 스크립트는 Windows PowerShell 환경과 SQL Server Management Studio에서 실행하거나 SQL Server 에이전트 작업으로 실행할 수 있습니다.

SQL Server PowerShell 지원 설치

SQL Server 설치 프로그램을 사용하여 Windows PowerShell 스크립트를 실행하는 데 필요한 소프트웨어를 설치할 수 있습니다. SQL Server 2008부터는 클라이언트 소프트웨어 또는 Database Services 노드 중 하나를 선택하면 설치 프로그램이 다음 Windows PowerShell 구성 요소를 설치합니다.

  • Windows PowerShell 1.0(Windows PowerShell이 컴퓨터에 아직 없는 경우)

  • SQL Server 스냅인. 스냅인은 SQL Server에 대한 두 가지 유형의 Windows PowerShell 지원을 구현하는 dll 파일입니다.

    • SQL Server cmdlet 집합. Cmdlet은 특정 동작을 구현하는 명령입니다. 예를 들어 Invoke-Sqlcmd는 sqlcmd 유틸리티를 사용하여 실행할 수도 있는 Transact-SQL 또는 XQuery 스크립트를 실행하고 Invoke-PolicyEvaluation은 SQL Server 개체가 정책 기반 관리 정책을 준수하는지 여부를 보고합니다.

    • SQL Server 공급자. 공급자를 사용하여 파일 시스템 경로와 유사한 경로로 표시되는 SQL Server 개체의 계층 구조를 탐색할 수 있습니다. 각 개체는 SQL Server 관리 개체 모델의 클래스와 관련되어 있습니다. 클래스의 메서드 및 속성을 사용하여 개체에 대한 작업을 수행할 수 있습니다. 예를 들어 경로에서 databases 개체로 cd를 수행하는 경우 Microsoft.SqlServer.Managment.SMO.Database 클래스의 메서드와 속성을 사용하여 해당 데이터베이스를 관리할 수 있습니다.

  • SQL Server 스냅인이 포함된 Windows PowerShell 세션을 실행하는 데 사용되는 sqlps 유틸리티

SQL Server 2008부터 SQL Server Management Studio는 개체 탐색기 트리에서 Windows PowerShell 세션을 시작하도록 지원합니다. 또한 SQL Server 2008부터는 SQL Server 에이전트가 Windows PowerShell 작업 단계를 지원합니다.

설치가 완료된 후 Windows PowerShell이 제거되면 Windows PowerShell의 SQL Server 기능이 작동하지 않습니다. Windows 사용자는 Windows PowerShell을 제거할 수 있으며 일부 Windows 운영 체제 업그레이드 시 Windows PowerShell을 제거해야 할 수 있습니다. Windows PowerShell이 제거된 후 SQL Server 기능을 사용하려면 다음 중 하나를 수행해야 합니다.

  • Microsoft 다운로드 센터에서 Windows PowerShell 1.0을 수동으로 다운로드하여 다시 설치합니다. 다운로드 지침은 Windows Server 2003웹 사이트에서 확인할 수 있습니다.

  • Windows Server 2008을 실행 중이면 운영 체제에 Windows PowerShell 1.0이 있지만 기본적으로 비활성화되어 있습니다. Windows Server 2008에서 Windows PowerShell을 다시 활성화할 수 있습니다.

지원되는 SQL Server 버전

SQL Server 2008 클라이언트 구성 요소를 사용해야 Windows PowerShell을 실행할 수 있습니다. Windows PowerShell은 SQL Server 2000 이상의 인스턴스에 연결할 수 있습니다. 사용할 수 있는 가장 오래된 SQL Server 2005 버전은 SP2입니다. 사용할 수 있는 가장 오래된 SQL Server 2000 버전은 SP4입니다. Windows PowerShell을 SQL Server 2005 및 SQL Server 2000에 연결하는 경우 이러한 버전의 SQL Server에서 지원되는 기능만 사용할 수 있습니다.

sqlps 유틸리티 사용

sqlps는 Windows PowerShell 환경을 만든 다음 SQL Server 스냅인을 로드하고 등록하는 유틸리티입니다. sqlps를 사용하여 다음 작업을 수행할 수 있습니다.

  • 대화형으로 Windows PowerShell 명령을 실행합니다.

  • Windows PowerShell 스크립트 파일을 실행합니다.

  • SQL Server cmdlet을 실행합니다.

  • SQL Server 공급자 경로를 사용하여 SQL Server 개체의 계층 구조를 탐색합니다.

기본적으로 sqlps는 모든 Windows PowerShell 스크립트 실행을 방지하는 Restricted로 설정된 스크립팅 실행 정책과 함께 실행됩니다. Set-ExecutionPolicy cmdlet을 사용하면 서명된 스크립트나 기타 스크립트를 실행하도록 설정할 수 있습니다. 신뢰할 수 있는 출처에서 제공하는 스크립트만 실행하고 적절한 NTFS 권한을 사용하여 모든 입력 및 출력 파일을 보호하십시오. Windows PowerShell 스크립트를 활성화하는 방법은 Windows PowerShell 스크립트 실행(Running Windows PowerShell Scripts)을 참조하십시오.

자세한 내용은 sqlps 유틸리티를 참조하십시오.

SQL Server Management Studio에서 Windows PowerShell 사용

개체 탐색기의 개체를 마우스 오른쪽 단추로 클릭하고 PowerShell 시작을 선택하여 SQL Server Management Studio의 Windows PowerShell 세션을 시작할 수 있습니다. 그러면 SQL Server Management Studio에서 SQL Server PowerShell 스냅인이 로드 및 등록된 Windows PowerShell 세션을 시작합니다. 세션 경로는 사용자가 개체 탐색기에서 마우스 오른쪽 단추로 클릭한 개체의 위치로 미리 설정됩니다. 예를 들어 개체 탐색기에서 AdventureWorks2008R2 데이터베이스 개체를 마우스 오른쪽 단추로 클릭하고 PowerShell 시작을 선택하면 Windows PowerShell 경로가 다음과 같이 설정됩니다.

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

SQL Server 에이전트 작업 단계에서 Windows PowerShell 사용

SQL Server 에이전트 작업 단계 유형은 여러 가지가 있습니다. 각 유형은 복제 에이전트나 명령 프롬프트 환경과 같은 특정 환경을 구현하는 하위 시스템과 관련되어 있습니다. Windows PowerShell용 SQL Server 에이전트 하위 시스템은 Windows PowerShell 스크립트를 실행하는 작업 단계를 지원합니다. Windows PowerShell 스크립트를 코딩한 다음 SQL Server 에이전트를 사용하여 예약된 시간이나 SQL Server 이벤트에 대한 응답으로 실행되는 스크립트를 작업에 포함할 수 있습니다. SQL Server 에이전트 하위 시스템은 Windows PowerShell 스크립트를 실행할 수 있도록 SQL Server 스냅인을 로드하고 등록합니다.

자세한 내용은 SQL Server 에이전트 하위 시스템을 참조하십시오.

주의 사항주의

각 SQL Server 에이전트 작업 단계는 약 20MB의 메모리를 사용하는 sqlps 프로세스를 시작합니다. 많은 Windows PowerShell 작업 단계를 동시에 실행하면 성능이 저하될 수 있습니다.

Windows PowerShell에 SQL Server 스냅인 추가

sqlps 유틸리티는 Windows PowerShell 미니 셸입니다. 미니 셸에는 특정 제한이 있습니다. 예를 들어 미니 셸은 하나 이상의 Windows PowerShell 스냅인에서 로드되도록 코딩되지만 사용자와 스크립트는 다른 스냅인을 추가할 수 없습니다. SQL Server 스냅인과 다른 제품의 스냅인을 함께 사용하여 작업하는 경우와 같이 미니 셸에서 지원하지 않는 기능이 필요한 경우 SQL Server 스냅인을 Windows PowerShell 환경에 직접 추가할 수 있습니다.

다음 코드를 메모장에 붙여 넣고 컴퓨터에 ps1 스크립트 파일로 저장합니다(예: 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

그런 다음 이 스크립트를 사용하여 다음 명령으로 SQL Server 스냅인이 로드되는 Windows PowerShell 환경을 시작할 수 있습니다.

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

명령 프롬프트, 바탕 화면 바로 가기 또는 시작 메뉴의 실행 대화 상자에서 명령을 실행할 수 있습니다. 기본적으로 Windows PowerShell은 스크립트 실행을 지원하지 않는 Restricted 모드에서 실행됩니다. Windows PowerShell 스크립트를 활성화하는 방법은 Windows PowerShell 스크립트 실행(Running Windows PowerShell Scripts)을 참조하십시오.

Windows PowerShell에서 SQL Server Management Objects 로드

SQL Server 공급자는 SMO(SQL Server Management Object) 어셈블리를 자동으로 로드합니다. SMO 어셈블리를 직접 로드해야 할 수 있는 두 가지 경우가 있습니다.

  • 스크립트에서 SQL Server 스냅인의 cmdlet 또는 공급자를 참조하는 첫 번째 명령 전에 SMO 개체를 참조하는 경우

  • 공급자 또는 cmdlet을 사용하지 않는 C# 또는 VB.Net과 같은 다른 언어에서 SMO 코드를 이식하려는 경우

다음 코드는 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