PowerShell을 사용하여 탄력적 작업 생성 및 관리

적용 대상:Azure SQL Database

이 문서에서는 PowerShell을 사용하여 탄력적 작업을 시작하기 위한 자습서와 예제를 제공합니다. 탄력적 작업을 사용하면 여러 데이터베이스에서 동시에 하나 이상의 T-SQL(Transact-SQL) 스크립트를 실행할 수 있습니다.

이 포괄적인 자습서에서는 여러 데이터베이스에서 쿼리를 실행하는 데 필요한 단계를 알아봅니다:

• 탄력적 작업 에이전트 생성하기
• 작업이 해당 대상에서 스크립트를 실행할 수 있도록 작업 자격 증명 만들기
• 작업을 실행하려는 대상(서버, 탄력적 풀, 데이터베이스) 정의
• 에이전트가 작업을 연결하고 실행하도록 대상 데이터베이스에서 데이터베이스 범위 자격 증명 만들기
• 작업 만들기
• 작업에 작업 단계 추가
• 작업 실행 시작
• 작업 모니터링

필수 조건

탄력적 데이터베이스 작업에는 PowerShell cmdlet 집합이 있습니다.

이 cmdlet은 2023년 11월에 업데이트되었습니다.

최신 탄력적 작업 cmdlet 설치

Azure 구독이 없는 경우 시작하기 전에 체험 계정을 만듭니다.

아직 설치하지 않은 경우 최신 버전의 Az.Sql 및 SqlServer 모듈을 설치합니다. 관리자 권한으로 PowerShell에서 다음 명령을 실행합니다.

# installs the latest PackageManagement and PowerShellGet packages
Find-Package PackageManagement | Install-Package -Force
Find-Package PowerShellGet | Install-Package -Force

# Restart your powershell session with administrative access

# Install and import the Az.Sql module, then confirm
Install-Module -Name Az.Sql
Import-Module Az.Sql
Install-Module -Name SqlServer
Import-Module SqlServer

자세한 내용은 SQL Server PowerShell 모듈 설치를 참조하세요.

필수 리소스 만들기

탄력적 작업 에이전트를 만들려면 탄력적 작업 데이터베이스로 사용할 데이터베이스(S1 이상)가 필요합니다.

아래 스크립트에서는 탄력적 작업 데이터베이스로 사용할 새 리소스 그룹, 서버 및 데이터베이스를 만듭니다. 두 번째 스크립트에서는 작업을 실행할 수 있도록 빈 데이터베이스 2개가 포함된 두 번째 서버를 만듭니다.

Azure 요구 사항을 준수하면 원하는 명명 규칙을 사용할 수 있도록 탄력적 작업에는 특정한 명명 요구 사항이 없습니다. 서버에 탄력적 작업 데이터베이스로 빈 데이터베이스를 이미 만든 경우 탄력적 작업 에이전트 만들기로 건너뜁니다.

탄력적 작업 프라이빗 엔드포인트를 사용하는 경우 New-AzSqlServerFirewallRule로 방화벽 규칙을 구성할 필요가 없습니다.

# Sign in to your Azure account
Connect-AzAccount

# The SubscriptionId in which to create these objects
$SubscriptionId = '<your subscription id>'
# Set subscription context, important if you have access to more than one subscription.
Set-AzContext -SubscriptionId $subscriptionId 

# Create a resource group
Write-Output "Creating a resource group..."
$resourceGroupName = Read-Host "Please enter a resource group name"
$location = Read-Host "Please enter an Azure Region, for example westus2"
$rg = New-AzResourceGroup -Name $resourceGroupName -Location $location
$rg

# Create an Azure SQL logical server
Write-Output "Creating a server..."
$agentServerName = Read-Host "Please enter an agent server name"
$agentServerName = $agentServerName + "-" + [guid]::NewGuid()
$adminLogin = Read-Host "Please enter the server admin name"
$adminPassword = Read-Host "Please enter the server admin password"
$adminPasswordSecure = ConvertTo-SecureString -String $AdminPassword -AsPlainText -Force
$adminCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $adminLogin, $adminPasswordSecure
$parameters = @{
    ResourceGroupName = $resourceGroupName 
    Location = $location
    ServerName = $agentServerName 
    SqlAdministratorCredentials = ($adminCred)    
}
$agentServer = New-AzSqlServer @parameters

