Edit

Create an Elastic Job agent using PowerShell (preview)

  • Article

Applies to: Azure SQL Database

Elastic jobs (preview) enable the running of one or more Transact-SQL (T-SQL) scripts in parallel across many databases.

In this tutorial, you learn the steps required to run a query across multiple databases:

  • Create an Elastic Job agent
  • Create job credentials so that jobs can execute scripts on its targets
  • Define the targets (servers, elastic pools, databases) you want to run the job against
  • Create database scoped credentials in the target databases so the agent connect and execute jobs
  • Create a job
  • Add job steps to a job
  • Start execution of a job
  • Monitor a job

Prerequisites

The upgraded version of Elastic Database jobs has a new set of PowerShell cmdlets for use during migration. These new cmdlets transfer all of your existing job credentials, targets (including databases, servers, custom collections), job triggers, job schedules, job contents, and jobs over to a new Elastic Job agent.

Install the latest Elastic Jobs cmdlets

If you don't have already have an Azure subscription, create a free account before you begin.

Install the Az.Sql module to get the latest Elastic Job cmdlets. Run the following commands in PowerShell with administrative access.

# 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

Get-Module Az.Sql

In addition to the Az.Sql module, this tutorial also requires the SqlServer PowerShell module. For details, see Install SQL Server PowerShell module.

Create required resources

Creating an Elastic Job agent requires a database (S1 or higher) for use as the Job database.

The script below creates a new resource group, server, and database for use as the Job database. The second script creates a second server with two blank databases to execute jobs against.

Elastic Jobs has no specific naming requirements so you can use whatever naming conventions you want, as long as they conform to any Azure requirements.

# sign in to Azure account
Connect-AzAccount

# 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"
$rg = New-AzResourceGroup -Name $resourceGroupName -Location $location
$rg

# create a 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
$agentServer = New-AzSqlServer -ResourceGroupName $resourceGroupName -Location $location `
    -ServerName $agentServerName -ServerVersion "12.0" -SqlAdministratorCredentials ($adminCred)

# set server firewall rules to allow all Azure IPs
Write-Output "Creating a server firewall rule..."
$agentServer | New-AzSqlServerFirewallRule -AllowAllAzureIPs
$agentServer

# create the job database
Write-Output "Creating a blank database to be used as the Job Database..."
$jobDatabaseName = "JobDatabase"
$jobDatabase = New-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $agentServerName -DatabaseName $jobDatabaseName -RequestedServiceObjectiveName "S1"
$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()
$targetServer = New-AzSqlServer -ResourceGroupName $resourceGroupName -Location $location `
    -ServerName $targetServerName -ServerVersion "12.0" -SqlAdministratorCredentials ($adminCred)

# set target server firewall rules to allow all Azure IPs
$targetServer | New-AzSqlServerFirewallRule -AllowAllAzureIPs
$targetServer | New-AzSqlServerFirewallRule -StartIpAddress 0.0.0.0 -EndIpAddress 255.255.255.255 -FirewallRuleName AllowAll
$targetServer

# create sample databases to execute jobs against
$db1 = New-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $targetServerName -DatabaseName "database1"
$db1
$db2 = New-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $targetServerName -DatabaseName "database2"
$db2

Create the Elastic Job agent

An Elastic Job agent is an Azure resource for creating, running, and managing jobs. The agent executes jobs based on a schedule or as a one-time job.

The New-AzSqlElasticJobAgent cmdlet requires a database in Azure SQL Database to already exist, so the resourceGroupName, serverName, and databaseName parameters must all point to existing resources.

Write-Output "Creating job agent..."
$agentName = Read-Host "Please enter a name for your new Elastic Job agent"
$jobAgent = $jobDatabase | New-AzSqlElasticJobAgent -Name $agentName
$jobAgent

Create the job credentials

Jobs use database scoped credentials to connect to the target databases specified by the target group upon execution and execute scripts. These database scoped credentials are also used to connect to the master database to enumerate all the databases in a server or an elastic pool, when either of these are used as the target group member type.

The database scoped credentials must be created in the job database. All target databases must have a login with sufficient permissions for the job to complete successfully.

Elastic Jobs credentials

In addition to the credentials in the image, note the addition of the GRANT commands in the following script. These permissions are required for the script we chose for this example job. Because the example creates a new table in the targeted databases, each target db needs the proper permissions to successfully run.

To create the required job credentials (in the job database), run the following script:

