Do You Need an Availability Group? Matching RPO and RTO to a Feature

An availability feature should solve a defined service requirement. Agree on RPO and RTO first, then compare the recovery behavior and operating work of the available designs.

Ask the Business to Set RPO and RTO

Recovery point objective describes the tolerated loss of work as a time interval. Recovery time objective describes the tolerated delay before the service returns. Define the service, the assumed failure, and who accepts reopening. A local server failure, a storage failure, a site outage, and an accidental data change need different recovery paths.

Ask whether users can repeat lost work and whether delayed service prevents another business process from functioning. Identify dependencies across databases and external systems. I write the accepted scenario beside the objective so a feature selected for one failure is not presented as protection against every failure.

Separate a target from demonstrated behavior. A design diagram can promise a short recovery while authentication, application routing, or a missing job delays the actual service. Include those components in the test. A feature name does not contain a hidden agreement with the business.

Check the Backup Position Against RPO and RTO

Backups provide a recovery path that every availability design still needs. Inspect the most recent completed full, differential, and log backups alongside the recovery model. This query summarizes local history for current user databases.

SELECT d.name,d.recovery_model_desc,
       MAX(CASE WHEN b.type='D' THEN b.backup_finish_date END) AS LastFull,
       MAX(CASE WHEN b.type='I' THEN b.backup_finish_date END) AS LastDifferential,
       MAX(CASE WHEN b.type='L' THEN b.backup_finish_date END) AS LastLog,
       DATEDIFF_BIG(second,MAX(CASE WHEN b.type='L'
                    THEN b.backup_finish_date END),SYSDATETIME()) AS SecondsSinceLog
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name=d.name
    AND b.backup_finish_date IS NOT NULL
WHERE d.database_id>4
GROUP BY d.name,d.recovery_model_desc;

Time since the last log backup is a useful exposure indicator, not a guaranteed worst-case data-loss figure. History does not prove accessible files, an intact chain, or successful recovery. A missing log entry is unknown coverage, and simple recovery has a different restore capability. Validate the actual retained media and selected recovery point.

Backup frequency alone does not determine restore time. Include media transfer, database size, file initialization, log replay, encryption setup, consistency checks, and application acceptance. An isolated restore exercise gives a more useful result than estimating recovery from backup-job duration. Keep that result current after the data or infrastructure changes.

Consider Backups and Restore as the Baseline

For a service that accepts a longer interruption, tested backups and an available recovery destination can meet the requirement with manageable complexity. The design still needs offsite protection, accessible credentials, sufficient capacity, and a practiced runbook. A backup on the failed server's only storage volume is an incomplete failure boundary.

Measure the point reachable from retained full, differential, and log backups. Tail-log recovery can reduce loss when the original log remains usable, but it is scenario-dependent. Record who chooses between recovering over the failed database and building a separate restored copy. Use the simpler design when its demonstrated behavior meets the accepted objective.

Evaluate a Log-Shipping Standby

Log shipping automates log backup, copy, and restore jobs to a standby. The accumulated delay across those stages determines how current the standby is. A planned restore delay can provide time to respond to certain accidental data changes, although it is not a replacement for backup retention and independent recovery copies.

Role change requires an operating procedure and application reconnection plan. Verify the remaining logs, the standby's state, and the accepted data-loss decision before recovery. Synchronize required instance objects separately. Monitor the last successful backup, copy, and restore rather than relying only on whether Agent is running.

SELECT primary_server,primary_database,
       last_backup_date,last_backup_file
FROM msdb.dbo.log_shipping_monitor_primary;
SELECT secondary_server,secondary_database,
       last_copied_date,last_restored_date,
       last_copied_file,last_restored_file
FROM msdb.dbo.log_shipping_monitor_secondary;

Run those queries on the configured monitor with the required access. On a server with no log shipping configured, both come back empty. NULL or stale results need investigation, including whether the monitor has current reporting. Exercise the actual role change and validation sequence. A warm standby reduces some restore work, but it does not automatically make the service available to clients.

Understand What a Failover Cluster Instance Protects

A failover cluster instance protects the SQL Server instance through a cluster-managed alternate node and its configured storage design. It includes instance-level components under that instance, but failover does not inherently create an independent database copy on separate storage. Evaluate storage availability and the site failure boundary explicitly.

SELECT SERVERPROPERTY('IsClustered') AS IsClustered,
       SERVERPROPERTY('ServerName') AS ServerName,
       SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS CurrentNode;

A clustered instance can address a server failure while still requiring another design for storage loss, corruption, or accidental deletion. Confirm that the alternate node has adequate capacity and that clients reconnect as expected. Test dependencies under the cluster's actual failure policy. Backup and restore remain necessary even when node failover works correctly.

Evaluate Availability Groups Against the Actual Requirement

An availability group maintains database replicas on separate instances. Synchronous commit can support a no-data-loss failover when the required replica is synchronized and the supported failover conditions are met. Asynchronous replication accepts a potential loss window. Forced failover to an unsynchronized copy can lose data even when the normal design uses synchronous commit.

Check current replica configuration and database movement state rather than inferring protection from the existence of an availability group. The following diagnostic query reports local and visible replica state within its permissions and role-dependent scope.

SELECT ar.replica_server_name,ar.availability_mode_desc,
       ar.failover_mode_desc,drs.is_local,
       DB_NAME(drs.database_id) AS DatabaseName,
       drs.synchronization_state_desc,drs.synchronization_health_desc,
       drs.is_suspended,drs.log_send_queue_size,drs.redo_queue_size
FROM sys.availability_replicas AS ar
JOIN sys.dm_hadr_database_replica_states AS drs
  ON drs.replica_id=ar.replica_id AND drs.group_id=ar.group_id;

On an instance with no availability group, the query returns no rows. Queue sizes describe work outstanding, not a complete service recovery estimate. Visibility differs between primary and secondary replicas. Check the edition's supported capabilities, listener behavior, quorum, and failover eligibility for the selected platform. Avoid selecting the feature from a checklist without understanding the operational conditions that make failover available.

Which job, login, or application dependency will be missing after the database changes replicas? Inventory those separately unless the chosen deployment has explicitly supported containment behavior for them. I test client routing, read-write intent, credentials, and scheduled work alongside database failover. The service can remain broken while the database is already writable.

Test the Smallest Design That Meets RPO and RTO

Compare demonstrated RPO and RTO, failure coverage, staffing, monitoring, maintenance, and recovery complexity. Include planned outages as well as unplanned ones. Keep an independent recovery route for data mistakes and threats that can propagate to online replicas. A replica that promptly receives a bad change is doing its assigned job.

Use RPO and RTO as acceptance criteria for a complete recovery exercise. Record unmet conditions and the owner's decision rather than hiding them behind a more elaborate architecture. Select an availability group when its tested behavior and operating requirements match the service, and retain simpler recovery methods when they satisfy the accepted objectives.

Related reading on this blog: Full, Differential and Log Backups: A Practical Guide and Check Backup Reliability.

An availability feature is not a recovery objective, it is one component of a tested service recovery design.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.