Creare e gestire processi elastici usando PowerShell

Si applica a:Database SQL di Azure

Questo articolo fornisce numerose esercitazioni ed esempi per iniziare a usare i processi elastici tramite PowerShell. I processi elastici permettono l'esecuzione di uno o più script di Transact-SQL (T-SQL) in parallelo tra molti database.

Questa esercitazione end-to-end illustra tutte le fasi necessarie per l'esecuzione di una query tra più database:

Prerequisiti

I processi di database elastici possiedono un insieme di cmdlet PowerShell.

Tali cmdlet sono stati aggiornati a novembre 2023.

Installare la versione più recente dei cmdlet per i processi elastici

Se non si ha una sottoscrizione di Azure, creare un account gratuito prima di iniziare.

Se non è già presente, installare le versioni più recenti dei Az.Sql e dei SqlServer moduli. Eseguire questi comandi in PowerShell con accesso amministrativo.

# 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

Per i dettagli, vedere Installare il modulo SQL Server PowerShell.

Creare le risorse necessarie

La creazione di un agente di processi elastici richiede un database (S1 o versione successiva) da usare come database per processi elastici.

Lo script seguente crea un nuovo gruppo di risorse, un server e un database da usare come database di gestione dei processi elastici. Il secondo script crea un secondo server con due database vuoti per l'esecuzione dei processi.

I processi elastici non hanno requisiti di denominazione specifici, pertanto è possibile usare le convenzioni di denominazione desiderate, purché siano conformi ai requisiti di Azure. Se è già stato creato un database vuoto per il server come database dei processi elastici, passare a Creare l'agente di processi elastici.

La configurazione di una regola del firewall con New-AzSqlServerFirewallRule non è necessaria quando si utilizza l'endpoint privato dei job elastici.

# 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

Creare l'agente dei processi elastici

Un agente di processi elastici è una risorsa di Azure per la creazione, l'esecuzione e la gestione dei processi. L'agente esegue i processi in base a una pianificazione o come processo unico. Tutte le date e orari nei lavori elastici sono nel fuso orario UTC.

Il cmdlet New-AzSqlElasticJobAgent richiede un database già esistente nel database SQL di Azure, quindi i parametri resourceGroupName, serverName, e databaseName devono indirizzare a risorse esistenti. Analogamente, è possibile usare Set-AzSqlElasticJobAgent per modificare l'agente di processi elastici.

Per creare un nuovo agente di processi elastici usando l'autenticazione di Microsoft Entra con un'identità gestita assegnata dall'utente, usare gli argomenti IdentityType e IdentityID di 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

Per creare un nuovo agente di processi elastici utilizzando credenziali con ambito database, IdentityType e IdentityID non sono forniti.

Creare l'autenticazione del processo

L'operatore del processo elastico deve essere in grado di autenticarsi in ogni server o database di destinazione.

Come illustrato in Creare l'autenticazione dell'agente di processi:

• Usare gli utenti del database mappati all'identità gestita assegnata dall'utente (UMI) per eseguire l'autenticazione nel/nei server/database di destinazione.
  • Si consiglia di usare un'identità gestita assegnata dall'utente (UMI) con l'autenticazione di Microsoft Entra (in precedenza Azure Active Directory). I cmdlet di PowerShell ora dispongono di nuovi argomenti per supportare l'autenticazione di Microsoft Entra con una UMI (identità gestita dall'utente).
  • Questo è il metodo di autenticazione consigliato.
• Usare gli utenti del database mappati allecredenziali nell’ambito del database in ogni database.
  • In precedenza, le credenziali nell'ambito del database erano l'unica opzione a disposizione dell'agente di lavoro elastico per autenticarsi agli obiettivi.

Usare l'autenticazione di Microsoft Entra con un'identità gestita assegnata dall'utente per l'autenticazione nelle destinazioni

Per usare il metodo consigliato per l'autenticazione di Microsoft Entra (in precedenza Azure Active Directory) con un'identità gestita assegnata dall'utente, procedere come segue. L'agente dei processi elastici si connette ai server logici o ai database di destinazione desiderati tramite l'autenticazione Entra.

Oltre all'accesso e agli utenti del database, prendere nota dell'aggiunta dei comandi GRANT nello script seguente. Queste autorizzazioni sono necessarie per lo script scelto per questo lavoro di esempio. I tuoi lavori possono richiedere permessi diversi. Poiché nell'esempio viene creata una nuova tabella nei database di destinazione, l'utente database di ogni destinazione necessita di autorizzazioni appropriate per la corretta esecuzione.

In ogni server o database di destinazione, creare un utente contenuto mappato all'UMI.

• Se il processo elastico ha come destinazioni server o pool logici, è necessario creare l'utente contenuto mappato all'identità gestita nel master database del server logico di destinazione.
• Ad esempio, per creare un account di accesso contenuto nel database master e un utente nel database utente, in base all'identità gestita assegnata dall'utente denominata 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
}

