Introduction
One thing that has been lacking in Azure SQL Database is the ability to write database jobs in Transact-SQL. Recently, Microsoft has introduced T-SQL database jobs this functionality in Elastic Database Jobs. This article will talk about the benefits of Elastic Jobs as well as make a case as to why you should vote for this Feature Suggestion so that all Service Tiers can benefit from this functionality.
Elastic Database Jobs
At Build 2015, Microsoft announced Azure Elastic Databases, an enhancement that allows users to create a large group of databases that all share a single pool of resources. Using this model, Database Throughput Units (DTU's) can be shared between databases instead of just being allocated to a single instance and sitting idle. This type of architecture is suited to scenarios where an organisation will have a large number of databases for a single application. For example, an application could have a single database per customer, and each server will contain many of these customer databases. This scenario can be seen in the diagram below.
Figure 1 - Normal Server vs Server with Elastic Pool
Due to the pricing model, Elastic Databases only make sense economically when there are a large number of databases sharing a single Elastic Pool (think 100+ databases). This architecture would create a problem if you needed to deploy schema changes to all of those databases using a traditional Powershell Runbook. To solve this problem, Microsoft introduced
Elastic Database Jobs. Elastic Database Jobs allows users to create jobs using T-SQL and run these jobs against one (or more) databases in the Elastic Pool.
In order to set up Elastic Job you will need to do the following (there are too many steps to set them all out in this post so I will just include the links):
- Create an Elastic Pool
- Install the Elastic Database Jobs components
- Create an Elastic Database Job
When you've finished you'll have an Elastic Scale Job that you'll be able to run against one or more of the databases in your Elastic Pool as shown below.
I think that Elastic Jobs in one of the most exciting things to happen to Azure SQL Database in quite some time. While I have written a fair share of Powershell Runbooks, they feel like a bit of unnecessary overhead if I am strictly doing a T-SQL task. I also feel that it will be a massive barrier to adoption for organisations with on premise versions of SQL Server to convert all of the SQL Server Jobs to Powershell Runbooks in order to move to Azure SQL Database. I know that in some of the organisations that I have worked in, the conversion would have been way too difficult (and costly) and would make Azure SQL Database become not a viable solution.
So with all of the positives that come with this new release of technology, there is one MAJOR drawback: Elastic Database Jobs are only available on databases in Elastic Pools. And databases in Elastic Pools are really expensive. When I set up the Elastic Pool for this demo, my monthly charges on my demo server went from $60 per month up to $458 per month (and that is with a 50% discount because the service is still in preview). That is just for one Elastic Database at the minimum 1GB size. Needless to say, the Elastic Pool was deleted very quickly. If you'd like to read what other have to say on the costs of the new Elastic Pool offering, check out the comments posted on the article
Tame explosive growth with elastic databases posted
here - it is not pretty reading.
While I am not going to enter the debate around the cost of Elastic Pool Databases (although they are really expensive), I will say that I don't see any reason that T-SQL jobs need to be reserved for Elastic Pool Databases. Why can't all Service Tiers have this functionality? I don't see any reason that why they can't. In order to try and get this functionality included in future releases of Azure SQL Database, I have created the following Feature Request -
Allow transact-sql jobs for all service tiers not just elastic scale databases. Please give it a couple of votes if you agree!!!