# Set server firewall rules to allow all Azure IPs
# Unnecessary if using an elastic jobs private endpoint
Write-Output "Creating a server firewall rule..."
$agentServer | New-AzSqlServerFirewallRule -AllowAllAzureIPs -FirewallRuleName "Allowed IPs"
$agentServer

# Create the job database
Write-Output "Creating a blank database to be used as the Job Database..."
$jobDatabaseName = "JobDatabase"
$parameters = @{
    ResourceGroupName = $resourceGroupName 
    ServerName = $agentServerName 
    DatabaseName = $jobDatabaseName 
    RequestedServiceObjectiveName = "S1"
}
$jobDatabase = New-AzSqlDatabase @parameters
$jobDatabase

# Create a target server and sample databases - uses the same credentials
Write-Output "Creating target server..."
$targetServerName = Read-Host "Please enter a target server name"
$targetServerName = $targetServerName + "-" + [guid]::NewGuid()
$parameters = @{
    ResourceGroupName= $resourceGroupName
    Location= $location 
    ServerName= $targetServerName
    ServerVersion= "12.0"
    SqlAdministratorCredentials= ($adminCred)
}
$targetServer = New-AzSqlServer @parameters

# Set target server firewall rules to allow all Azure IPs
# Unnecessary if using an elastic jobs private endpoint
$targetServer | New-AzSqlServerFirewallRule -AllowAllAzureIPs 

# Set the target firewall to include your desired IP range. 
# Change the following -StartIpAddress and -EndIpAddress values.
$parameters = @{
    StartIpAddress = "0.0.0.0" 
    EndIpAddress = "0.0.0.0"
    FirewallRuleName = "AllowAll"
}
$targetServer | New-AzSqlServerFirewallRule @parameters
$targetServer

# Create two sample databases to execute jobs against
$parameters = @{
    ResourceGroupName = $resourceGroupName 
    ServerName = $targetServerName 
    DatabaseName = "database1"
}
$db1 = New-AzSqlDatabase @parameters
$db1
$parameters = @{
    ResourceGroupName = $resourceGroupName 
    ServerName = $targetServerName 
    DatabaseName = "database2"
}
$db2 = New-AzSqlDatabase @parameters
$db2

탄력적 작업 에이전트 만들기

탄력적 작업 에이전트는 작업을 생성하고, 실행하고, 관리하기 위한 Azure 리소스입니다. 에이전트는 일정에 따라 또는 일회성 작업으로 작업을 실행합니다. 탄력적 작업의 모든 날짜와 시간은 UTC 표준 시간대를 따릅니다.

resourceGroupName, serverName 및 databaseName 매개 변수가 모두 기존 리소스를 가리켜야 하므로 New-AzSqlElasticJobAgent cmdlet에는 Azure SQL Database의 데이터베이스가 이미 있어야 합니다. 마찬가지로 Set-AzSqlElasticJobAgent를 사용하여 탄력적 작업 에이전트를 수정할 수 있습니다.

사용자가 할당한 관리 ID와 Microsoft Entra 인증을 사용하여 새 탄력적 작업 에이전트를 만들려면 New-AzSqlElasticJobAgent의 IdentityType및 IdentityID인수를 사용합니다:

Write-Output "Creating job agent..."
$agentName = Read-Host "Please enter a name for your new elastic job agent"
$parameters = @{
    Name = $agentName 
    IdentityType = "UserAssigned" 
    IdentityID = "/subscriptions/abcd1234-caaf-4ba9-875d-f1234/resourceGroups/contoso-jobDemoRG/providers/Microsoft.ManagedIdentity/userAssignedIdentities/contoso-UMI"
}
$jobAgent = $jobDatabase | New-AzSqlElasticJobAgent @parameters
$jobAgent

데이터베이스 범위 자격 증명을 사용하여 새 탄력적 작업 에이전트를 만들기 위해 IdentityType 및 IdentityID를 제공하지 않습니다.

작업 인증 만들기

탄력적 작업 에이전트는 각 대상 서버 또는 데이터베이스를 인증할 수 있어야 합니다.

작업 에이전트 인증 만들기에서 다루는 내용:

• UMI(사용자가 할당한 관리 ID)에 매핑된 데이터베이스 사용자를 사용하여 대상 서버/데이터베이스를 인증합니다.
  • Microsoft Entra 인증(구 Azure Active Directory)과 UMI를 함께 사용하는 것이 좋습니다. 이제 PowerShell cmdlet에는 UMI를 사용한 Microsoft Entra 인증을 지원하는 새 인수가 있습니다.
  • 이는 권장하는 인증 방법입니다.
