使用 PowerShell 创建和管理弹性作业

项目
08/12/2024

适用于：Azure SQL 数据库

本文通过教程和示例说明了如何使用 PowerShell 来完成弹性作业的入门。弹性作业可以跨多个数据库并行运行一个或多个 Transact-SQL (T-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 或更高版本）用作弹性作业数据库。

以下脚本创建新的资源组、服务器以及可用作弹性作业数据库的数据库。第二个脚本创建另一个服务器，其中包含两个用于对其执行作业的空数据库。

弹性作业没有特定的命名要求，因此可以使用所需的任何命名约定，只要其符合 Azure 要求即可。如果已创建服务器的空白数据库作为弹性作业数据库，请跳至创建弹性作业代理。

使用弹性作业专用终结点时，无需配置 New-AzSqlServerFirewallRule 的防火墙规则。

# Sign in to your Azure account
Connect-AzAccount -EnvironmentName AzureChinaCLoud

# 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"
$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 时区。

New-AzSqlElasticJobAgent cmdlet 要求 Azure SQL 数据库中已存在一个数据库，因此 resourceGroupName、serverName 和 databaseName 参数必须都指向现有资源。同样，Set-AzSqlElasticJobAgent 可用于修改弹性作业代理。

要使用 Microsoft Entra 身份验证和用户分配的托管标识创建新的弹性作业代理，请使用 IdentityType 和 New-AzSqlElasticJobAgent 的 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) 的数据库用户对目标服务器/数据库进行身份验证。
- 建议将 UMI 与 Microsoft Entra 身份验证（以前称为 Azure Active Directory）配合使用。 PowerShell cmdlet 现在具有新的参数，以支持使用 UMI 进行 Microsoft Entra 身份验证。
- 这是推荐的身份验证方法。
在每个数据库中使用映射到数据库范围的凭据的数据库用户。
- 以前，数据库范围的凭据是弹性作业代理向目标进行身份验证的唯一选项。

将 Microsoft Entra 身份验证与 UMI 配合使用以对目标进行身份验证

要按照建议将 Microsoft Entra（以前称为 Azure Active Directory）身份验证与用户分配的托管标识 (UMI) 配合使用，请执行以下步骤。弹性作业代理通过 Entra 身份验证连接到所需的目标逻辑服务器/数据库。

请注意，除了登录名和数据库用户，还在以下脚本中添加了 GRANT 命令。这些权限是为此示例作业选择的脚本所需要的。作业可能需要不同的权限才能运行。由于此示例会在目标数据库中创建新表，因此每个目标数据库中的数据库用户需要适当的权限才能成功运行。

在每个目标服务器/数据库中，创建映射到 UMI 的包含用户。

如果弹性作业具有逻辑服务器或池目标，则必须在目标逻辑服务器的 master 数据库中创建映射到 UMI 的包含用户。
例如，若要根据名为 job-agent-UMI 的用户分配的托管标识 (UMI) 在 master 数据库中创建包含的数据库登录名，并在用户数据库中创建用户：

$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.chinacloudapi.cn'
  '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 命令。这些权限是为此示例作业选择的脚本所需要的。作业可能需要不同的权限才能运行。由于此示例会在目标数据库中创建新表，因此每个目标数据库中的数据库用户需要适当的权限才能成功运行。

每个目标服务器/数据库的登录名/用户必须采用与作业用户的数据库范围凭据的标识相同的名称，并采用与作业用户的数据库范围凭据相同的密码。 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.chinacloudapi.cn'
  '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

定义目标服务器和数据库

目标组定义可以在其上执行作业步骤的数据库集（包含一个或多个数据库）。

以下代码片段将创建两个目标组：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 中看到两个新表，在 TargetDb2 中只看到一个新表。

还可以将作业计划为在以后运行。

重要

弹性作业中的所有开始时间均处于 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	作业执行已超时。
已取消	作业执行已取消。
已跳过	已跳过作业执行，因为同一作业步骤的另一个执行已在同一目标上运行。
WaitingForChildJobExecutions	作业执行正在等待其子执行完成。

清理资源

通过删除资源组来删除本教程中创建的 Azure 资源。

提示

如果打算继续使用这些作业，请勿清理本文中创建的资源。

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

下一步

使用 T-SQL 创建和管理弹性作业

通过