Azure SQL Elastic Database Job Agents Configuration

Introduction

As Database administrator we know that SQL jobs managed by SQL Server agent services, but this concept on SQL Server on-premises, in Microsoft cloud we have another something called Azure SQL Elastic job. Before starting in the definition of it I highly recommend you to understand first what is Azure database deployment model options and How we can configure Azure single database, Azure elastic Pool database and Azure Database Managed Instance Check Below Articles 👇

• Azure Database Deployment model options
• Azure Single Database Fundamental
• Azure SQL Database Elastic Pool Fundamental
• Azure SQL Database managed instance Fundamental

In this article of today I will cover below points and these points from my view are the basic points still we have many others points in this subject and I will cover it in the upcoming posts, Keep Following me and if you are interested to learn more about Azure SQL Database check this link and if you are interested to prepare for Azure administrator certification AZ-103 or AZ-104 check this link

• What is the Azure SQL Elastic Job?
• Differences between Azure SQL Database Jobs and SQL Agent
• Azure SQL Elastic Database jobs Component
• Configuration Prerequisites
• Azure SQL Elastic Job Full Configuration (8 Steps)
• Review the Elastic Job Configuration
• References

What is the Azure SQL Elastic Job?

Azure SQL Elastic Job is Microsoft Azure service used for managing and monitoring SQL Custom jobs on Azure Single database and Azure SQL Elastic Pool, For Azure managed instance this feature not supported it because we already have SQL Server Agent on Azure Managed instance for more information about How to automate tasks using Database jobs check this link

Differences between Azure SQL Database Jobs and SQL Agent

• Azure SQL Agent used to execute custom jobs on one database or multiple databases on the same SQL Server instance and we can configure it using T-SQL or SQL Server management studio SSMS and this supported by Azure Managed instance
• Azure SQL Elastic Database Jobs used to execute custom jobs in different database Servers, Subscription or Rejoins and it can target Single database, Elastic Pool Database or Data Warehouse and to configure it we can use Azure Portal, PowerShell, T-SQL or Azure Resource Manager and this service not supported by Azure managed instance.

For more information check this link

Azure SQL Elastic Database jobs Component

• Job Agent: Is Azure resource used for creating and managing the jobs and it is required existing free SQL database Called Job Database and this Job Database used to store the jobs definition and the history of the jobs like our normal jobs in on-premises SQL Server the history and the configuration of the SQL jobs saved in system tables in MSDB database. Also inside the Job database, we will have default Stored procedure can be used for collecting information about the job definition and history, the recommended Service Tier for Azure Job Database is S1 or Higher.
• Target Group: and in this Target group we will define the target servers or the target databases that we need to execute this jobs on it and we can add in the target group (Azure Single database logical server or database, Azure SQL Database Elastic pool logical server or Elastic Pool Database, SQL Server database and Shardmap database)
• Job Output: and this used to store the Outcome of the job execution and this information saved in the table on the existing database or a new database and this time this Database called Output database.

Configuration Prerequisites

In the article, I do my demo and configuration on Azure Single Database and if you will follow the same steps I do it you should configure two things

• Job Agent Server: Normal Azure Single database logical server with a new empty database to be used for Azure SQL Elastic job agent configuration
• Job Agent server information: Logical Server (jobagentlogicalserver), Single Database Name (jobagentdatabase)
• Target Server: The server you need to execute the jobs on it, in my demo I created new Azure single database logical server with a new empty server
• Target Server Information: Logical Server (targetlogicalserver), Single Database name (targetazuresqldb)

During the Configuration for Agent database and Target database we will select the services tier as Stander DTU S1 as we mentioned before it is recommended add in your note that you can configure this Agent database on vCore-based Purchase model, also you need to enable Server Firewall by adding your local IP address to be able to connect to the new servers through the SQL Server management studio SSMS on your local machine Check this article for How to configure azure server firewall.

For more information about How to Created Azure Single Database Check this Article

Azure SQL Elastic Job Full Configuration

Now we will start the real configuration of Azure SQL Elastic job and this demo I will cover the below points:

1. Create Elastic Job Agents Using Azure Portal
2. Create Credentials on Agent database Using (T-SQL)
3. Create Target Group members on Agent Database Using (T-SQL)
4. Add Target Member Using (T-SQL)
5. Verify Target Group Member Configuration Using (T-SQL)
6. Create Logins on Target Database Using (T-SQL)
7. Create Job on Agent Database Using (T-SQL)
8. Execute the Job and Check the results Using (T-SQL)

Create Elastic Job Agents Using Azure Portal

I will Create “Elastic Job agents” on the Job Agent Server “jobagentlogicalserver” by selecting the DB “jobagentdatabase” to be used as Agent Job. To do this step follow the below actions

• Open Azure Portal and write on the search “ Elastic Job agents” or you can click on this link to target you to the  Elastic Job agents
• Click on Add to add new Elastic job Agent for example “azurelelasticjobagentdemo1”
• Name: You need to Provide the Name of the Elastic Job agents
• Job Database: in the Part, you will select the Job database logical server we created it “jobagentlogicalserver”

• After this, the database Agent “jobagentdatabase” will appear to select it
• Now if you do refresh on Elastic Job Agent Services you will find the new Agent, we created it

• After the Elastic Job agents created If you do refresh now for the Agent database “jobagentdatabase” you will find some new tables and Stored Procedures created

Create credentials on Agent database

Now I will create the credential account that will be used to connect to the target server and Execute the custom job on the target server

