Post navigation

Azure SQL Elastic Jobs

The purpose of an Elastic Job is to execute a T-SQL script that is scheduled or executed ad-hoc against a group of Azure SQL databases. Targets can be in different SQL Database servers, subscriptions, and/or regions. This blog post is quite long and heavy (code wise) so grab a coffee and follow through.

The architecture you could follow is shown below.

Setup

Setting this up does take some time. PowerShell is the way forward here rather than T-SQL. Looking at the above architecture you can see that we will need a database to host the job database and for this example a different server that hosts the target databases to where I want to code deployed to.

The setup between the databases involves databases scoped credentials and keys as summarised below.

The code you will see it what I ran and got the feature working (with all fake server names and passwords etc).

Once you are ready load PowerShell ISE and run the following commands.

Please note for this example I opened the firewall to allow all Azure IPs making this demo easier to show. I suggest you don’t do this and run the script and if I remember correctly the IP needed will be presented to you upon running the code.

Now, if you connect via SSMS to the host server where the job database is, you can issue the following code to get confirmation of a successful run,

SELECT * FROM jobs.job_executions
ORDER BY start_time DESC

Connecting to the target server you can see the tables have been created.

Hopefully you can see the benefit of setting up elastic jobs. Just one last important point is that the scripts you run must be idempotent. That is the script can survive during network issues. An idempotent script has the same result even if it has been successfully run twice. It helps to follow this approach.