If you are an old hand at Microsoft SQL Server, you have probably used the SQL Server Agent (i.e., SQL Agent) service and MSDB database for the last 20 years. With a humble and largely unchanged feature set since it was first introduced, SQL Agent is one of those incredibly useful and cost-saving features we have come to expect from the Microsoft Data Platform. However, SQL Agent faces one important shortcoming: It can run on Azure VMs and on Azure SQL Managed Instances, but it is not available on Azure SQL Database. As a result, we need a substitute.
I recently had the pleasure of discussing Azure Elastic Database Jobs (i.e., Elastic Jobs) with Kate Smith and Srini Acharya, program managers in the Microsoft SQL Database product group, specifically for Azure SQL. Elastic Jobs is a new feature set currently in public preview, with an expected general availability date sometime in the first half of 2021. Kate and Srini are excited about Elastic Jobs and the cool things it can do, such as automatically detecting a newly added or dropped database at execution time and responding accordingly. SQL Agent and other automation products usually expect a fixed set of target databases, and the list needs an explicit refresh through app logic. Elastic Jobs provides this dynamic enumeration at runtime to address such scenarios.
Here’s a bit more good news. Public preview might sound as if you have a long time to wait. But, it actually means that the feature set is essentially finished, with only incremental changes to come. Many Microsoft customers are currently using Elastic Jobs in production and are fully supported it through their Microsoft Support contracts. With that behind us, let’s dive into the features.
All About Azure Elastic Database Jobs
As a starting point, let’s assume you know at least the basics of SQL Agent so we will simply compare the two. Many functional behaviors are held in common but are implemented differently. For example, both Elastic Jobs and SQL Agent allow you to create jobs composed of one or more steps so that you can fully customize the execution sequence of a series of actions. As another example, both Elastic Jobs and SQL Agent can be managed through a set of stored procedures to add, drop, and manage your jobs. Some key differences to keep in mind are the following:
First, Elastic Jobs supports T-SQL, PowerShell, REST APIs, and API interfaces to the Azure Portal and the Azure Resource Manager. SQL Agent, in contrast, supports only T-SQL and the SQL Server Management Studio (SSMS) API, and SMO.
Second, Elastic Jobs has a broader scope in Azure, running jobs on multiple Azure SQL Databases and/or data warehouses as long as they’re in the same cloud as the job agent. The targets can be on different servers (including Hyperscale and Serverless databases), subscriptions (including Azure SQL elastic pools), shard maps, or regions. SQL Agent runs jobs on a local database server or, using a master server, on multiple target servers.
In addition, Elastic Jobs can dynamically enumerate the database in a target group, including or excluding databases that have added or dropped without manual intervention. Target groups can also be customized to include/exclude specific databases, Elastic pools, and/or servers. This is one of the key differentiators with other solutions in this area, especially in SaaS or other dynamic settings where additional databases are added to a server/pool frequently.
Elastic Jobs can also support an upper limit on the number of databases a job runs in parallel for more efficient resource consumption, support APIs and stored procedures so that you can build additional GUI functionality, and it is a fully integrated Azure service.
A Panoply of Options
You have other options for automation when moving from on-premise SQL Server to Azure SQL Database. For example, depending on your situation you could use Azure Automation Hub, Azure Runbooks, Azure ARM templates, Azure Functions, and Azure Automation Desired State Configuration (DSC). As mentioned earlier, you can still use SQL Agent on Azure VMs and, with some limitations, on Azure SQL Managed Instances.
With all of those options competing for your attention, just remember that Azure Elastic Database Jobs are designed from the ground up to work with and support Azure SQL Database. They’re a natural fit. However, note that pricing is not yet set for Elastic Jobs.
Learn More
Kate Smith has written an excellent six-post blog series discussing the ins and outs of Elastic Jobs, including monitoring and troubleshooting advice, starting with the post at: https://techcommunity.microsoft.com/t5/azure-sql-database/elastic-jobs-in-azure-sql-database-what-and-why/ba-p/1177902. In addition, Kate has posted a bunch of very useful samples on Github at https://github.com/Azure-Samples/elastic-jobs-samples.
You can also read more in the Microsoft Documentation online at https://docs.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview#elastic-database-jobs-preview for full coverage of the conceptual and architectural aspects of Elastic Jobs. Finally, read up on how to create your first Elastic Job at https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview. (An Azure account and at least one Azure SQL Database are required to begin a trial).