• 각 데이터베이스에서 데이터베이스 범위 자격 증명에 매핑된 데이터베이스 사용자를 사용합니다.
  • 이전에는 데이터베이스 범위 자격 증명이 탄력적 작업 에이전트가 대상을 인증할 수 있는 유일한 옵션이었습니다.

대상 인증을 위해 UMI와 함께 Microsoft Entra 인증 사용

UMI(사용자가 할당한 관리 ID)에 대한 Microsoft Entra(구 Azure Active Directory)의 권장 인증 방법을 사용하려면 다음 단계를 수행합니다. 탄력적 작업 에이전트는 Entra 인증을 통해 원하는 대상 논리 서버/데이터베이스와 연결합니다.

로그인 및 데이터베이스 사용자 외에도 다음 스크립트에서 추가적인 GRANT 명령에 주목하세요. 이러한 사용 권한은 이 예제 작업에서 선택한 스크립트에 필요합니다. 작업에 다른 권한이 필요할 수 있습니다. 이 예제에서는 대상 데이터베이스에서 새 테이블을 만들기 때문에 성공적으로 실행하려면 각 대상 데이터베이스의 데이터베이스 사용자에게 적절한 권한이 필요합니다.

각 대상 서버/데이터베이스에서 UMI에 매핑되었으며 포함된 사용자를 만듭니다.

• 탄력적 작업에 논리 서버 또는 풀 대상이 있는 경우 대상 논리 서버의 master 데이터베이스에서 UMI에 매핑되었으며 포함된 사용자를 만들어야 합니다.
• 예를 들어 다음과 같이 master 데이터베이스에서 포함된 데이터베이스 로그인을 만들고 job-agent-UMI UMI(사용자가 할당한 관리 ID)에 따라 사용자 데이터베이스에서 사용자를 만듭니다:

$targetServer = '<target server name>'
$adminLogin = '<username>'
$adminPassword = '<password>'

# For the target logical server, in the master database
# Create the login named [job-agent-UMI] based on the UMI [job-agent-UMI], and a user
$params = @{
  'database' = 'master'
  'serverInstance' =  $targetServer.ServerName + '.database.windows.net'
  'username' = $adminLogin
  'password' = $adminPassword
  'outputSqlErrors' = $true
  'query' = 'CREATE LOGIN [job-agent-UMI] FROM EXTERNAL PROVIDER;'
}
Invoke-SqlCmd @params
$params.query = "CREATE USER [job-agent-UMI] FROM LOGIN [job-agent-UMI]"
Invoke-SqlCmd @params

# For each target database in the target logical server
# Create a database user from the job-agent-UMI login 
$targetDatabases = @( $db1.DatabaseName, $Db2.DatabaseName )
$createJobUserScript =  "CREATE USER [job-agent-UMI] FROM LOGIN [job-agent-UMI]"

# Grant permissions as necessary. For example ALTER and CREATE TABLE:
$grantAlterSchemaScript = "GRANT ALTER ON SCHEMA::dbo TO [job-agent-UMI]" 
$grantCreateScript = "GRANT CREATE TABLE TO [job-agent-UMI]"

$targetDatabases | % {
  $params.database = $_
  $params.query = $createJobUserScript
  Invoke-SqlCmd @params
  $params.query = $grantAlterSchemaScript
  Invoke-SqlCmd @params
  $params.query = $grantCreateScript
  Invoke-SqlCmd @params
}

대상 인증에 데이터베이스 범위 자격 증명 사용

작업 에이전트는 실행 시 대상 그룹에서 지정한 자격 증명을 사용하고 스크립트를 실행합니다. 이러한 데이터베이스 범위 자격 증명은 서버 또는 탄력적 풀을 대상 그룹 구성원 형식으로 사용하는 경우 서버 또는 탄력적 풀에서 모든 데이터베이스를 검색하기 위해 master 데이터베이스와 연결하는 데 사용하기도 합니다.

데이터베이스 범위 자격 증명은 작업 데이터베이스에서 만들어야 합니다. 모든 대상 데이터베이스는 작업이 성공적으로 완료되게 하려면 충분한 사용 권한으로 로그인해야 합니다.

