Criar e gerenciar trabalhos elásticos usando o PowerShell

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure

Este artigo fornece um tutorial e exemplos para começar a trabalhar com trabalhos elásticos usando o PowerShell. Os trabalhos elásticos habilitam a execução de um ou mais scripts T-SQL (Transact-SQL) em paralelo entre vários bancos de dados.

Neste tutorial completo, você aprenderá as etapas necessárias para executar uma consulta em vários bancos de dados:

  • Criar um agente de trabalho elástico
  • Criar credenciais de trabalho para que os trabalhos possam executar scripts em seus destinos
  • Definir os destinos (servidores, pools elásticos, bancos de dados) nos quais você deseja executar o trabalho
  • Criar credenciais no escopo do banco de dados nos bancos de dados de destino para que o agente se conecte e execute trabalhos
  • Criar um trabalho
  • Adicionar etapas de trabalho a um trabalho
  • Iniciar a execução de um trabalho
  • Monitorar um trabalho

Pré-requisitos

Trabalhos de banco de dados elásticos têm um conjunto de cmdlets do PowerShell.

Estes cmdlets foram atualizados em novembro de 2023.

Instalar os cmdlets de trabalhos elásticos mais recentes

Se você não tiver uma assinatura do Azure, crie uma conta gratuita antes de começar.

Se ainda não estiverem presentes, instale as versões mais recentes dos módulos Az.Sql e SqlServer. Execute os comandos a seguir no PowerShell com acesso administrativo.

# 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

Para obter detalhes, confira Instalar o módulo do SQL Server PowerShell.

Criar recursos necessários

A criação de um agente de trabalho elástico requer um banco de dados (S1 ou superior) a ser usado como o banco de dados de trabalhos.

O script a seguir cria um novo grupo de recursos, servidor e banco de dados para uso como o banco de dados de trabalhos elásticos. O segundo script cria um segundo servidor com dois bancos de dados em branco nos quais os trabalhos serão executados.

Trabalhos elásticos não têm nenhum requisito de nomenclatura específico. Você pode usar as convenções de nomenclatura que desejar, desde que estejam em conformidade com os requisitos do Azure. Se você já tiver criado um banco de dados em branco para o servidor como o banco de dados de trabalhos elásticos, acesse Criar o agente de trabalho elástico.

Não é necessário configurar uma regra de firewall com New-AzSqlServerFirewallRule ao usar o ponto de extremidade privado de trabalhos elásticos.

# 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

Criar o agente de trabalho elástico

Um agente de trabalho elástico é um recurso do Azure para criar, executar e gerenciar trabalhos. O agente executa trabalhos com base em uma agenda ou como um único trabalho. Todas as datas e horas em trabalhos elásticos estão no fuso horário UTC.

O cmdlet New-AzSqlElasticJobAgent exige que um banco de dados no Banco de Dados SQL do Azure já exista e, portanto, os parâmetros resourceGroupName, serverName e databaseName devem apontar para recursos existentes. Da mesma maneira, Set-AzSqlElasticJobAgent pode ser usado para modificar o agente de trabalho elástico.

Para criar um novo agente de trabalho elástico usando a autenticação do Microsoft Entra com uma identidade gerenciada atribuída pelo usuário, use os argumentos IdentityType e IdentityID de 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

Para criar um novo agente de trabalho elástico usando credenciais no escopo de banco de dados, IdentityType e IdentityID não são fornecidos.

Criar a autenticação do trabalho

O agente de trabalho elástico deve ser capaz de se autenticar em cada servidor ou banco de dados de destino.

Conforme abordado em Criar autenticação do agente de trabalho:

Usar a autenticação do Microsoft Entra com uma UMI para autenticação em destinos

Para usar o método recomendado de autenticação do Microsoft Entra (o antigo Azure Active Directory) para uma identidade gerenciada atribuída pelo usuário (UMI), siga estas etapas. O agente de trabalho elástico se conecta aos servidores lógicos/bancos de dados de destino desejados por meio da autenticação do Entra.

Além dos usuários de logon e banco de dados, observe a adição dos comandos GRANT no script a seguir. Essas permissões são necessárias para o script que escolhemos como exemplo para o trabalho. Seus trabalhos podem exigir permissões diferentes. O exemplo cria uma nova tabela nos bancos de dados de destino, o usuário de banco de dados de cada banco de dados de destino precisa ter as permissões apropriadas para ser executado com êxito.

