使用 PowerShell 建立及管理彈性作業

適用於：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

# 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 時區。

New-AzSqlElasticJobAgent Cmdlet 會要求 Azure SQL Database 中必須已有資料庫存在，因此 resourceGroupName、serverName 與 databaseName 參數全都必須指向現有的資源。 同樣地，Set-AzSqlElasticJobAgent 可用於修改彈性工作代理程式。

若要透過 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) 的資料庫使用者來執行目標伺服器/資料庫驗證。
  • 建議的方法是，使用 UMI 搭配 Microsoft Entra 驗證 (先前稱為 Azure Active Directory)。 PowerShell Cmdlet 現在有新的引數，可支援使用 Microsoft Entra 驗證搭配 UMI。
  • 這是建議的驗證方法。
• 在各個資料庫中，使用對應至資料庫範圍認證的資料庫使用者。
  • 以往，資料庫範圍認證是彈性作業代理程式用於驗證目標的唯一選項。

使用 Microsoft Entra 驗證搭配 UMI 來執行目標驗證

若要對使用者指派的受控識別 (UMI) 使用建議的 Microsoft Entra (先前稱為 Azure Active Directory) 驗證方法，請遵循下列步驟操作。 彈性工作代理程式會透過 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.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 命令。 我們為此範例作業選擇的指令碼需要這些權限。 您的工作可能需要不同的權限。 由於此範例會在目標資料庫中建立新的資料表，因此每個目標資料庫中的資料庫使用者都必須具備適當的權限才能成功執行。

每個目標伺服器/資料庫的登入/使用者都必須與作業使用者的資料庫範圍認證身分識別具有相同的名稱，並與作業使用者的資料庫範圍認證具有相同的密碼。 當 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

定義目標伺服器和資料庫

目標群組可定義一或多個將會執行作業步驟的資料庫。

下列片段會建立兩個目標群組：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) 會在每個資料庫中建立新的資料表 (Step2Table)，但 TargetDb2 除外，因為先前定義的目標群組指定要加以排除。

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

下表列出可能的作業執行狀態：

State	描述
建立日期	作業執行剛建立，且尚未開始執行。
InProgress	作業執行目前正在進行中。
WaitingForRetry	作業執行無法完成其動作，正在等待重試。
已成功	作業執行已順利完成。
SucceededWithSkipped	作業執行已順利完成，但略過了部分子系。
已失敗	作業執行失敗，且用完重試次數。
TimedOut	作業執行逾時。
Canceled	作業執行已取消。
已略過	已略過作業執行，因為已在相同的目標上執行相同作業步驟的另一個執行。
WaitingForChildJobExecutions	作業執行正在等候其子系執行完成。

清除資源

您可以刪除資源群組，以刪除在本教學課程中建立的 Azure 資源。

提示

如果您打算繼續使用這些作業，請勿清除在此本文中建立的資源。

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

後續步驟

使用 T-SQL 建立及管理彈性作業