Is SQL Server Agent missing from Azure SQL Database?

Gavin Payneis a principal architect for Coeo, a SQL Server and Azure professional services company, and a Microsoft Certified Architect and Microsoft Certified Master. His role is to guide and lead organisations through data platform transformation and cloud adoption programmes.

Despite Microsoft continuously adding functionality to it, the Azure SQL Database service has always been missing one key feature – SQL Server Agent. The reality is, it’s absent rather than missing.  Platform as a Service capabilities are, and should be, promoting platform wide approaches to scheduling. So now is as good a time as ever for database developers and administrators to transition to a world where their favourite agent is missing.

Azure SQL Database

The Azure SQL Database service has had a bumpy few years, as well as a few names. The reason SQL Server is popular is because of its scalability and broad range of features. Wrapping that many capabilities into a fully managed API based service meant tough decisions. There were tears from the community but more significantly, there were roadblocks stopping adoption.

In early 2016, life is different. The service’s latest release, V12, now has almost all of the core database engine’s functionality and a few exclusive extras. Global scalability, built-in high availability and a strong T-SQL language often make using it an obvious choice for developers.

The use – and misuse – of SQL Server Agent

SQL Server Agent is a bolt-on service to the SQL Server database engine. It’s a scheduling engine with (very) basic workflow capabilities. It knows enough about T-SQL, PowerShell, SSIS and operating system commands to manage them. If executing tasks wasn’t useful enough, it also has a notification and alerting engine. In summary, it’s useful.

Its usefulness was its biggest downfall. A tool that was intended to schedule maintenance tasks often ends up being used as an application workflow engine. I often see tasks scheduled to run once a minute that empty idle shopping baskets or update the counters on management dashboards. Its simplicity lets organisations become dependent on what must be one of the most expensive scheduling and batch processing tools there is.

New world thinking

In the era of the cloud, developers are creating applications that use bundles of small pieces of functionality.  This helps them scale out using lots of cheap compute services.  These application tiers are where developers should deploy and execute scheduled tasks.  Even if they subsequently call database stored procedures that do all of the hard work.  The capabilities in Azure to do that there are far stronger, more appropriate and cheaper than using a database engine’s internal scheduling engine.

Azure scheduling options

Azure provides the Azure Scheduler and Azure Automation services - for scheduling and automation. Neither are a like-for-like replacement for SQL Server Agent, but times have changed and we live in a new – better – world.

The Scheduler service in Azure is a simple service with a narrow set of capabilities. At a given time or on a given schedule, it’ll call an HTTP or HTTPS API, or post a message to a queue. It’s perfect for application environments where business logic can be executed using APIs. If this is how your application works – then schedule your tasks here. If you want to schedule database index maintenance or something else, keep reading.

The Automation service in Azure is the primary tool for those needing to schedule any other tasks. Like the Azure Scheduler service, this is very different to SQL Server Agent, but this is the cloud PaaS world. The Azure Automation service uses runbooks to manage, schedule and define its jobs. These contain command script JSON documents that store PowerShell commands.

For those un-familiar with JSON and PowerShell, there’s a gallery of pre-written runbooks that can be used. Helpfully, for those looking to replace SQL Server Agent, there’s one that executes a T-SQL command. The tools are there, it just needs the time to set it up.

Simplicity, however, is often traded for capability and the Azure Automation is perhaps sadly a good example of this. It uses an Azure Active Directory based security model that can take time to setup, it can’t schedule something to run less than once an hour and its best management interface is the Azure Portal.

However, I’ve seen application development teams who never thought to use it to schedule index maintenance for their Azure SQL Database service databases. For them, it was 30 seconds of clicks, copy and pasting, and scheduling. For database administrators, I recommend it’s something they add to their learning plan.

Our missing agent

It’s true that in the cloud world, our trusted SQL Server Agent feature is missing from the Azure SQL Database service. As worrying as this sounds, I suspect he’s been gone too long to be missed now.

If you would like to learn more about Azure SQL Database then check out this Microsoft Virtual Academy course.