This feature allows SQL Server agent-like functionality in Azure SQL Database. It provides the ability to define and execute tasks across one or many databases, which could be especially helpful while managing multiple databases in Azure with repetitive tasks needing to be performed regularly.
What are Elastic Jobs?
Elastic Jobs are a service in Azure SQL Database that executes tasks that might influence more than one database, such as schema management, credentials management, reference data management, performance testing, and query optimization. Elastic jobs are frequently employed to execute administrative duties and standard maintenance operations across a group of databases.
How to create Elastic Jobs?
There are two main components that we need to create first, a Job database and an Elastic Job Agent. A Job database stores jobs’ metadata, history, and logs for all jobs executed by an Elastic Job agent. The Job Agent actually handles the execution of jobs.
To create a Job database, we follow the below steps:
- Head over to Azure Portal
- Click on SQL Databases and click “Add”
- Fill up the appropriate fields such as Database name, subscription, resource group Server and pricing tier. Click “Review + Create” and then click “Create”
Post the creation of Job database, we create an Elastic Job agent.
- Navigate to the newly created job database from Azure Portal
- Select Elastic Job agent from the left-side menu and click “Create”
- Fill up the Agent name and leveraging existing job database created in the above step
Configuring Elastic Jobs
There are three main components involved while configuring elastic jobs, namely, Job Credentials, Job Target Group, and Job that we need to create.
- Job Credentials: These are used by the job step when connecting to the target databases.
- Job Target Group: This is a logical grouping of Azure SQL databases that can be targeted by a job.
- Job: It contains Job Step, which represents the T-SQL script to be executed by the job.
For example, to create Job Credentials using Azure SQL Database:
EXEC jobs.sp_add_target_group 'target_group';
GO
EXEC jobs.sp_add_job 'job1';
GO
EXEC jobs.sp_add_jobstep 'job1', 'step1', @target_group_name='target_group',
@command=N'SELECT COUNT(*) FROM sys.tables',
@credential_name = 'job1credential';
GO
EXEC jobs.sp_start_job 'job1';
In this example, we register a target (our server), declare a new job and create a job step which runs a command against our target group. Along with this, we run the credential named ‘job1credential’. Finally, we start the job with the given name.
It is possible to check the current status of running jobs using a T-SQL query:
SELECT job_id, job_name, job_description, start_time, end_time, status
FROM jobs.job_executions
ORDER BY start_time DESC;
In conclusion, Elastic Jobs in Azure provide a powerful tool to manage, control and automate work across multiple databases. Through Job database and Elastic Job Agent, tasks can be flexibly scheduled and managed in a centralized manner. The ability to define tasks via Job Credentials, Job Target Group, and Job adds to the versatility of Elastic Jobs, thereby aiding administrators to effectively manage databases in Azure with repetitive tasks needing to be performed regularly.
Practice Test
True or False: Elastic jobs can be created and configured via the Azure portal.
- True
- False
Answer: False
Explanation: Elastic jobs cannot be created and configured through the Azure portal, they are created and managed programmatically, typically through T-SQL or PowerShell scripts.
Which of the following permissions are required for an Azure SQL Elastic Job account to communicate with target logical servers? (Select all that apply)
- A. CONTROL permissions
- B. CREATE DATABASE permissions
- C. SERVER STATE permissions
- D. VIEW DATABASE STATE permissions
Answer: A, D
Explanation: The job account requires CONTROL permissions at an Azure SQL server level and VIEW DATABASE STATE permissions at the database level to communicate with target logical servers.
True or False: Elastic jobs utilize T-SQL scripts for execution against one or more databases?
- True
- False
Answer: True
Explanation: Elastic jobs in Azure run T-SQL scripts against one or more databases that are within the target group.
In elastic jobs, which of the following is TRUE about custom retry policies?
- A. They allow specifying the number of retry attempts for a job step.
- B. They enable specifying the maximum time duration for a job step.
- C. They can only be configured for a single step.
- D. They cannot specify the interval between retries.
Answer: A
Explanation: Elastic jobs provide custom retry policies that allow you to specify how many retry attempts should be made for a job step.
Where do you configure an Elastic Job?
- A. In the Azure portal
- B. On the Logical SQL Server
- C. On the Job Database
Answer: C
Explanation: You configure and manage Elastic Jobs in the Job Database on Managed Instance or Azure SQL server.
Which of the following statements are true regarding Azure Elastic jobs? (Select all that apply)
- A. They only target Azure SQL Database.
- B. They target any valid T-SQL endpoint.
- C. They’re created and managed using Azure portal.
- D. They’re designed to perform administrative operations.
Answer: B, D
Explanation: Azure Elastic jobs can perform administrative operations and tasks across multiple databases, and they target any valid T-SQL endpoint, not just Azure SQL Database.
What is the purpose of Invocation Credentials for Elastic Jobs?
- A. They specify which databases the job will target.
- B. These are the credentials used when running the job steps.
- C. They determine the time that the job will be run.
- D. These are the credentials used for creating Elastic Jobs.
Answer: B
Explanation: Invocation Credentials are used for authentication when running the job steps.
When do elastic jobs start automatically?
- A. After being created
- B. When enabled
- C. After the target group is created
- D. Never
Answer: B
Explanation: Elastic jobs start automatically when they are enabled.
True or False: Recoverability of Elastic Jobs can be handled by Azure SQL Database automatic backups.
- True
- False
Answer: False
Explanation: Elastic Jobs are not recoverable from Azure SQL Database automatic backups; they are recovered from the Job database.
Which PowerShell cmdlet is used to create a new elastic job?
- A. New-AzureRmSqlElasticJob
- B. New-SqlElasticJob
- C. Create-SqlElasticJob
- D. New-AzSqlElasticJob
Answer: D
Explanation: The New-AzSqlElasticJob command is used in PowerShell to create a new Azure SQL Elastic Job.
Interview Questions
What prerequisites must be met to create elastic jobs?
To create elastic jobs for an Azure SQL database, you have two prerequisites: Firstly, you must use PowerShell, REST API, or Transact-SQL. Secondly, you also need an Azure SQL Database server that will host the job database.
What type of account is required to create an elastic job agent?
An Azure Active Directory account is required to create an elastic job agent.
What role must be assigned to an Azure Active Directory account to create and manage Elastic Jobs?
The AAD account needs the SQL DB Contributor role to create and manage Elastic Jobs.
What PowerShell cmdlet is used to create a new elastic job agent?
The New-AzSqlElasticJobAgent cmdlet is used to create a new elastic job agent.
What are the steps to configure Elastic Jobs using the Azure portal?
The Azure portal does not support creating or managing Elastic Jobs. You need to use PowerShell, the REST API, or Transact-SQL to configure Elastic Jobs.
What are targets in the context of elastic jobs?
Targets in elastic jobs are databases that the job will execute against. They can be specified explicitly or dynamically.
Is it possible to use the master database as a job database for elastic jobs in Azure SQL Database?
No, the master database can’t be used as a job database for elastic jobs in Azure SQL Database.
Can you filter databases selected in an elastic job using tags?
Yes, when defining the target group in an elastic job, databases can be filtered by tags.
What is the maximum execution time for an individual job step in an elastic job?
The maximum execution time for an individual job step is 1 hour.
How to debug the failure of an Elastic Job in Azure SQL Database?
Elastic job history and error messages can be queried to debug a failed Elastic Job.
What is the purpose of a Refresh credential in Azure SQL Elastic Job?
A Refresh credential is used by job agent to refresh the list of databases within a target elastic pool or shard map.
Can Elastic Jobs perform operations across multiple Azure SQL Databases?
Yes, Elastic Jobs are designed to perform administrative operations and run custom jobs across multiple Azure SQL Databases.
Do Elastic Jobs support idempotent actions?
Yes, Elastic Jobs support idempotent actions which means that repeated execution of the same job leads to the same state in the database.
What is the limitation of Elastic Job’s level of parallelism?
Elastic Jobs can run against a maximum of 100 databases concurrently.
Can I specify the frequency for an Elastic Job in Azure SQL Database?
Yes, you can specify the frequency of an Elastic Job via schedules which can be set up for the job to run one time, continuously, or on a recurring schedule.
extutorials.com