Create, configure, and manage elastic jobs
In this article, you will learn how to create, configure, and manage elastic jobs. If you have not used Elastic jobs, learn more about the job automation concepts in Azure SQL Database.
Create and configure the agent
Create or identify an empty S0 or higher SQL database. This database will be used as the Job database during Elastic Job agent creation.
Create, run, and manage jobs
Create a job agent credential in each database the job will run (add the user (or role) to each database in the group). For an example, see the PowerShell tutorial.
Credentials for running jobs
Jobs use database scoped credentials to connect to the databases specified by the target group upon execution. If a target group contains servers or pools, these database scoped credentials are used to connect to the master database to enumerate the available databases.
Setting up the proper credentials to run a job can be a little confusing, so keep the following points in mind:
- 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 (
jobuserin the diagram below).
- Credentials can be reused across jobs, and the credential passwords are encrypted and secured from users who have read-only access to job objects.
The following image is designed to assist in understanding and setting up the proper job credentials. Remember to create the user in every database (all target user dbs) the job needs to run.
Security best practices
A few best practice considerations for working with Elastic Jobs:
- Limit usage of the APIs to trusted individuals.
- Credentials should have the least privileges necessary to perform the job step. For more information, see Authorization and Permissions SQL Server.
- When using a server and/or pool target group member, it is highly suggested to create a separate credential with rights on the master database to view/list databases that is used to expand the database lists of the server(s) and/or pool(s) prior to the job execution.
Agent performance, capacity, and limitations
Elastic Jobs use minimal compute resources while waiting for long-running jobs to complete.
Depending on the size of the target group of databases and the desired execution time for a job (number of concurrent workers), the agent requires different amounts of compute and performance of the Job database (the more targets and the higher number of jobs, the higher the amount of compute required).
Currently, the preview is limited to 100 concurrent jobs.
Prevent jobs from reducing target database performance
To ensure resources aren't overburdened when running jobs against databases in a SQL elastic pool, jobs can be configured to limit the number of databases a job can run against at the same time.
Set the number of concurrent databases a job runs on by setting the
sp_add_jobstep stored procedure's
@max_parallelism parameter in T-SQL, or
Add-AzSqlElasticJobStep -MaxParallelism in PowerShell.
Best practices for creating jobs
A job's T-SQL scripts must be idempotent. Idempotent means that if the script succeeds, and it is run again, the same result occurs. A script may fail due to transient network issues. In that case, the job will automatically retry running the script a preset number of times before desisting. An idempotent script has the same result even if its been successfully run twice (or more).
A simple tactic is to test for the existence of an object before creating it.
IF NOT EXIST (some_object) -- Create the object -- If it exists, drop the object before recreating it.
Similarly, a script must be able to execute successfully by logically testing for and countering any conditions it finds.