Dinesh Asanka

Elastic Jobs in Azure SQL Database

February 18, 2021 by

Introduction

I have dedicated this article to the topic of Elastic Jobs in Azure SQL Database which is in Public Review. This feature allows you to run scheduled tasks in your Azure SQL Databases. This is similar to SQL Server Agent you have in the On-prem SQL Server versions. However, in Elastic Jobs, you can execute the scheduled tasks in multiple Azure SQL Servers and multiple Databases which is an added advantage when considering the features of SQL Server Agent. Further, this execution performs parallelly.

This type of Elastic Jobs can be used to capture information on servers such as wait stats, index stats, and a number of users or execute maintenance tasks such as Partition Maintenance, Index Rebuild, Statistics Update, or data update for multiple databases or schema update for multiple databases. Like in SQL Server Agent jobs, Elastic Jobs can be scheduled as well. This shows that Elastic Jobs in Azure SQL Database can be used in distributed databases heavily.

This feature supports all the options of Azure SQL Database offerings such as General Purpose, Basic, Hyper-Scale, Serverless, Elastic pools, etc. However, to enable Elastic Agent Job you need at least Standard S0 level which has at least DTU 10. It is important to remember that the Elastic Job feature will not be available for Azure SQL Managed Instance.

Explanation using a Scenario

Let us select a scenario to explain Elastic Jobs in Azure SQL Database. In the case of distributed databases or sharding databases, let us look at some database distribution as follows:

Enabling Elastic Jobs in Azure SQL Databases in the selected senario

As shown in the above figure, a typical database sharding environment will contain a master shard that decides the method to connect to its member shards. In the above environment, Sharding databases are distributed among three database servers and sharded databases are shown in blue color. There are other databases in these servers which are not logically part of these sharding. This distribution of databases means that your tasks should execute only in the relevant sharded databases. In the above example, all databases in shard 1 to be executed meanwhile one database should be excluded from the execution in shard 2, meanwhile, only one database should be selected from the third shard.

Creating Azure SQL Databases

Let us create 10 databases in four servers and the Master shard should be at least in the capacity of Standard S0. If you need to know the details of how to create an Azure SQL Database, please refer to the Creating your first Azure SQL Database article.

All ten databases were created as shown in the Azure portal below.

Azure SQL Databases in multiple servers including the Master Shard.

When necessary firewalls are enabled, you can connect to all the servers and databases from the SSMS as shown below.

Connecting to Azure SQL Databases from SSMS.

Now we are ready with the environment to Create Elastic Jobs in Azure SQL Databases. First of all, let us enable Elastic Job Agent for the master shard database as shown below.

Enabling Elastic Job Agent in Azure SQL Database.

Once this is enabled, tables, views and stored procedures related to the jobs will be created in selected the database. In Elastic jobs, there are two important credentials that need to be created. One user to connect to the shard server and the other user to execute the job in each server.

The following credentials are created on the master shard database:

Further, the user MasterUser should be created in all the servers as below. Remember to use the same password that was used to create the SCOPED CREDENTIAL for the relevant login. In case you are changing the password for the login, make sure that you are changing the password for the policies as well.

The next step is the creation of the group and adds the members to the created group. The following t-SQL statement will create a group named shardDatabase.

As discussed before, we have three different members. Let us add these memberships one by one.

First, we need to enable the entire shard01 to the membership. This means that we are enabling the entire server as below.

In the shard02, we need to exclude the database OtherDatabases and for that purpose we can use the exclude option as shown in the below script.

From the above two scripts, the entire server is included and then the unwanted database is excluded. This needs not to be executed in the same order. For example, you can do the exclude first and then include one.

In the shard03, we need to include the database ShardDatabase06 and for that purpose, we will use the below script.

EXEC jobs.sp_add_target_group_member N’ShardDatabase’, @target_type = N’SqlDatabase’, @server_name =’shard03.database.windows.net’, @database_name =’ShardDatabase06′

We can verify these create groups and its members from the following views:

Create target group and its members.

Next is to create the Job which is the key part of the Elastic Jobs in Azure SQL Databases. Configuration of Elastic Job is somewhat similar to the SQL Server Agent job. In the Elastic jobs, we need to create the job and its step.

Let’s say we want to create a table in all databases in the shards. Since this job will execute under the jobuser credential, we need to create the jobuser in every database and need to create the user in the master and other relevant databases. The following statement should be executed in the master database.

The next step is to map the above login into a user in all the databases including the master databases.

Let’s create the job name create_elastic_job_table with the following script in the master shard database.

The idea of the job is to create the following table in all the shards that mean we are looking at six databases.

We need to add a job step to the job as shown below.

Remember to use the correct credential and the correct target group name.

Since you are creating a table, the jobuser should have the DDL admin permission or DB owner permission in all the databases from the following script.

Let us see how Elastic Jobs in Azure SQL Databases are created using the following scripts.

Following are the output for above queries.

Creation of Jobs and its steps in Elastic Jobs in Azure SQL Databases.

Now we are ready to execute the job using the following script:

For the execution of elastic jobs in Azure SQL Databases, you need to enable the IP address of the master shard at the other database server. If you do not know the IP address, you can retrieve the IP address from the job failure message as well.

The execution of the jobs can be monitored from the following query:

Following is the output for the above query:

Monitoring of Elastic Jobs

After examining the target_database_name column, it is clear that all the databases that are in the shards are selected. From the last_message, you can view the last error. By default, there will be 11 retries before returning the job with a failure.

You can verify that the relevant table is created in all six databases. The next step is to schedule the job. Let us add a step to the job to add a record to the previously created job. Since you are adding the second step, you need to explicitly name the step name.

Next is to schedule the job to execute every minute as shown from the below script.

Now you will observe that data is inserted to the table every minute for all the databases that are in the shard.

Conclusion

Elastic Jobs in Azure SQL Database can be utilized to execute the same query in multiple Azure SQL Databases parallelly. You can configure the Elastic jobs from T-SQL or from power shell scripts as there are no options in Azure Portal. This scalable feature can be enabled in any Azure SQL Databases but not in Azure Managed SQL Server.

References

Dinesh Asanka
911 Views