# in the master database (target server)
# create the master user login, master user, and job user login
$params = @{
  'database' = 'master'
  'serverInstance' =  $targetServer.ServerName + '.database.windows.net'
  'username' = $adminLogin
  'password' = $adminPassword
  'outputSqlErrors' = $true
  'query' = 'CREATE LOGIN masteruser WITH PASSWORD=''password!123'''
}
Invoke-SqlCmd @params
$params.query = "CREATE USER masteruser FROM LOGIN masteruser"
Invoke-SqlCmd @params
$params.query = 'CREATE LOGIN jobuser WITH PASSWORD=''password!123'''
Invoke-SqlCmd @params

# for each target database
# create the jobuser from jobuser login and check permission for script execution
$targetDatabases = @( $db1.DatabaseName, $Db2.DatabaseName )
$createJobUserScript =  "CREATE USER jobuser FROM LOGIN jobuser"
$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 master user
Write-Output "Creating job credentials..."
$loginPasswordSecure = (ConvertTo-SecureString -String 'password!123' -AsPlainText -Force)

$masterCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList "masteruser", $loginPasswordSecure
$masterCred = $jobAgent | New-AzSqlElasticJobCredential -Name "masteruser" -Credential $masterCred

$jobCred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList "jobuser", $loginPasswordSecure
$jobCred = $jobAgent | New-AzSqlElasticJobCredential -Name "jobuser" -Credential $jobCred

Define the target databases to run the job against

A target group defines the set of one or more databases a job step will execute on.

The following snippet creates two target groups: serverGroup, and serverGroupExcludingDb2. serverGroup targets all databases that exist on the server at the time of execution, and serverGroupExcludingDb2 targets all databases on the server, except targetDb2:

Write-Output "Creating test target groups..."
# create ServerGroup target group
$serverGroup = $jobAgent | New-AzSqlElasticJobTargetGroup -Name 'ServerGroup'
$serverGroup | Add-AzSqlElasticJobTarget -ServerName $targetServerName -RefreshCredentialName $masterCred.CredentialName

# create ServerGroup with an exclusion of db2
$serverGroupExcludingDb2 = $jobAgent | New-AzSqlElasticJobTargetGroup -Name 'ServerGroupExcludingDb2'
$serverGroupExcludingDb2 | Add-AzSqlElasticJobTarget -ServerName $targetServerName -RefreshCredentialName $masterCred.CredentialName
$serverGroupExcludingDb2 | Add-AzSqlElasticJobTarget -ServerName $targetServerName -Database $db2.DatabaseName -Exclude

Create a job and steps

This example defines a job and two job steps for the job to run. The first job step (step1) creates a new table (Step1Table) in every database in target group ServerGroup. The second job step (step2) creates a new table (Step2Table) in every database except for TargetDb2, because the target group defined previously specified to exclude it.

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

Run the job

To start the job immediately, run the following command:

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

After successful completion you should see two new tables in TargetDb1, and only one new table in TargetDb2:

new tables verification in SSMS

You can also schedule the job to run later. To schedule a job to run at a specific time, run the following command:

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

Monitor status of job executions

The following snippets get job execution details:

# 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

The following table lists the possible job execution states:

State Description
Created The job execution was just created and is not yet in progress.
InProgress The job execution is currently in progress.
WaitingForRetry The job execution wasn't able to complete its action and is waiting to retry.
Succeeded The job execution has completed successfully.
SucceededWithSkipped The job execution has completed successfully, but some of its children were skipped.
Failed The job execution has failed and exhausted its retries.
TimedOut The job execution has timed out.
Canceled The job execution was canceled.
Skipped The job execution was skipped because another execution of the same job step was already running on the same target.
WaitingForChildJobExecutions The job execution is waiting for its child executions to complete.

Clean up resources

Delete the Azure resources created in this tutorial by deleting the resource group.

Tip

If you plan to continue to work with these jobs, you do not clean up the resources created in this article.

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

Next steps

In this tutorial, you ran a Transact-SQL script against a set of databases. You learned how to do the following tasks:

  • Create an Elastic Job agent
  • Create job credentials so that jobs can execute scripts on its targets
  • Define the targets (servers, elastic pools, databases) you want to run the job against
  • Create database scoped credentials in the target databases so the agent connect and execute jobs
  • Create a job
  • Add a job step to the job
  • Start an execution of the job
  • Monitor the job

Manage Elastic Jobs using Transact-SQL