Usare le credenziali con ambito database per l'autenticazione alle destinazioni

Gli agenti di lavoro usano le credenziali specificate dal gruppo di destinazione durante l'esecuzione ed eseguono script. Queste credenziali con ambito database permettono inoltre di connettersi al master database per enumerare tutti i database in un server o in un pool elastico, quando uno di questi viene usato come tipo di membro del gruppo di destinazione.

Le credenziali con ambito database devono essere create nel database di processo. Tutti i database di destinazione devono disporre di un accesso con autorizzazioni sufficienti per completare correttamente il processo.

Oltre alle credenziali riportate nella figura, si noti l'aggiunta dei comandi GRANT nello script seguente. Queste autorizzazioni sono necessarie per lo script scelto per questo esempio di lavoro. I tuoi lavori potrebbero richiedere autorizzazioni diverse. Poiché nell'esempio viene creata una nuova tabella nei database di destinazione, l'utente database di ogni destinazione necessita di autorizzazioni appropriate per la corretta esecuzione.

L'utente di accesso su ciascun server/database di destinazione deve avere lo stesso nome dell'identità della credenziale con ambito del database per l'utente del processo e la stessa password della credenziale con ambito del database per l'utente del processo. Laddove lo script di PowerShell usa <strong jobuser password here>, usare la stessa password per qualsiasi processo di autenticazione.

Nell'esempio seguente vengono usate le credenziali a livello di database. Per creare le credenziali del processo richieste (nel database del processo), eseguire lo script seguente, che usa l'autenticazione SQL per connettersi ai server di destinazione/database:

# 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

Definire server e database di destinazione

Un gruppo di destinazione definisce il set di uno o più database in cui verrà eseguito il passaggio di un processo.

Il seguente frammento di codice crea due gruppi di destinazione: serverGroup, e serverGroupExcludingDb2. serverGroup ha come destinazione tutti i database esistenti nel server durante la fase di esecuzione, mentre serverGroupExcludingDb2 ha come destinazione tutti i database nel server, ad eccezione di 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

Creare un processo e i passaggi

Questo esempio definisce un processo e due passaggi di processo per l'esecuzione del processo. La prima fase del processo (step1) crea una nuova tabella (Step1Table) in ogni database nel gruppo di destinazione ServerGroup. La seconda fase del processo (step2) crea una nuova tabella (Step2Table) in ogni database ad eccezione di TargetDb2, perché nel gruppo di destinazione definito in precedenza ne è stata specificata l'esclusione.

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

Eseguire il job

Per avviare immediatamente il processo, eseguire il comando seguente:

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

Dopo il corretto completamento, dovrebbero essere visibili due nuove tabelle in TargetDb1 e solo una nuova tabella in TargetDb2.

È anche possibile pianificare l'esecuzione del processo in un secondo momento.

Per pianificare l'esecuzione di un processo in un momento specifico, eseguire il comando seguente:

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

Monitorare lo stato delle esecuzioni del processo

I seguenti frammenti di codice recuperano i dettagli di esecuzione del processo.

# 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

La tabella seguente elenca i possibili stati di esecuzione dei processi:

Pulire le risorse

Eliminare le risorse di Azure create in questa esercitazione eliminando il gruppo di risorse.

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

Passaggio successivo