Make SQL Server Agent Jobs HADR Aware


Always On Availability Groups (AGs/AG) provides a fairly convenient and reliable High-Availability Disaster Recovery (« HADR ») solution. Each database is configured to provide a fail-over solution using multiple servers running Windows Clustering replicas. The two replicas, primary (supporting reads and writes) and secondary (a.k.a. read replica), are synchronized via your choice of a synchronous or an asynchronous process. Once an AG Listener identifies an unhealthy primary, it will bring read replica as a primary on a different server.

This blog post is going to cover the process to make SQL Server Agent (« Agent ») AG-aware. Some knowledge of cursors, sp_executesql and job