다음을 통해 공유

  • 아티클

SQL Server PowerShell 공급자 사용

Windows PowerShell용 SQL Server 공급자는 SQL Server 개체의 계층 구조를 파일 시스템 경로와 비슷한 경로에 표시합니다. 이 경로를 사용하여 개체를 찾은 다음 SMO(SQL Server Management Object) 모델의 메서드를 사용하여 개체에 대해 동작을 수행할 수 있습니다.

SQL Server PowerShell 계층 구조

데이터나 개체 모델을 계층 구조로 표현할 수 있는 제품은 Windows PowerShell 공급자를 사용하여 계층 구조를 표시합니다. Windows 파일 시스템에 사용되는 것과 유사한 드라이브 및 경로 구조를 사용하여 계층 구조를 표시합니다.

각 Windows PowerShell 공급자는 하나 이상의 드라이브를 구현합니다. 각 드라이브는 관련 개체 계층 구조의 루트 노드를 나타냅니다. SQL Server 공급자는 SQLSERVER: 드라이브를 구현합니다. SQLSERVER: 드라이브에는 4개의 주 폴더가 있습니다. 각 폴더 및 해당 하위 폴더는 SQL Server 관리 개체 모델을 사용하여 액세스할 수 있는 개체 집합을 나타냅니다. 이러한 주 폴더 중 하나로 시작하는 경로의 하위 폴더에 포커스를 설정하면 관련 개체 모델의 메서드를 사용하여 이러한 노드가 나타내는 개체에 대해 동작을 수행할 수 있습니다. SQL Server 2008 R2 공급자가 구현하는 Windows PowerShell 폴더는 다음 테이블에 나열되어 있습니다.

폴더

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

데이터베이스 엔진의 관리되는 인스턴스와 같은 유틸리티 개체입니다.

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)이 번갈아 사용됩니다. 스키마는 개체 클래스로 표현되지 않습니다. 스키마의 테이블 또는 뷰와 같은 최상위 수준 개체에 대한 노드를 지정할 때는 개체 이름을 SchemaName.ObjectName 형식으로 지정해야 합니다.

다음은 로컬 컴퓨터의 기본 데이터베이스 엔진 인스턴스에서 AdventureWorks2008R2 데이터베이스의 Purchasing 스키마에 있는 Vendor 테이블의 경로입니다.

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

SMO 개체 모델 계층 구조에 대한 자세한 내용은 SMO 개체 모델 다이어그램을 참조하십시오.

경로의 개체 클래스 노드는 관련된 개체 모델의 컬렉션 클래스와 관련이 있습니다. 다음 테이블에 나와 있듯이 개체 이름 노드는 관련된 개체 모델의 개체 클래스와 관련이 있습니다.

경로

SMO 클래스

SQLSERVER:\SQL\MyComputer\DEFAULT\Databases

DatabaseCollection

SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks2008R2

Database

사용자가 경로에서 데이터베이스 엔진 인스턴스를 참조할 때마다 SQL Server 공급자는 SMO를 사용하여 인스턴스에 대한 Windows 인증 연결을 엽니다. 이 연결은 Windows PowerShell 세션을 실행 중인 Windows 계정의 자격 증명을 사용하여 설정됩니다. SQL Server 공급자는 SQL Server 인증을 사용하지 않습니다.

SQL Server 경로 탐색

Windows PowerShell은 공급자 계층 구조를 탐색하고 현재 개체에 대한 기본 작업을 수행할 수 있는 cmdlet을 구현합니다. cmdlet은 자주 사용되므로 간단한 정규 별칭을 가지고 있습니다. 또한 cmdlet을 유사한 명령 프롬프트 명령에 매핑하는 별칭 집합과 UNIX 셸 명령에 대한 별칭 집합도 있습니다.

SQL Server 공급자는 다음 테이블과 같이 공급자 cmdlet의 하위 집합을 구현합니다.

cmdlet

정규 별칭

cmd 별칭

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

개체를 제거합니다.

예를 들어 SQLSERVER:\SQL 폴더로 이동하고 해당 폴더의 자식 항목 목록을 요청하여 사용 가능한 SQL Server 인스턴스 목록을 검색하려면 다음과 같은 cmdlet 또는 별칭의 집합 중 하나를 사용하면 됩니다.

  • 전체 cmdlet 이름을 사용하는 경우:

    Set-Location SQLSERVER:\SQL
Get-ChildItem

  • 정규 별칭을 사용하는 경우:

    sl SQLSERVER:\SQL
gci

  • cmd 별칭을 사용하는 경우:

    cd SQLSERVER:\SQL
dir

  • UNIX 셸 별칭을 사용하는 경우:

    cd SQLSERVER:\SQL
