SQL Server PowerShell 공급자 사용

SQL Server PowerShell 계층 구조

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

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

예를 들어 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 개체 모델 다이어그램을 참조하십시오.

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

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

SQL Server 경로 탐색

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

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

예를 들어 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은 시스템 개체를 나열하지 않습니다. 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 사용을 참조하십시오.

메서드 및 속성 나열

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

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

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

메서드 및 속성 사용

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

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 }

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 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*

참고 항목

개념

관련 자료