Choosing the Right SQL Server HA/DR Strategy for Your Business

The right SQL Server HA/DR strategy depends on your recovery time objective (RTO) and recovery point objective (RPO). For near-zero data loss and sub-minute failover, Always On Availability Groups is the industry standard. For cost-sensitive environments with tolerance for some data loss, log shipping or backup-based recovery may be sufficient. Most Australian businesses need a layered approach combining at least two technologies. Choosing wrong costs more than choosing carefully upfront.

Backup schedules and restore procedures are not a disaster recovery plan. That distinction matters enormously when a storage array fails at 2am on a Monday. A proper sql server disaster recovery service covers automated failover, tested recovery procedures, documented RTOs, and regular validation. Too many organisations discover the gap only when they need to close it urgently.

What Are the Core SQL Server High Availability Options?

SQL Server offers four primary technologies for high availability and disaster recovery. Each solves a different problem, and each has real costs and limitations that vendor marketing tends to understate.

Always On Availability Groups (AG) Introduced in SQL Server 2012 and significantly improved through 2019 and 2022, AGs replicate databases at the transaction log level to one or more secondary replicas. Synchronous commit mode delivers RPO of zero and RTO measured in seconds. Asynchronous mode extends geographic reach at the cost of potential data loss measured in seconds to minutes, depending on network latency. Always On Availability Groups require Windows Server Failover Clustering (WSFC) as the underlying infrastructure, which adds licensing and operational complexity.

Failover Cluster Instances (FCI) A failover cluster instance treats the entire SQL Server instance as the highly available unit rather than individual databases. Storage is shared between nodes, typically via a SAN or Storage Spaces Direct. FCI protects against server failure but not storage failure. RTO is typically two to five minutes. It's a mature, well-understood technology that suits environments with existing SAN infrastructure.

Log Shipping SQL Server log shipping copies transaction log backups from a primary server to one or more secondary servers on a scheduled basis. It's simple, reliable, and works across SQL Server editions including Standard. RPO equals your log backup interval, commonly 15 to 30 minutes. RTO is manual and can take 30 minutes or more depending on how many logs need applying. For a sql server disaster recovery service on a budget, log shipping is often underestimated.

Database Mirroring Deprecated since SQL Server 2012 and removed in SQL Server 2022. If you're still running mirroring, migration to AGs should be a priority project, not a backlog item.

How Do You Match Technology to Your RTO and RPO Requirements?

This is where most planning breaks down. Teams pick a technology based on what they know or what a vendor recommended, rather than working backwards from actual business requirements.

Start by asking two questions for each critical database:

1. How much data can the business afford to lose? (RPO)
2. How long can the business operate without the database? (RTO)

Then map the answers to technology:

One practical note: the RTO figures above assume the failover has been tested recently and the team knows what they're doing. Untested failover procedures routinely take two to three times longer than documented estimates.

What Does a Properly Configured Always On Availability Group Look Like?

For environments where the business genuinely cannot tolerate significant downtime or data loss, Always On Availability Groups is the right answer. Here's a minimal T-SQL example to check the synchronisation health of your AG replicas:

SELECT
    ag.name AS availability_group,
    ar.replica_server_name,
    ars.role_desc,
    ars.synchronization_health_desc,
    ars.connected_state_desc,
    ars.operational_state_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar
    ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars
    ON ar.replica_id = ars.replica_id
ORDER BY ag.name, ars.role_desc;

If synchronization_health_desc is returning anything other than HEALTHY, you have a problem that needs investigating before it becomes a crisis. Common causes include log send queue buildup due to network throughput limitations, redo queue growth on the secondary due to disk I/O constraints, and endpoint certificate expiry.

A well-configured AG also requires a listener, proper quorum configuration, and a tested failover runbook. Many environments have the AG configured but no listener, which means applications aren't actually using it. Check your application connection strings.

Why Is SQL Server Log Shipping Still Relevant in 2025?

Log shipping gets dismissed as old technology, but it remains genuinely useful in several scenarios. It runs on SQL Server Standard Edition, which costs significantly less than Enterprise. It's transparent, simple to monitor, and easy to understand operationally. It also works across significantly different SQL Server versions, making it useful during migration projects.

The monitoring query below checks log shipping status across all configured databases:

SELECT
    primary_database,
    last_backup_date,
    last_backup_file,
    backup_threshold,
    DATEDIFF(MINUTE, last_backup_date, GETDATE()) AS minutes_since_backup
FROM msdb.dbo.log_shipping_monitor_primary
ORDER BY last_backup_date ASC;

Any database showing minutes_since_backup exceeding your configured threshold needs immediate attention. A common failure mode is a log backup job silently failing for days, with the secondary falling progressively further behind. By the time someone checks, the secondary is hours out of date.

For a managed sql server disaster recovery service, log shipping is often combined with AG for different tiers of databases. Tier 1 databases get AG with synchronous replication. Tier 2 databases get log shipping. This approach balances cost and protection realistically.

What Are the Most Common HA/DR Configuration Mistakes?

In over 20 years of managing SQL Server environments across Australian businesses, certain mistakes appear repeatedly. Knowing them helps you audit your own environment honestly.

Mistake 1: No tested failover procedure Documentation that hasn't been executed under realistic conditions isn't a procedure. It's a hypothesis. Test failover at least twice per year for production systems.

Mistake 2: Backup jobs and HA technology treated as the same thing Backups protect against data corruption, accidental deletion, and ransomware. HA/DR technologies protect against infrastructure failure. You need both. Neither replaces the other.

Mistake 3: Quorum misconfiguration in WSFC A two-node cluster with no quorum witness will lose quorum if one node fails, defeating the purpose of the cluster. Add a file share witness or cloud witness. This is a basic configuration requirement documented in Microsoft's WSFC documentation, and it's still misconfigured in a significant number of environments we assess.

Mistake 4: Secondary replicas not monitored The primary is healthy. The secondary has been failing to synchronise for three days. Nobody notices until failover is attempted. Monitor both.

Mistake 5: RTO/RPO never formally documented If the business has never agreed on acceptable RTO and RPO, the technical team has no basis for choosing or justifying their configuration. Get these numbers in writing and reviewed by someone with budget authority.

Key Takeaways

• Always On Availability Groups delivers the strongest protection with synchronous commit providing zero RPO and sub-two-minute RTO, but requires SQL Server Enterprise Edition and WSFC infrastructure.
• SQL Server log shipping remains a cost-effective, reliable option for Standard Edition environments or Tier 2 databases where RPO of 15 to 30 minutes is acceptable.
• A failover cluster instance protects against server failure but not storage failure. Combining FCI with AG provides defence in depth.
• Untested failover procedures consistently take two to three times longer than estimated. Test at least twice per year.
• Backups and HA/DR technologies solve different problems. Both are required. Neither is a substitute for the other.

What to Do Next

If you're not certain your current configuration would actually meet your business's recovery requirements, that uncertainty is worth resolving before an incident forces the answer.

DBA Services offers SQL Server Health Checks that specifically assess your HA/DR configuration against your documented RTO and RPO requirements. Our health checks examine AG synchronisation health, log shipping monitor status, quorum configuration, backup validation, and failover readiness. In our experience, the majority of SQL Server environments we assess have at least one significant gap between their assumed protection and their actual protection.

A health check is a fixed-cost, low-disruption engagement that gives you a clear picture of where you stand and a prioritised list of remediation steps. If you need ongoing management of your sql server disaster recovery service rather than a point-in-time assessment, our managed DBA services cover monitoring, maintenance, and tested failover procedures as a continuous service.

Contact DBA Services to discuss what a health check or managed service would look like for your environment.