이미지의 자격 증명 외에도 다음 스크립트에서 추가적인 GRANT 명령에 주목하세요. 이러한 사용 권한은 이 예제 작업에서 선택한 스크립트에 필요합니다. 작업에는 다른 권한이 필요할 수 있습니다. 이 예제에서는 대상 데이터베이스에서 새 테이블을 만들기 때문에 성공적으로 실행하려면 각 대상 데이터베이스의 데이터베이스 사용자에게 적절한 권한이 필요합니다.

각 대상 서버/데이터베이스의 로그인/사용자는 작업 사용자에 대한 데이터베이스 범위 자격 증명의 ID와 이름이 동일하고 작업 사용자의 데이터베이스 범위 자격 증명과 동일한 암호를 가져야 합니다. PowerShell 스크립트가 <strong jobuser password here>를 사용하는 경우 항상 동일한 암호를 사용합니다.

다음 예제에서는 데이터베이스 범위 자격 증명을 사용합니다. 필요한 작업 자격 증명을 만들려면(작업 데이터베이스에서) 다음 스크립트를 실행합니다. 이 스크립트는 SQL 인증을 사용하여 대상 서버/데이터베이스와 연결합니다:

# For the target logical server, in the master database
# Create the master user login, master user, and job user login
$targetServer = '<target server name>'
$adminLogin = '<username>'
$adminPassword = '<password>'

$params = @{
  'database' = 'master'
  'serverInstance' =  $targetServer.ServerName + '.database.windows.net'
  'username' = $adminLogin
  'password' = $adminPassword
  'outputSqlErrors' = $true
  'query' = 'CREATE LOGIN adminuser WITH PASSWORD=''<strong adminuser password here>'''
}
Invoke-SqlCmd @params
$params.query = "CREATE USER adminuser FROM LOGIN adminuser"
Invoke-SqlCmd @params
$params.query = 'CREATE LOGIN jobuser WITH PASSWORD=''<strong jobuser password here>'''
Invoke-SqlCmd @params

# For each target database in the target logical server
# Create the jobuser from jobuser login and check permission for script execution
$targetDatabases = @( $db1.DatabaseName, $Db2.DatabaseName )
$createJobUserScript =  "CREATE USER jobuser FROM LOGIN jobuser"

# Grant permissions as necessary. For example ALTER and CREATE TABLE:
$grantAlterSchemaScript = "GRANT ALTER ON SCHEMA::dbo TO jobuser"
$grantCreateScript = "GRANT CREATE TABLE TO jobuser"

$targetDatabases | % {
  $params.database = $_
  $params.query = $createJobUserScript
  Invoke-SqlCmd @params
  $params.query = $grantAlterSchemaScript
  Invoke-SqlCmd @params
  $params.query = $grantCreateScript
  Invoke-SqlCmd @params
}

# Create job credential in job database for admin user
Write-Output "Creating job credentials..."
$loginPasswordSecure = (ConvertTo-SecureString -String '<strong jobuser password here>' -AsPlainText -Force)
$loginadminuserPasswordSecure = (ConvertTo-SecureString -String '<strong adminuser password here>' -AsPlainText -Force)

$adminCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList "adminuser", $loginadminuserPasswordSecure
$adminCred = $jobAgent | New-AzSqlElasticJobCredential -Name "adminuser" -Credential $adminCred

$jobCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList "jobuser", $loginPasswordSecure
$jobCred = $jobAgent | New-AzSqlElasticJobCredential -Name "jobuser" -Credential $jobCred

대상 서버 및 데이터베이스 정의

대상 그룹은 작업 단계에서 실행될 데이터베이스 중 하나 이상의 집합을 정의합니다.

다음 코드 조각은 2개의 대상 그룹(serverGroup 및 serverGroupExcludingDb2)을 만듭니다. serverGroup은 실행 시 서버에 존재하는 모든 데이터베이스를 대상으로 지정하고, serverGroupExcludingDb2는 TargetDb2를 제외한 서버의 모든 데이터베이스를 대상으로 지정합니다:

Write-Output "Creating test target groups..."
# create ServerGroup target group
$serverGroup = $jobAgent | New-AzSqlElasticJobTargetGroup -Name 'ServerGroup'
$serverGroup | Add-AzSqlElasticJobTarget -ServerName $targetServerName -RefreshCredentialName $adminCred.CredentialName

