Creación y administración de trabajos elásticos mediante PowerShell

Se aplica a: Azure SQL Database

En este artículo se proporciona un tutorial y ejemplos para empezar a trabajar con trabajos elásticos mediante PowerShell. Los trabajos elásticos habilitan la ejecución de uno o más scripts de Transact-SQL (T-SQL) en paralelo en varias bases de datos.

En este tutorial completo, aprenderá los pasos necesarios para ejecutar una consulta en múltiples bases de datos:

Requisitos previos

Los trabajos de bases de datos elásticas tienen un conjunto de cmdlets de PowerShell.

Estos cmdlets se actualizaron en noviembre de 2023.

Instalación de los últimos cmdlets de trabajos elásticos

Si no tiene una suscripción a Azure, cree una cuenta gratuita antes de empezar.

Si aún no está presente, instale las versiones más recientes de los módulos Az.Sql y SqlServer. Ejecute los comandos siguientes en PowerShell con acceso 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 más información, consulte Instalación de un módulo de SQL Server PowerShell.

Creación de los recursos necesarios

La creación de un agente de trabajos elásticos requiere una base de datos (S1 o superior) para usarla como base de datos de trabajos elásticos.

El script siguiente crea un nuevo grupo de recursos, un servidor y una base de datos que se usará como base de datos de trabajos elásticos. El segundo script crea un segundo servidor con dos bases de datos en blanco en las que ejecutar los trabajos.

Los trabajos elásticos no tienen ningún requisito de nomenclatura específico, por lo que puede usar las convenciones de nomenclatura que desee, siempre y cuando cumplan con los requisitos de Azure. Si ya ha creado una base de datos en blanco para el servidor como base de datos de trabajos elásticos, vaya a Creación del agente de trabajos elásticos.

La configuración de una regla de firewall con New-AzSqlServerFirewallRule no es necesaria al usar el punto de conexión privado de trabajos 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

Creación del agente de trabajos elásticos

Un agente de trabajos elásticos es un recurso de Azure para crear, ejecutar y administrar trabajos. El agente ejecuta los trabajos según una programación o como un trabajo único. Todas las fechas y horas de los trabajos elásticos se encuentran en la zona horaria UTC.

El cmdlet New-AzSqlElasticJobAgent requiere que exista ya una base de datos de Azure SQL Database, por lo que los parámetros resourceGroupName, serverName y databaseName deben todos apuntar a recursos ya existentes. Del mismo modo, se puede usar Set-AzSqlElasticJobAgent para modificar el agente de trabajo elástico.

Para crear un nuevo agente de trabajo elástico mediante la autenticación de Microsoft Entra con una identidad administrada asignada por el usuario, use los argumentos IdentityType y 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 crear un nuevo agente de trabajo elástico mediante credenciales con ámbito de base de datos, IdentityType y IdentityID no se proporcionan.

Creación de la autenticación del trabajo

El agente de trabajos elásticos debe poder autenticarse en cada servidor o base de datos de destino.

Como se describe en Creación de la autenticación del agente de trabajo:

• Use los usuarios de base de datos asignados a la identidad administrada asignada por el usuario (UMI) para autenticarse en servidores o bases de datos de destino.
  • El uso de una UMI con la autenticación de Microsoft Entra (anteriormente Azure Active Directory) es el método recomendado. Los cmdlets de PowerShell ahora tienen nuevos argumentos para admitir la autenticación de Microsoft Entra con una UMI.
  • Este es el método de autenticación recomendado.
• Use usuarios de base de datos asignados a credenciales de ámbito de base de datos en cada base de datos.
  • Anteriormente, las credenciales de ámbito de base de datos eran la única opción para que el agente de trabajo elástico se autenticara en destinos.

Uso de la autenticación de Microsoft Entra con una UMI para la autenticación en destinos

Para usar el método recomendado de autenticación de Microsoft Entra (anteriormente Azure Active Directory) en una identidad administrada asignada por el usuario (UMI), siga estos pasos. El agente de trabajo elástico se conecta a los servidores lógicos o bases de datos objetivo deseados a través de la autenticación de Entra.

