Fundamental Concepts for Elastic Jobs in Azure SQL Database

In my previous post, I explained what Elastic Jobs are and why you might consider using them. In this post, I’ll be discussing two fundamental concepts that you should understand before creating elastic jobs: Idempotence, and Credentials.

Idempotence:

It is very important that the T-SQL scripts being executed by Elastic Jobs be idempotent.  This means that if they are run multiple times (by accident or intentionally) they won’t fail and won’t produce unintended results. If an elastic job has some side effects, and gets run more than once, it could fail or cause other unintended consequences (like consuming double the resources needed for a large statistics update).  One way to ensure idempotence is to make sure that you check if something already exists before trying to create it.

IF NOT EXISTS

(<SELECT OBJECT HERE YOU MIGHT CREATE>)

CREATE

<OBJECT YOU INTEND TO CREATE>

A good way to test if your job is idempotent is to run your script multiple times on a single target and see if the operation fails on subsequent executions, or to make sure that, if a single run consumes many resources, repeated runs do not also consume those resources.

Credentials:

There are two categories of credentials that are needed to create and run Elastic Jobs.  The Refresh Credential and the Job Credential.  In practice, a single elastic job will have one refresh credential, and each job step must have one job credential.  The refresh credential and the job credential do not have to be the same credential.  Further jobs with more than one step can use a diffuse a different job credential for each step. 

Refresh Credential 

The elastic job agent needs one credential that provides enough access to all servers within the target group to enumerate the databases there.  In some examples you may see, this credential is called “mastercred”.  However, I am calling it the Refresh Credential because when setting this value for the elastic job in PowerShell, the argument to set the credential on the job is called RefreshCredentialName.    For all target servers in the groups being targeted by the job, this credential should have view permissions  in the master database (sufficient to look at the contents of the system view sys.databases), as it is used to enumerate all databases in the server. 

Job Credential 

Each job step operates within a database using its own credential, provided when you define the job step.  A single job, comprised of different steps, can use a different credential for each step.  It is important, though, to make sure that the credential for the step satisfies both of the following:  

1. The credential has appropriate permissions for the operation being performed, and  


2. The credential exists on all target databases in the group with those permissions. 

Creating Credentials

Each of these credential types needs to be created on both the target (against which the jobs will run) and on the database hosting the Elastic Jobs Agent. I discuss each separately here.

On the targets – Refresh Credential:

Each of the two types of credentials can be created on the targets via T-SQL; this is not an action specific to Elastic Jobs, and so there is no special PowerShell support.   You must create the refresh credential in the target’s master db. Connect to master and issue the following:

CREATE LOGIN refreshcredential WITH PASSWORD=’strongpassword’

CREATE USER refreshcredential FROM LOGIN refreshcredential

On the targets – Job Credential:

The best way is to connect to master and create the login, and then to create the user on each individual DB you wish to include as a target of the elastic job.

So, in master, you would issue:

CREATE LOGIN jobcredential WITH PASSWORD=’alsostrongpassword’

Then, on EACH of the target databases, you need to connect and issue the following:

CREATE USER jobcredential FROM LOGIN jobcredential

On the Elastic Job Agent host database – both types of credentials:

Having credentials on the target is the first step, next you need to add these same credentials to the database in which you have created the Elastic Job Agent. For this step , you issue commands ONLY in the database where the Elastic Job Agent is hosted (we will call this the host or job database). These connections are to the specific database, and not master or the server generally. As a best practice, the Elastic Job Agent and the target databases would be located on different servers. Separating the target from the host of the jobs being targeted is a good practice for maintenance and billing. It also prevents the jobs from inadvertently targeting the Elastic Jobs Agent host database itself.

There are two ways to create the credentials on the host database. This can be done via T-SQL, but the preferred way to do this is to create the credentials using PowerShell in the Azure Command Shell because those credentials are seen as “first class citizens” from the perspective of the Elastic Job Agent, as the agent is the principal associated with the credential and has full permissions to view and modify/delete it.  If the credentials are created in T-SQL, you will still be able to use them, but you won’t be able to delete or modify them through the agent. Another way of looking at this is that the T-SQL credentials are more general and not created in a scope limited to the Elastic Jobs Agent, while the credentials created via the Elastic Job Agent via PowerShell are associated with the appropriate principal and database role. T-SQL created credentials are associated with the principal from which you create the user in T-SQL (so, if you connected to the database and created the credentials via your admin account, they would be associated with the dbo principal). This scope is larger than necessary for the task of connecting to the target database to execute jobs. Thus, for security and maintenance purposes, creating the credentials using PowerShell is preferred.

Using T-SQL (less preferred):

Connect to the job host database, issue the following:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘password’

CREATE DATABASE SCOPED CREDENTIAL refreshcredential

WITH IDENTITY = ‘refreshcredential’,

SECRET = ‘password1’

CREATE DATABASE SCOPED CREDENTIAL jobcredential

WITH IDENTITY = ‘jobcredential’,

SECRET = ‘password2’

Using Azure Cloud Shell (preferred method):

Open the Azure Cloud Shell by clicking on this icon on the top right/middle of the screen: 

At this point, you will have to already have created the Elastic Jobs Agent. I am not going to explain that process in this post (it will come in more detail when I do a full walkthrough).  For now, assume we have created it already, and we can do the following to bind the agent so that we can use it for creating the credentials:

:\> $db = Get-azsqldatabase -ServerName ElasticJobsAgenthostServer -DatabaseName ElasticJobsAgentHostDB 

:\> $server = $db | Get-AzSqlServer

:\> $jobAgent = $server | Get-AzSqlElasticJobAgent

To create the credentials, execute the following commands:

:\> $loginPasswordSecure1 = (ConvertTo-SecureString -String “strongPWD1” -AsPlainText -Force)

:\> $loginPasswordSecure2 = (ConvertTo-SecureString -String “strongPWD2” -AsPlainText -Force)

:\> $refreshCred = New-Object -TypeName “System.Management.Automation.PSCredential” -ArgumentList “refreshcredential”, $loginPasswordSecure1

:\> $refreshCred = $jobAgent | New-AzSqlElasticJobCredential -Name “refreshcredential ” -Credential $masterCred

:\> $jobCred = New-Object -TypeName “System.Management.Automation.PSCredential” -ArgumentList “jobcredential”, $loginPasswordSecure2

:\> $jobCred = $jobAgent | New-AzSqlElasticJobCredential -Name “jobcredential” -Credential $jobCred

You can check on the status of these new credentials in two ways.  One is by querying them in the host database:

SELECT * FROM sys.database_scoped_credentials

the other is via Azure Cloud Shell by issuing the following:

:\> $jobAgent | Get-AzSqlElasticJobCredential

At this point, you should understand the why and how of creating appropriate credentials for Elastic Jobs, and feel prepared to create and test the idempotence of any scripts you would want to run via Elastic Jobs.

This blog is part of a series about Elastic Jobs on Azure SQL Database.

1. Elastic Jobs in Azure SQL Database – What and Why


2. Fundamental Concepts for Elastic Jobs in Azure SQL Database


3. Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database in PowerShell


4. Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database in T-SQL


5. Running, Scheduling and Monitoring Elastic Jobs in Azure SQL Database


6. Troubleshooting Common issues with Elastic Jobs in Azure SQL Database