In SQL Server (and other database systems), High Availability (HA) ensures that your databases remain accessible and operational even in the event of hardware failures, software issues, or planned maintenance. The difference between basic and advanced HA lies in the features, flexibility, and scalability they offer.
Basic High Availability
Basic HA solutions are typically simpler to configure and are suitable for small to medium-sized environments with less complex requirements.
Common Basic HA Options:
- Database Mirroring (Deprecated): Simple failover for a single database.
- Log Shipping: Periodic transaction log backups sent to a secondary server.
- Basic Availability Groups (SQL Server Standard Edition):
- Supports only one database per group.
- Automatic or manual failover, managed by the Windows cluster.
- No read-only replicas.
- Limited to two nodes (primary and secondary).
Pros:
- Easier to set up and manage.
- Lower licensing and infrastructure costs.
Cons:
- Limited scalability and flexibility.
- Fewer automation and monitoring features.
Advanced High Availability
Advanced HA is designed for enterprise-grade environments with mission-critical workloads.
Common Advanced HA Options:
- Always On Availability Groups (SQL Server Enterprise Edition):
- Supports multiple databases per group.
- Automatic failover with full synchronisation.
- Read-only replicas for reporting or backups.
- Up to eight secondary replicas.
- Failover Cluster Instances (FCI):
- Provides instance-level protection.
- Requires shared storage and Windows Server Failover Clustering (WSFC).
Pros:
- High scalability and performance.
- Better support for disaster recovery and load balancing.
- Advanced monitoring and automation.
Cons:
- More complex to configure and maintain.
- Higher licensing and infrastructure costs.
Summary Table
| Feature | Basic HA | Advanced HA |
|---|---|---|
| Edition | Standard | Enterprise |
| Databases per group | 1 | Multiple |
| Automatic failover | Limited | Yes |
| Readable secondaries | No | Yes |
| Number of replicas | 2 | Up to 9 (1 primary + 8 secondaries) |
| Use case | Small/medium workloads | Enterprise/mission-critical systems |
Note: Failover Cluster Instances (two nodes) and log shipping are also available in Standard Edition. Enterprise Edition is needed for full availability groups with readable secondaries.
To prevent SQL Server Agent jobs from running on all nodes in a High Availability (HA) setup, especially in Always On Availability Groups, you need to make sure jobs only run on the primary replica. This avoids duplicate job execution across replicas.
Steps to Prevent Jobs Running on All HA Nodes
1. Use a Job Check Script
Add a step at the beginning of each job to check if the current server is the primary replica:
-- Job step 1: carry on only on the primary replica.
-- Set this step's 'On failure action' to 'Quit the job reporting success'.
IF ISNULL(sys.fn_hadr_is_primary_replica(N'YourDB'), 1) = 0
RAISERROR (N'Not the primary replica for YourDB - skipping.', 16, 1);
Place this in the first step of the job, with that step’s On failure action set to Quit the job reporting success. On a secondary the step fails, the job stops quietly and the remaining steps never run. sys.fn_hadr_is_primary_replica returns NULL if the database is not in an availability group, so the job runs as normal there.
2. Centralise Job Management
Use a central job server or management tool (like SQL Server Agent on the primary node or a third-party scheduler) to control job execution.
3. Use Availability Group Listeners
If your job connects to a database in an AG, ensure it uses the AG listener name so it always connects to the primary replica.
4. Monitor and Audit Job Execution
Set up alerts or logging to track job execution and ensure they’re not running on secondary replicas.
5. Disable Jobs on Secondary Replicas
Manually disable jobs on secondary nodes, or use a start-up procedure to disable them automatically if the node is not primary.
From my SQL Server DBA handbook.