Introduction
Speed is of the essence in today’s world, and efficiency as well as automation are some of the most vital aspects to keep up with. With growing dependence on data-driven insight and operations by businesses, there has never been a more critical time for robust scalable solutions that manage SQL jobs. Azure Elastic Job Agent is such a solution built to automate and simplify SQL job management in the Azure environment.
With Azure Elastic Job Agent, your SQL activities will be executed reliably and efficiently through easy creation, scheduling and administration of these tasks across multiple databases. Whether you need to undertake routine maintenance, complex data transformations or create regular reports, Azure Elastic Job Agent can effectively help streamline all such workflows.
In this blog post we will deeply analyze Azure Elastic Job Agent’s features and capabilities indicating its installation procedure and how it is being used. We shall have a look at stepwise instructions for creating your first elastic job, authenticating using User Assigned Identity (UMI), configuring private endpoints among other topics. Once you are done with this guide you would easily exploit Azure Elastic Job Agent fully to enhance your data operations hence increasing business processes efficiency.
Create Your First Elastic Job
As mentioned in our video, you have the flexibility to create your jobs either through the Azure portal or using T-SQL. In this section, we’ll delve into the step-by-step process of creating jobs using T-SQL. If you’re interested in learning how to achieve this via the Azure portal, be sure to check out the video linked below.
Create Target Groups :
Adding target group can be done from the portal or using T-SQL .
| Target Group Type | Description |
| Logical SQL Server | All databases existing in the specified logical server at the time of job execution are included in the group. Requires master database credentials for enumeration and updating. |
| Elastic Pool | All databases within the specified elastic pool at the time of job execution are included in the group. Requires master database credentials for updating. |
| Single Database | One or more individual databases can be specified to comprise the group. |
Adding target group using the portal:
When adding a target group, we can add a server, a database from server or even a pool.
Using T-SQL too we can add target groups :
-- Connect to the job database specified when creating the job agent -- Add a target group containing server(s) EXEC jobs.sp_add_target_group 'ServerGroup1'; -- Add a server target member EXEC jobs.sp_add_target_group_member @target_group_name = 'ServerGroup1', @target_type = 'SqlServer', @server_name = 'servername.database.windows.net'; @database_name = 'dbname'; --View the recently created target group and target group members SELECT * FROM jobs.target_groups WHERE target_group_name='ServerGroup1'; SELECT * FROM jobs.target_group_members WHERE target_group_name='ServerGroup1';
Create Sample Job
The jobs can be configured to run once or recurring. (using T-SQL)
-- Create a new job named 'job01' with a description
EXEC jobs.sp_add_job
@job_name = 'job01',
@description = 'execute the procedure InsertOrUpdateInternalData';
-- Add the first step to the job 'job01'
EXEC jobs.sp_add_jobstep
@job_name = 'job01',
@command = N'EXEC InsertOrUpdateInternalData;', -- Command to execute the procedure
@target_group_name = 'group01'; -- Target group where the job will be executed
Using the portal :
When creating a job, we should give it a name and choose if it is disabled or enabled, and we choose if it will be running once or recurring.
The jobs can be configured to run once or recurring.
We can also configure for a job the maximum retries times and timeout.
as you can see in the below picture, a new step has been added, a job can have one or more steps.
Create Recurring Job
From portal
Using T-SQL
-- Add a job to run every 60 min EXEC jobs.sp_add_job @job_name = 'reccuringjob01', @description = 'exec job every 60 min', @schedule_interval_type = 'Minutes', @schedule_interval_count = 60;
Authentication via User Assigned Identity (UMI)
Authentication via User Assigned Identity (UMI) allows for secure access to Azure resources by assigning a unique identity to your Azure services. This approach enhances security by eliminating the need to manage credentials directly, leveraging Azure Active Directory for seamless authentication and authorization.
The first step is to create a user assigned identity (no role assignments to be added)
-- Create a user named accesdbidentity, which authenticates using an external identity provider. CREATE USER accesdbidentity FROM EXTERNAL PROVIDER; -- Grant the permission to view database performance state to the accesdbidentity user. GRANT VIEW DATABASE PERFORMANCE STATE TO accesdbidentity; -- Grant the permission to select data from all tables within the dbo schema to the accesdbidentity user. GRANT SELECT ON SCHEMA::dbo TO accesdbidentity; -- Grant the permission to execute stored procedures within the dbo schema to the accesdbidentity user. GRANT EXEC ON SCHEMA::dbo TO accesdbidentity; -- Grant the permission to create tables within the dbo schema to the accesdbidentity user. GRANT CREATE TABLE TO accesdbidentity; -- Grant the permission to alter objects within the dbo schema to the accesdbidentity user. GRANT ALTER ON SCHEMA::dbo TO accesdbidentity;
private endpoint
Each target server can be reached via a service-managed private endpoint, created and managed by Microsoft, and exclusively for use with elastic jobs. Creating an elastic jobs private endpoint establishes a private link between the elastic job and the target server. Once configured, all communication between the elastic job agent and the target server will occur through the private endpoint.
PS : The connection from the elastic job agent to the jobs database will not use private endpoint. The job agent itself uses internal certificate-based authentication to connect to its jobs database. One caveat being if you add the jobs database as a target group member. Then it behaves as a regular target that you would need to setup with private endpoint as needed.
job steps
in a job we can have one or more steps, to add a new step we need to click on add step and follow the instructions:
As you can see in the pictures , we have now a job with two steps.
Job Output
When a job is executed, we can save data as output in a table, the below sample show how the performance data are collected from tables inside the ‘pertable’ table that was created in job database
Sample T-SQL
--Connect to the job database specified when creating the job agent -- Add a job to collect perf results EXEC jobs.sp_add_job @job_name ='ResultsJobforper', @description='Collection Performance data from all customers db' -- Add a job step w/ schedule to collect results EXEC jobs.sp_add_jobstep @job_name = 'ResultsJobforper', @command = N' SELECT DB_NAME() DatabaseName, $(job_execution_id) AS job_execution_id, * FROM sys.dm_db_resource_stats WHERE end_time > DATEADD(mi, -20, GETDATE());', @target_group_name = 'sqldb-target-db-poc-001', @output_type = 'SqlDatabase', @output_server_name = 'ej-server-poc-001.database.windows.net', @output_database_name = 'ej-db-poc-001', @output_table_name = 'perftable';
Monitor job execution status
To monitor the job execution, there is two ways, the first one is through the portal which is not very detailed (only status about executions) and the second way is using the T-SQL and look for the results In the job table as in the pictures below:
--Connect to the job database specified when creating the job agent -- View all jobs SELECT * FROM jobs.jobs; -- View the steps of the current version of all jobs SELECT js.* FROM jobs.jobsteps js JOIN jobs.jobs j ON j.job_id = js.job_id AND j.job_version = js.job_version; -- View the steps of all versions of all jobs SELECT * FROM jobs.jobsteps;
Pricing
Till this moment when I’m creating this file, the price of the service does not exist in the official Microsoft page, while creating the service we can only see the price of the server and the database dedicated to the elastic job agent.
The price of the Elastic jobs depends of the choice of the JA .