# create ServerGroup with an exclusion of db2
$serverGroupExcludingDb2 = $jobAgent | New-AzSqlElasticJobTargetGroup -Name 'ServerGroupExcludingDb2'
$serverGroupExcludingDb2 | Add-AzSqlElasticJobTarget -ServerName $targetServerName -RefreshCredentialName $adminCred.CredentialName
$serverGroupExcludingDb2 | Add-AzSqlElasticJobTarget -ServerName $targetServerName -Database $db2.DatabaseName -Exclude

작업 및 단계 만들기

이 예제에서는 작업 하나와 작업을 실행할 두 개의 작업 단계를 정의합니다. 첫 번째 작업 단계(step1)는 ServerGroup대상 그룹의 모든 데이터베이스에서 새 테이블(Step1Table)을 만듭니다. 이전에 정의한 대상 그룹은 해당 항목을 제외하도록 지정했기 때문에 두 번째 작업 단계(step2)는 TargetDb2의 모든 데이터베이스에서 새 테이블(Step2Table)을 만듭니다.

Write-Output "Creating a new job..."
$jobName = "Job1"
$job = $jobAgent | New-AzSqlElasticJob -Name $jobName -RunOnce
$job

Write-Output "Creating job steps..."
$sqlText1 = "IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('Step1Table')) CREATE TABLE [dbo].[Step1Table]([TestId] [int] NOT NULL);"
$sqlText2 = "IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('Step2Table')) CREATE TABLE [dbo].[Step2Table]([TestId] [int] NOT NULL);"

$job | Add-AzSqlElasticJobStep -Name "step1" -TargetGroupName $serverGroup.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText1
$job | Add-AzSqlElasticJobStep -Name "step2" -TargetGroupName $serverGroupExcludingDb2.TargetGroupName -CredentialName $jobCred.CredentialName -CommandText $sqlText2

작업 실행

작업을 즉시 시작하려면 다음 명령을 실행합니다:

Write-Output "Start a new execution of the job..."
$jobExecution = $job | Start-AzSqlElasticJob
$jobExecution

성공적으로 완료한 후 TargetDb1에 새로운 테이블 2개가 나타나고 TargetDb2에 새로운 테이블 1개가 나타납니다.

작업이 나중에 실행되도록 예약할 수도 있습니다.

Important

탄력적 작업의 모든 시작 시간은 UTC 표준 시간대를 따릅니다.

특정 시점에 작업이 실행되도록 예약하려면 다음 명령을 실행합니다:

# run every hour starting from now
$job | Set-AzSqlElasticJob -IntervalType Hour -IntervalCount 1 -StartTime (Get-Date) -Enable

작업 실행 상태 모니터링

다음 코드 조각은 작업 실행 세부 정보를 가져옵니다:

# get the latest 10 executions run
$jobAgent | Get-AzSqlElasticJobExecution -Count 10

# get the job step execution details
$jobExecution | Get-AzSqlElasticJobStepExecution

# get the job target execution details
$jobExecution | Get-AzSqlElasticJobTargetExecution -Count 2

다음 표에서 가능한 작업 실행 상태를 나열합니다:

시스템 상태	설명
만든 날짜	작업 실행이 방금 만들어졌으며 아직 진행 중이 아닙니다.
InProgress	작업 실행이 현재 진행 중입니다.
WaitingForRetry	작업 실행이 해당 작업을 완료할 수 없어 다시 시도를 기다리고 있습니다.
성공함	작업 실행이 성공적으로 완료되었습니다.
SucceededWithSkipped	작업 실행이 성공적으로 완료되었지만 자식 중 일부를 건너뛰었습니다.
실패	작업 실행이 실패했으며 해당 재시도 횟수를 소진했습니다.
TimedOut	작업 실행 시간이 초과되었습니다.
Canceled	작업 실행이 취소되었습니다.
생략	동일한 작업 단계의 또 다른 실행이 동일한 대상에서 이미 실행 중이므로 작업 실행을 건너뛰었습니다.
WaitingForChildJobExecutions	작업 실행에서 해당 자식 실행이 완료되기를 기다리고 있습니다.

리소스 정리

리소스 그룹을 삭제하여 이 빠른 시작에서 만든 Azure 리소스를 삭제합니다.

팁

이러한 작업을 계속 사용하려면 이 문서에서 만든 리소스를 정리하지 마세요.

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

다음 단계

T-SQL을 사용하여 탄력적 작업 생성 및 관리