Además del inicio de sesión y los usuarios de la base de datos, tenga en cuenta la adición de los comandos GRANT en el siguiente script. Estos permisos son necesarios para el script que hemos elegido para este trabajo de ejemplo. Los trabajos pueden requerir permisos diferentes. Dado que en el ejemplo se crea una nueva tabla en las bases de datos de destino, el usuario de cada una de estas bases de datos necesita los permisos adecuados para ejecutarse correctamente.

En cada uno de los servidores o bases de datos de destino, cree un usuario independiente asignado a la UMI.

• Si el trabajo elástico tiene destinos de grupo o servidor lógico, debe crear el usuario independiente asignado a la UMI en la base de datos master del servidor lógico objetivo.
• Por ejemplo, para crear un inicio de sesión de base de datos independiente en la base de datos master y un usuario de la base de datos de usuario, en función de la identidad administrada asignada por el usuario (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
}

Uso de credenciales de ámbito de base de datos para la autenticación en destinos

Los agentes de trabajo usan credenciales especificadas por el grupo de destino tras la ejecución y ejecución de scripts. Estas credenciales de ámbito de base de datos también se usan para conectarse a la base de datos master para descubrir todas las bases de datos de un servidor o un grupo elástico, cuando cualquiera de estos se utiliza como el tipo de miembro del grupo de destino.

Las credenciales de ámbito de base de datos se deben crear en la base de datos de trabajos. Todas las bases de datos de destino deben tener un inicio de sesión con permisos suficientes para que el trabajo se complete correctamente.

Además de las credenciales de la imagen, observe la adición de los comandos GRANT en el siguiente script. Estos permisos son necesarios para el script que hemos elegido para este trabajo de ejemplo. Los trabajos pueden requerir permisos diferentes. Dado que en el ejemplo se crea una nueva tabla en las bases de datos de destino, el usuario de cada una de estas bases de datos necesita los permisos adecuados para ejecutarse correctamente.

El inicio de sesión o usuario de cada servidor o base de datos de destino debe tener el mismo nombre que la identidad de la credencial de ámbito de base de datos para el usuario del trabajo y la misma contraseña que la credencial de ámbito de base de datos para el usuario del trabajo. Donde el script de PowerShell usa <strong jobuser password here>, use la misma contraseña en todo.

En el ejemplo siguiente se usan credenciales de ámbito de base de datos. Para crear las credenciales de trabajo necesarias (en la base de datos de trabajos), ejecute el siguiente script, que usa la autenticación de SQL para conectarse a los servidores o bases de datos 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 + '.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

Definición de servidores y bases de datos de destino

Un grupo de destino define el conjunto de una o varias bases de datos en las que se ejecutará un paso de un trabajo.

En el fragmento de código siguiente se crean dos grupos de destino: serverGroup y serverGroupExcludingDb2. serverGroup tiene como destino todas las bases de datos que existen en el servidor en el momento de la ejecución, mientras que el destino de serverGroupExcludingDb2 son todas las bases de datos del servidor, excepto 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

Creación de un trabajo y pasos

En este ejemplo se definen un trabajo y dos pasos para que ejecute el trabajo. El primer paso de trabajo (step1) crea una nueva tabla (Step1Table) en cada base de datos del grupo de destino ServerGroup. El segundo paso de trabajo (step2) crea una nueva tabla (Step2Table) en cada base de datos excepto para TargetDb2, ya que el grupo de destino definido anteriormente especificó que se excluyera.

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

Ejecutar el trabajo

Para iniciar el trabajo inmediatamente, ejecute el comando siguiente:

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

Después de una correcta finalización debería ver dos nuevas tablas en TargetDb1 y solo una en TargetDb2.

También puede programar que el trabajo se ejecute más tarde.

Para programar un trabajo para que se ejecute en un momento determinado, ejecute el siguiente comando:

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

Supervisión del estado de las ejecuciones de trabajos

Los siguientes fragmentos de código permiten obtener la información sobre las ejecuciones:

# 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

En la tabla siguiente se muestran los posibles estados de ejecución de los trabajos:

Limpieza de recursos

Elimine los recursos de Azure que ha creado en este tutorial eliminando el grupo de recursos.

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

Paso siguiente