ls
    중요 정보중요

    일부 SQL Server 식별자(개체 이름)의 경우 Windows PowerShell에서 지원하지 않는 문자가 경로 이름에 포함되어 있습니다. 이러한 문자가 포함된 이름을 사용하는 방법은 PowerShell에서 SQL Server 식별자 사용을 참조하십시오.

Get-ChildItem 사용

Get-ChildItem(또는 해당 dir 및 ls 별칭)에서 반환하는 정보는 SQLSERVER: 경로에서의 현재 위치에 따라 달라집니다.

경로 위치

Get-ChildItem 결과

SQLSERVER:\SQL

로컬 컴퓨터의 이름을 반환합니다. SMO 또는 WMI를 사용하여 다른 컴퓨터에 있는 데이터베이스 엔진 인스턴스에 연결한 경우에는 해당 컴퓨터도 나열됩니다.

SQLSERVER:\SQL\ComputerName

컴퓨터에 있는 데이터베이스 엔진 인스턴스의 목록입니다.

SQLSERVER:\SQL\ComputerName\InstanceName

Endpoints, Certificates 및 Databases와 같은 인스턴스의 최상위 개체 유형 목록입니다.

Databases와 같은 개체 클래스 노드

다음 데이터베이스 목록과 같은 해당 유형의 개체 목록. 예: master, model, AdventureWorks20008R2.

AdventureWorks2008R2 와 같은 개체 이름 노드

개체 내에 포함된 개체 유형 목록입니다. 예를 들어 데이터베이스는 테이블 및 뷰와 같은 개체 유형을 나열합니다.

기본적으로 Get-ChildItem은 시스템 개체를 나열하지 않습니다. Force 매개 변수를 사용하여 sys 스키마의 개체와 같은 시스템 개체를 볼 수 있습니다.

이 예에서는 SMO 또는 WMI 연결이 만들어진 모든 컴퓨터와 로컬 컴퓨터를 나열합니다.

Set-Location SQLSERVER:\SQL
Get-ChildItem

이 예에서는 로컬 컴퓨터에 있는 데이터베이스 엔진 인스턴스를 나열합니다.

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

이 예에서는 기본 데이터베이스 엔진 인스턴스에서 사용할 수 있는 개체의 주 클래스를 나열합니다. 여기에는 Endpoints, Certificates 및 Databases와 같은 이름도 포함됩니다.

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

이 예에서는 기본 데이터베이스 엔진 인스턴스에서 사용할 수 있는 데이터베이스를 나열합니다. Force 매개 변수를 사용하여 master 및 model과 같은 시스템 데이터베이스를 포함합니다.

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

경로 노드에 대한 동작 수행

Windows PowerShell 경로의 노드로 이동한 후에는 두 가지 유형의 동작을 수행할 수 있습니다.

  • Rename-Item과 같이 노드에서 작동하는 Windows PowerShell cmdlet을 실행할 수 있습니다.

  • SMO와 같은 관련 SQL Server 관리 개체 모델에서 메서드를 호출할 수 있습니다. 예를 들어 경로에서 Databases 노드로 이동하는 경우 Database 클래스의 메서드와 속성을 사용할 수 있습니다.

SQL Server 공급자는 데이터베이스 엔진 인스턴스의 개체를 관리하는 데 사용됩니다. 데이터베이스의 데이터 작업에는 사용되지 않습니다. 테이블 또는 뷰로 이동한 경우에는 공급자를 사용하여 데이터에 대한 선택, 삽입, 업데이트 또는 삭제 작업을 수행할 수 없습니다. Windows PowerShell 환경에서 테이블 및 뷰의 데이터를 쿼리하거나 변경하려면 Invoke-Sqlcmd cmdlet을 사용하십시오. 자세한 내용은 Invoke-Sqlcmd cmdlet 사용을 참조하십시오.

메서드 및 속성 나열

Get-Member cmdlet을 사용하여 특정 개체 또는 개체 클래스에 사용할 수 있는 메서드 및 속성을 볼 수 있습니다.

이 예에서는 Windows PowerShell 변수를 SMO Database 클래스로 설정하고 메서드 및 속성을 나열합니다.

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

또한 Get-Member를 사용하여 Windows PowerShell 경로의 끝 노드와 관련된 메서드 및 속성을 나열할 수 있습니다.

이 예에서는 SQLSERVER: 경로의 데이터베이스 노드로 이동하여 컬렉션 속성을 표시합니다.

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

이 예에서는 SQLSERVER: 경로의 AdventureWorks2008R2 노드로 이동하여 개체 속성을 표시합니다.

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

메서드 및 속성 사용

Windows PowerShell 명령에서 SMO 속성을 참조할 수 있습니다. 이 예에서는 SMO Schema 속성을 사용하여 AdventureWorks2008R2의 Sales 스키마에서 테이블 목록을 가져옵니다.

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

