Создание эластичных заданий и управление ими с помощью PowerShell

Применимо к: База данных SQL Azure

В этой статье предлагаются руководство и примеры для начала работы с масштабируемыми задачами с помощью PowerShell. Задания обработки эластичных баз данных позволяют выполнять скрипты Transact-SQL (T-SQL) в нескольких базах данных одновременно.

В этом комплексном руководстве описаны действия, необходимые для выполнения запроса в нескольких базах данных:

Предварительные условия

Задания эластичной базы данных имеют набор командлетов PowerShell.

Эти командлеты были обновлены в ноябре 2023 года.

Установите новейшие командлеты эластичных заданий

Если у вас нет подписки 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 требует, чтобы база данных в Azure SQL Database уже существовала, поэтому параметры resourceGroupName, serverName, и databaseName должны указывать на существующие ресурсы. Аналогичным образом можно использовать Set-AzSqlElasticJobAgent для изменения агента эластичных заданий.

Чтобы создать новый агент эластичных заданий с использованием проверки подлинности Microsoft Entra и управляемого удостоверения, назначаемого пользователем, используйте аргументы IdentityType и IdentityID для New-AzSqlElasticJobAgent.

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 теперь имеют новые аргументы для поддержки проверки подлинности Microsoft Entra с помощью UMI.
  • Это рекомендуемый метод проверки подлинности.
• Используйте пользователей базы данных, сопоставленных с учетными данными, принадлежащими области базы данных, в каждой базе данных.
  • Ранее учетные данные, связанные с областью базы данных, были единственным вариантом для агента эластичных заданий для аутентификации на целевых системах.

Используйте аутентификацию Microsoft Entra с UMI для целевых объектов.

Чтобы использовать рекомендуемый метод проверки подлинности Microsoft Entra (ранее Azure Active Directory) для управляемого удостоверения, назначаемого пользователем (UMI), выполните следующие действия. Агент эластичного задания подключается к требуемому целевому логическому серверу или базам данных через проверку подлинности Entra.

Помимо пользователей входа и базы данных, обратите внимание на то, что команды GRANT добавлены в следующем скрипте. Эти разрешения являются обязательными для скрипта, выбранного в этом примере задания. Для ваших заданий могут потребоваться разные разрешения. Так как в примере создается новая таблица в целевых базах данных, пользователю базы данных в каждой целевой базе данных требуются соответствующие разрешения для успешного выполнения.

В каждом целевом сервере или базе данных создайте автономного пользователя, сопоставленного с UMI.

• Если у эластичного задания есть логические серверы или целевые объекты пула, необходимо создать автономного пользователя, сопоставленного с UMI в master базе данных целевого логического сервера.
• Например, чтобы создать логин изолированной базы данных в базе данных master и пользователя в базе данных пользователей, на основе управляемого удостоверения, назначенного пользователем (UMI) с именем job-agent-UMI:

$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 + '.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

Создание задания и шагов

В этом примере определяется задание и два шага для выполнения задания. Первый шаг задания () создает новую таблицу (step1Step1Table) в каждой базе данных в целевой группеServerGroup. Второй шаг задания (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.

Кроме того, вы можете запланировать более позднее выполнение задания.

Чтобы запланировать выполнение задания в определенное время, выполните следующую команду:

# 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

В приведенной ниже таблице указаны возможные состояния выполнения заданий.

Очистка ресурсов

Удалите ресурсы Azure, созданные в рамках этого руководства, удалив группу ресурсов.

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

Следующий шаг