| Elastic Jobs are currently in public preview. The feature is ready and we are preparing to make it officially generally available. No extra steps are required to get started. |
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:
Elastic jobs are processes hosted on the Elastic Jobs Agent Host, and yet they are running against databases and servers in the defined target groups (we call these the targets). In order to do this, the Elastic Jobs agent has to have some way to authenticate to the target so that it can execute commands. Any credentials you would need to execute on the target must also be created in the Elastic Jobs Agent Host Database. We create credentials on the host because that is the securest way to store them for use later.
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 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.
| Note: If your target group is defined as just databases (and no servers), you do not need the refresh credential. The refresh credential is used only to enumerate databases on servers in the target group. |
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:
- The credential has appropriate permissions for the operation being performed, and
- 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 $refreshCred
:\> $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.
Code samples are also available on GitHub.
- Elastic Jobs in Azure SQL Database – What and Why
- Fundamental Concepts for Elastic Jobs in Azure SQL Database
- Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database in PowerShell
- Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database in T-SQL
- Running, Scheduling and Monitoring Elastic Jobs in Azure SQL Database
- Troubleshooting Common issues with Elastic Jobs in Azure SQL Database