이 예에서는 SMO Script 메서드를 사용하여 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 }

이 예에서는 SMO Create 메서드를 사용하여 데이터베이스를 만든 다음 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: 드라이브를 사용하여 Purchasing.Vendor 테이블과 같은 AdventureWorks2008R2 개체에 대한 경로의 길이를 단축할 수 있습니다.

Set-Location AWDB:\Tables\Purchasing.Vendor

SQL Server 인증 연결 관리

기본적으로 SQL Server 공급자는 데이터베이스 엔진에 대한 Windows 인증 연결을 만들기 위해 실행하는 Windows 계정을 사용합니다. SQL Server 인증 연결을 만들려면 SQL Server 로그인 자격 증명을 가상 드라이브에 연결한 다음 디렉터리 변경 명령(cd)을 사용하여 해당 드라이브에 연결해야 합니다. Windows PowerShell에서는 보안 자격 증명만 가상 드라이브에 연결할 수 있습니다.

이 스크립트는 지정된 SQL Server 인증 로그인 및 인스턴스에 연결된 가상 드라이브를 만드는 데 사용할 수 있는 sqldrive라는 함수를 만듭니다.

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 함수는 로그인에 대한 암호를 입력하라는 메시지를 표시하고 사용자가 입력하는 암호를 마스킹합니다. 이렇게 하면 SQLAuth: 드라이브를 사용하여 경로에 연결하기 위해 디렉터리 변경 명령(cd)을 사용할 때마다 드라이브를 만들었을 때 제공한 SQL Server 인증 로그인 자격 증명을 사용하여 모든 작업이 수행됩니다.

Microsoft.SqlServer.Managment.Smo.Wmi 네임스페이스 사용

\SQL, \SQLPolicy 및 \SQLRegistration 폴더에 연결된 SQL Server 관리 개체 모델 네임스페이스 이외에 Microsoft.SqlServer.Management.Smo.Wmi 네임스페이스에서도 클래스를 사용할 수 있습니다. 이 네임스페이스를 가장 일반적으로 사용하는 경우는 데이터베이스 엔진의 각 인스턴스 또는 정책 저장소를 구현하는 서비스 상태를 쿼리하고 관리하는 경우입니다.

이 예에서는 ManagedComputer 클래스를 사용하여 데이터베이스 엔진의 기본 인스턴스를 실행하는 서비스를 중지하고 시작하는 방법을 보여 줍니다.

# 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 연결을 허용하도록 구성해야 합니다. 자세한 내용은 SQL Server 액세스를 허용하도록 Windows 방화벽 구성을 참조하십시오.

탭 완성 기능 관리

Windows PowerShell 탭 완성 기능은 입력해야 할 텍스트의 양을 줄여 줍니다. 특정 경로나 cmdlet 이름의 일부를 입력한 후 Tab 키를 누르면 입력한 이름과 일치하는 항목의 목록을 가져올 수 있습니다. 그런 다음 나머지 이름을 입력하지 않고 목록에서 원하는 항목을 선택할 수 있습니다.

개체가 많은 데이터베이스에서 작업 중인 경우에는 탭 완성 목록이 매우 커질 수 있습니다. 뷰와 같은 일부 SQL Server 개체 유형에도 다수의 시스템 개체가 있습니다.

SQL Server 스냅인에 도입된 3개의 시스템 변수를 사용하여 탭 완성 기능 및 Get-ChildItem에서 제공하는 정보의 양을 제어할 수 있습니다.

  • **$SqlServerMaximumTabCompletion =**n
    탭 완성 목록에 포함할 최대 개체 수를 지정합니다. 개체 수가 n보다 많은 경로 노드에서 Tab 키를 누르면 탭 완성 목록이 n개까지 표시됩니다. n은 정수입니다. 0은 기본 설정이며 나열되는 개체 수에 제한이 없음을 의미합니다.

  • **$SqlServerMaximumChildItems =**n
    Get-ChildItem에서 표시하는 최대 개체 수를 지정합니다. Get-ChildItem이 개체 수가 n개보다 많은 경로 노드에서 실행되는 경우 목록은 n개까지 표시됩니다. n은 정수입니다. 0은 기본 설정이며 나열되는 개체 수에 제한이 없음을 의미합니다.

  • $SqlServerIncludeSystemObjects = { $True | $False }
    $True인 경우 탭 완성 기능 및 Get-ChildItem에서 시스템 개체를 표시하고, $False인 경우에는 시스템 개체를 표시하지 않습니다. 기본 설정은 $False입니다.

다음 예에서는 3개 변수를 모두 설정하고 해당 설정을 나열합니다.

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

참고 항목

개념

SQL Server PowerShell 실행

관련 자료

SQL Server PowerShell 개요