Introduction:

In this article, we will explore the concept of Elastic Jobs in Azure SQL Database. Elastic Jobs allow you to run scheduled tasks in multiple Azure SQL Servers and Databases simultaneously. This feature is similar to SQL Server Agent in On-prem SQL Server versions, but with added advantages and capabilities.

Benefits of Elastic Jobs:

• Execute scheduled tasks in multiple Azure SQL Servers and Databases
• Perform parallel execution of tasks
• Supports all Azure SQL Database offerings
• Can be used for capturing server information or executing maintenance tasks

Scenario:

Let’s consider a scenario where we have a distributed database or sharding environment. In this environment, databases are distributed among multiple servers and we need to execute tasks only in the relevant sharded databases.

Creating Azure SQL Databases:

To begin, we need to create the necessary Azure SQL Databases and servers. Once created, we can enable the Elastic Job Agent for the master shard database. This will create the required tables, views, and stored procedures related to the jobs in the selected database.

Creating Credentials and Groups:

Next, we need to create credentials for connecting to the shard server and executing the job in each server. We also need to create a group and add the relevant members to it. The group represents the databases where the tasks will be executed.

Creating the Job:

Now, we can create the job itself. Similar to SQL Server Agent jobs, we need to create the job and its steps. We can specify the tasks to be performed, such as creating a table in all databases in the shards. The job will execute under a specific credential, so we need to create the necessary login and user in each database.

Scheduling the Job:

Finally, we can schedule the job to execute at specific intervals. We can specify the frequency and timing of the job execution, such as every minute or every hour.

Conclusion:

Elastic Jobs in Azure SQL Database provide a powerful and scalable solution for executing tasks in distributed databases. With the ability to run tasks in parallel and support for various Azure SQL Database offerings, Elastic Jobs offer a flexible and efficient way to manage and automate tasks in your Azure SQL environment.