• Connect to Agent Server “jobagentlogicalserver.database.windows.net” Using SSMS and open new query on agent database “jobagentdatabase”
• Create master Key Encryption
• Crate CREDENTIAL on Agent database “jobagentdatabase” and this will be used to execute the job on Target database
• Create second CREDENTIAL Agent database “jobagentdatabase” and this will be used to connect to the Master database on target Server “targetlogicalserver”

CREATE MASTER KEY ENCRYPTION BY PASSWORD='Elmasrydemo!@#'; 
GO
CREATE DATABASE SCOPED CREDENTIAL ElmasryjobScopcred 
WITH IDENTITY = 'Elmasryjobcred',
SECRET = 'Elmasrydemo!@#'; 
GO
CREATE DATABASE SCOPED CREDENTIAL ElmasrymasterScopcred 
WITH IDENTITY = 'Elmasrymastercred',
SECRET = 'Elmasrydemo!@#'; 
GO

Verify the Credentials

SELECT * FROM sys.database_scoped_credentials

Create Target Group Members on Agent Database

In this part, we will create a target group that will contain all of the jobs will be executed on the target database Using (jobs.sp_add_target_group)

EXEC jobs.sp_add_target_group 'ElmasryServerTargetGroup1'

Add Target Member

This step I will add the Target server as a member of the Target Group Using (jobs.sp_add_target_group_member), In this script, we will need to Provide the Target group Name “’ElmasryServerTargetGroup1‘” the Second Credential we created it to be used to connect to the Master database on the Target server “ElmasrymasterScopcred” and finally we need to provide the Target Server name “targetlogicalserver.database.windows.net”

EXEC jobs.sp_add_target_group_member
'ElmasryServerTargetGroup1',
@target_type =  N'SqlServer',
@refresh_credential_name='ElmasrymasterScopcred',
@server_name='targetlogicalserver.database.windows.net'
GO

Verify Target Group Member Configuration

By Using below Two T-SQL Statement we can check if the azure SQL Database contains configuration for Azure Elastic Job Agent

SELECT * FROM jobs.target_groups
GO
SELECT * FROM jobs.target_group_members

Create Logins on Target Database Using (T-SQL)

In this step, we will create Logins on Target Master Database and Target Database for both credentials we created it on Agent Database

• Create Logins on target Server on Master Database

CREATE LOGIN Elmasrymastercred WITH PASSWORD='Elmasrydemo!@#'
CREATE USER Elmasrymastercred FROM LOGIN Elmasrymastercred
CREATE LOGIN Elmasryjobcred WITH PASSWORD='Elmasrydemo!@#'

• Create Logins on Target Server on target Database “targetazuresqldb”

CREATE USER Elmasryjobcred FROM LOGIN Elmasryjobcred
ALTER ROLE db_owner ADD MEMBER [Elmasryjobcred] ;
Grant Execute to [Elmasryjobcred]
GO
SELECT * FROM Sys.sysusers where Name = 'Elmasryjobcred'

Create Job on Agent Database Using (T-SQL)

In this step, I will create a new Job to Create a new table on the target database in case this table is not Exists and to insert a new record on the table

• First Command we will create the Empty Job only name
• The second Command we will add the first Step to this Job: this Step will do create for an empty table in the target database
• Third command we will add the second step to the same job to insert new record to this table after creating it

EXEC jobs.sp_add_job @job_name='SQLElasticJobAgentdemojob'
GO
EXEC jobs.sp_add_jobstep @job_name='SQLElasticJobAgentdemojob',
@command=N'IF NOT EXISTS (SELECT * FROM sys.tables 
           	WHERE object_id = object_id(''demotable''))
CREATE TABLE [dbo].[demotable](
[demoid] [int] primary key identity(1,1) NOT NULL,
demoname nvarchar(100),
demotime datetime default Getdate()
);',
@credential_name='ElmasryjobScopcred',
@target_group_name='ElmasryServerTargetGroup1'
GO
EXEC jobs.sp_add_jobstep
@job_name='SQLElasticJobAgentdemojob',
@step_name = 'add Value',
@command=N'
insert into [demotable]
(demoname)
values 
(''Mustafa_Elmasry_Demo'')
',
@credential_name='ElmasryjobScopcred',
@target_group_name='ElmasryServerTargetGroup1'
GO

This is not the end of the configuration you can do many things for more information check Microsoft documentation also if you need to check and verify the jobs configuration and How many jobs running on your server easily you can check the deflate tables created on the Agent database

Execute and test the Job

In the step I will Execute the Job manually because I didn’t do any schedule for it because it is a demo so to Execute job manually you can do it by below the first command and the second command will share with you the status of the job

EXEC jobs.sp_start_job 'SQLElasticJobAgentdemojob'
GO
SELECT * FROM jobs.job_executions 
WHERE is_active = 1 AND job_name = 'SQLElasticJobAgentdemojob'
ORDER BY start_time DESC
GO

After Executing the Job, I do refresh for my target database I found the new table created and the first record inserted on , check below GIF File be clicking on download button you will be able to check online How the Job Executed and How is created and inserted new record easily

Review the Elastic Job Configuration

IF you need to check this configuration and check the log of the execution jobs you can open the Elastic job Agent, we created it and from this place, you can know the information about (Jobs, target Group, Credentials, Job Executions)

References

• https://docs.microsoft.com/en-us/azure/sql-database/elastic-jobs-overview
• https://docs.microsoft.com/en-us/azure/sql-database/elastic-jobs-tsql
• https://techcommunity.microsoft.com/t5/azure-sql-database/elastic-jobs-in-azure-sql-database-what-and-why/ba-p/1177902

Keep Following

• WordPress
• Facebook
• Twitter
• LinkedIn
• Link
• Link