Em cada um dos servidores/bancos de dados de destino, crie um usuário independente mapeado para a UMI.

  • Se o trabalho elástico tiver destinos de pool ou servidor lógico, você deverá criar o usuário independente mapeado para a UMI no banco de dados master do servidor lógico de destino.
  • Por exemplo, para criar um logon de banco de dados independente no banco de dados master e um usuário no banco de dados de usuário com base na identidade gerenciada atribuída pelo usuário (UMI) denominada 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
}

Usar credenciais no escopo de banco de dados para autenticação em destinos

Agentes de trabalho usam credenciais especificadas pelo grupo de destino na execução e executam scripts. Essas credenciais no escopo do banco de dados também são usadas para se conectar ao banco de dados master e descobrir todos os bancos de dados em um servidor ou um pool elástico, quando um destes é usado como o tipo de membro do grupo de destino.

As credenciais no escopo do banco de dados devem ser criadas no banco de dados de trabalhos. Todos os bancos de dados de destino devem ter um logon com permissões suficientes para que o trabalho seja concluído com êxito.

Além das credenciais na imagem, observe a adição dos comandos GRANT no script a seguir. Essas permissões são necessárias para o script que escolhemos como exemplo para o trabalho. Seus trabalhos podem exigir permissões diferentes. O exemplo cria uma nova tabela nos bancos de dados de destino, o usuário de banco de dados de cada banco de dados de destino precisa ter as permissões apropriadas para ser executado com êxito.

O logon/usuário em cada servidor/banco de dados de destino deve ter o mesmo nome que a identidade da credencial no escopo do banco de dados para o usuário do trabalho e a mesma senha que a credencial no escopo do banco de dados para o usuário do trabalho. Onde o script do PowerShell usar <strong jobuser password here>, use a mesma senha em todo o processo.

O exemplo a seguir usa credenciais no escopo do banco de dados. Para criar as credenciais de trabalho necessárias (no banco de dados de trabalhos), execute o seguinte script, que usa a Autenticação SQL para se conectar aos servidores/bancos de dados de destino:

# 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

Definir servidores e bancos de dados de destino

Um grupo de destino define o conjunto de um ou mais bancos de dados em que uma etapa de trabalho será executada.

O snippet a seguir cria dois grupos de destino: serverGroup e serverGroupExcludingDb2. serverGroup tem como alvo todos os bancos de dados existentes no momento da execução e serverGroupExcludingDb2 tem como alvo todos os bancos de dados no servidor, exceto 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

Criar um trabalho e etapas

Este exemplo define um trabalho e duas etapas de trabalho para que o trabalho seja executado. A primeira etapa de trabalho (step1) cria uma nova tabela (Step1Table) em cada banco de dados no grupo de destino ServerGroup. A segunda etapa de trabalho (step2) cria uma nova tabela (Step2Table) em cada banco de dados, exceto para TargetDb2, pois o grupo de destino definido anteriormente está especificado para excluí-lo.

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

Executar o trabalho

Para iniciar o trabalho imediatamente, execute o seguinte comando:

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

Após a conclusão bem-sucedida, você deverá ver duas tabelas novas em TargetDb1 e apenas uma tabela nova em TargetDb2.

Você também pode agendar o trabalho para ser executado posteriormente.

Importante

Todas as horas de início em trabalhos elásticos estão no fuso horário UTC.

Para agendar um trabalho para execução em um momento específico, execute o seguinte comando:

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

Monitorar status de execuções de trabalho

Os seguintes snippets de código obtêm os detalhes de execução do trabalho:

# 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

A tabela a seguir lista os estados possíveis de execução de trabalho:

Estado Descrição
Criado A execução do trabalho acabou de ser criada e não ainda está em andamento.
InProgress A execução do trabalho está atualmente em andamento.
WaitingForRetry A execução do trabalho não conseguiu concluir a ação e está aguardando para tentar novamente.
Êxito A execução do trabalho foi concluída com êxito.
SucceededWithSkipped A execução do trabalho foi concluída com êxito, mas alguns de seus filhos foram ignorados.
Com falha A execução do trabalho falhou e esgotou suas novas tentativas.
TimedOut A execução do trabalho atingiu o tempo limite.
Cancelado A execução do trabalho foi cancelada.
Ignorado A execução do trabalho foi ignorada porque outra execução da mesma etapa de trabalho já estava em execução no mesmo destino.
WaitingForChildJobExecutions A execução do trabalho está esperando a conclusão das execuções filhas.

Limpar os recursos

Exclua os recursos do Azure criados neste tutorial excluindo o grupo de recursos.

Dica

Se planejar continuar a usar esses trabalhos, não limpe os recursos criados neste artigo.

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

Próxima etapa

Criar e gerenciar trabalhos elásticos usando o T-SQL