For database administrators, keeping databases available and recoverable is job one. However, building an effective disaster recovery strategy and high availability (HA) framework involves much more than picking a backup schedule or deploying a standby server.

The right approach depends on your business requirements, the criticality of the data, the time available to recover, and the cost of the solution.

In my recent , I walked through the core questions DBAs should ask when designing a disaster recovery strategy, along with the SQL Server technologies and techniques that help meet different recovery tiers.

Here are the key considerations.

Start with your recovery objectives

Before choosing a technology, establish two essential measures: recovery point objective (RPO) and recovery time objective (RTO).

RPO defines how much data loss is acceptable. For example, if your RPO is 15 minutes, your recovery strategy needs to protect data so that no more than 15 minutes of work is lost.

RTO defines how quickly you need to restore the database. A recovery target of 30 seconds requires a very different approach from one that allows several hours.

These objectives should reflect the business impact of an outage, not simply what is easiest to implement.

High availability adds another consideration. Think of it as having a standby server ready to take over when the primary server becomes unavailable. For greater resilience, that standby may need to be in another geographical region, protecting against a wider regional outage.

The more demanding the recovery objectives, the more infrastructure and investment may be required. A 98% uptime requirement is very different from 99.9%. Therefore, the important thing is to understand what the business needs before deciding how to deliver it.

Build a backup strategy around the data lifecycle

A recovery strategy starts with understanding how long data needs to be retained and what the business expects to recover.

Retention requirements may be influenced by internal policies and regulatory obligations. Once those requirements are understood, you can establish a backup schedule that supports your required recovery points.

For SQL Server, I recommend a practical combination of full, differential, and transaction log backups.

Full backups: establish your recovery foundation

For very large databases, a weekly full backup is often a sensible starting point.

Here are several practical ways to make that process more efficient:

  • Run DBCC checks before taking the backup. SQL Server can back up a database containing corruption, but that doesn’t guarantee the database will be restorable.
  • Use backup compression. This significantly reduces storage requirements and network transfer time.
  • Use multiple backup files. Splitting a large backup across several files allows SQL Server to parallelize the operation. I suggest four files or more for very large databases.
  • Consider file and filegroup backups. These allow you to restore essential parts of a database first, bringing critical tables online before restoring the remaining data.

The objective is not simply to complete a backup. It is to ensure you have a recovery process that fits the database’s size and business requirements.

Differential backups: reduce the amount you need to restore

A differential backup captures the pages changed since the last full backup.

Taking these daily means that recovery doesn’t require replaying every daily backup in sequence. You restore the full backup, followed by the latest differential backup, before applying the transaction log backups needed to reach your exact point of recovery.

Consequently, this makes the restore process much faster and more manageable than relying on a full backup alone.

Transaction log backups: control your recovery point

Transaction log backups provide more granular recovery points.

I often use a 15-minute backup interval as an example. With log backups every 15 minutes, your disaster recovery strategy is designed around an RPO of no more than 15 minutes.

To recover, you restore the full backup, apply the latest differential, and then restore the transaction log backups in sequence up to the point of failure.

One important SQL Server consideration is the log sequence number (LSN) chain. An out-of-cycle backup can break or complicate that chain. Therefore, I strongly recommend using COPY_ONLY for ad hoc backups so they don’t interfere with the regular backup sequence.

Account for transaction log growth

Transaction log management can have a significant effect on recovery time.

As a SQL Server transaction log grows, it creates virtual log files (VLFs). If the log grows repeatedly in small increments, the number of VLFs can become excessively high.

In my consulting and troubleshooting work, I have seen production databases with 50,000 to 60,000 VLFs. In multi-terabyte databases, that can add hours to crash recovery and restore times compared with a database with a well-managed log.

My recommendation is straightforward: pre-size your transaction logs and use sensible growth settings rather than allowing repeated small growth events to create excessive VLFs.

It is a practical configuration step that makes a substantial difference when recovery matters most.

Match the recovery architecture to the importance of the database

Not every database needs the same level of protection.

I recommend establishing different recovery standards for different database tiers:

  • Highest-priority databases: Geographically replicated availability groups, with a recovery target of under 30 seconds.
  • Business-critical databases: A less demanding recovery target, perhaps around one hour.
  • Standard applications: A backup-based recovery approach may be completely sufficient.

Another option is a pilot light arrangement. Rather than keeping a fully provisioned and actively replicating environment running continuously in another site, you keep baseline resources pre-staged. Those resources can be brought into operation when needed. Cloud infrastructure can make spinning up the required compute servers quick and cost-effective.

The guiding principle is to avoid applying the most expensive recovery architecture to every database. Set the standard according to the actual business requirement.

Understand your SQL Server high availability options

SQL Server offers several approaches to protecting databases and making data available elsewhere. Each has different implications for recovery, administration, and cost.

Log shipping: straightforward and dependable

Log shipping copies transaction log backups from a primary server to a secondary server, where they are applied on a regular schedule.

It is a reliable, relatively low-effort option. For example, transaction logs can be transferred and applied every 15 minutes.

The trade-off is that failover is manual. Monitoring and alerts can help identify a problem and support the process, but human intervention is still required.

Log shipping is a good fit when the business has a defined RPO and RTO and does not mandate automatic failover.

Transactional replication: move the data you need

Transactional replication is designed for moving selected data, rather than protecting an entire database instance.

Take the example of 20 regional offices sending sales data to a central headquarters. Replication makes the relevant tables available centrally to support reporting and data aggregation.

In addition, it allows the secondary environment to offload useful query and reporting work.

However, replication can become difficult to manage when applied across many tables or an entire database. It can be brittle when schema changes occur, and failover is not automatic.

It is best considered when the requirement is to move specific data, rather than provide a straightforward disaster recovery solution.

Availability groups: a more comprehensive HA approach

SQL Server availability groups are Microsoft’s recommended approach for many high availability scenarios.

An availability group can have a primary replica and one or more secondary replicas. These can be located in the same data center or distributed across different environments.

A distributed availability group takes this further by connecting separate availability groups. This provides a way to extend replication across environments and geographical locations.

Availability groups offer options for synchronous and asynchronous data movement:

  • Synchronous commit: The primary waits for confirmation that the transaction has been committed and hardened on the secondary. This protects against data loss but can introduce transaction latency, particularly when replicas communicate across a wide area network (WAN).
  • Asynchronous commit: The primary does not wait for that confirmation. This avoids added latency for application transactions, but introduces the possibility of minimal data loss if the primary fails before changes reach the secondary.

Monitoring the volume of transactions or data waiting to be transferred is therefore critical.

Failover cluster instances: useful in specific circumstances

Failover cluster instances (FCIs) provide instance-level protection using shared storage.

I am cautious about recommending them as a general-purpose solution. However, there are specific situations where they make sense:

  • Shared storage is already part of your infrastructure architecture.
  • Instance-level protection is required.
  • The environment has very high database density, potentially around 3,000 databases on a single instance.
  • SQL Server Standard Edition licensing constraints make the approach attractive.
  • The workload involves a particularly large OLTP environment.

Outside those circumstances, I typically advise considering availability groups or log shipping first.

Extending availability groups across cloud environments

Availability groups do not have to be confined to an on-premises data center.

Hybrid arrangements allow a primary replica to run on-premises while a secondary runs in a cloud environment such as Google Cloud Compute Engine, AWS EC2, or Azure.

This provides geographical redundancy without requiring duplicate on-premises data center hardware.

Distributed availability groups offer another proven option for connecting separate availability groups across hybrid boundaries, using synchronous or asynchronous commit based on recovery objectives and network distance.

Keep in mind the configuration requirements, such as establishing reliable trust relationships between on-premises Active Directory and the identity services used by your cloud provider.

Make sure your monitoring covers the whole availability group

Having an availability group in place is only part of the job. DBAs also need continuous visibility into the state of its replicas and the movement of data between them.

During my webinar, I demonstrated how provides visibility into SQL Server Always On environments.

The Always On view brings together the instances participating in availability groups, with graphical topology and detailed metrics:

  • Replica health: Status indicators show whether replicas are healthy and online.
  • Log send queues: Visibility into data waiting to be sent from the primary replica.
  • Recovery queues: Visibility into data waiting to be hardened on secondary replicas.
  • Failover configuration: Indicators show whether automatic failover is configured and whether the designated secondary is ready.
  • Commit mode: The display distinguishes synchronous and asynchronous relationships.
  • Data movement: Line color and width clearly reflect queue health and the volume of data in flight.

DBAs can also inspect Windows Server Failover Cluster nodes, availability groups, quorum votes, and replicas. Context menus allow quick actions, such as opening a failover workflow directly in SQL Server Management Studio.

The history view adds another layer of insight, showing log send and recovery queues, error logs, and replica state changes over time.

I also highlighted newer SQL Sentry capabilities, including data loss metrics at the availability group, instance, and database levels. These provide visibility into replicated data loss, time, data volume, lag, and latency, along with monitoring for contained availability groups.

Explore the or try a hands-on walkthrough in the .

Protect supporting database metadata

Often, the assumption is that availability groups automatically replicate everything needed to run an application. However, there is another recovery consideration when working with availability groups: not everything required to operate a database is automatically replicated with it.

SQL Server system database information (such as SQL Server Agent jobs stored in MSDB and logins, credentials, and server permissions associated with master) requires deliberate planning.

If a database fails over to a secondary replica without the necessary supporting metadata, applications may experience authentication failures or missed jobs.

Contained databases and contained availability groups provide an effective approach to bundling relevant metadata with the database environment, helping you avoid those operational gaps during recovery.

It is worth factoring this into your wider recovery design, rather than focusing exclusively on the database files themselves.

A practical checklist for your disaster recovery strategy

I distill these recommendations into a practical checklist for DBAs planning or reviewing their disaster recovery strategy:

  • Define RPO and RTO first. Establish how much data the business can afford to lose and how quickly systems need to recover.
  • Set recovery standards by database tier. Reserve the most demanding HA architecture for the databases that genuinely need it.
  • Use a backup strategy that supports your recovery objectives. Combine full, differential, and transaction log backups where appropriate.
  • Use COPY_ONLY for ad hoc backups. Avoid disrupting the regular LSN backup sequence.
  • Make large backups more manageable. Consider compression, multiple backup files, and file or filegroup backups.
  • Pre-size transaction logs. Avoid excessive VLF counts that can extend recovery time.
  • Choose the right HA technology. Consider log shipping, transactional replication, availability groups, distributed availability groups, and FCIs according to the workload and recovery requirements.
  • Monitor data movement and replica health. Pay attention to log send queues, recovery queues, latency, and potential data loss.

Make recovery a deliberate decision

There’s no single recovery architecture that works for every database.

A backup-based strategy may be perfectly adequate for one application, while another may require geographically distributed availability groups and recovery in seconds.

The important thing is to understand the business requirements, establish realistic RPO and RTO targets, and choose the technology that meets them without adding unnecessary complexity or cost.

And once the architecture is in place, make sure you can see what’s happening across it.

With SolarWinds SQL Sentry, DBAs can monitor SQL Server Always On environments, investigate replica health, and track the movement of data between replicas. That visibility helps teams understand the state of their high availability environment when it matters.

The best disaster recovery strategy is one you have designed deliberately, understand clearly, and can rely on when things go wrong.

See SolarWinds SQL Sentry in action

Ready to see how dedicated database observability keeps your high availability and disaster recovery environments resilient?

Frequently asked questions about database disaster recovery

What is the difference between RPO and RTO?

Recovery point objective (RPO) defines the maximum acceptable amount of data loss measured in time (for example, losing up to 15 minutes of transactional data). Recovery time objective (RTO) defines the maximum acceptable duration of downtime before systems and applications must be restored and operational (for example, recovering within 30 seconds or two hours).

How often should SQL Server transaction logs be backed up?

Transaction log backup frequency should match your RPO target. If your business cannot tolerate more than 15 minutes of data loss, log backups must run at least every 15 minutes. In higher-throughput systems with strict RPO targets, teams often schedule log backups every 5 to 10 minutes.

Why do virtual log files slow down database recovery?

When a transaction log grows automatically in small increments, SQL Server generates thousands of virtual log files (VLFs). During crash recovery or database restore operations, SQL Server must process every VLF sequentially. Having tens of thousands of VLFs can add hours to recovery times. Pre-sizing log files and setting larger fixed autogrowth increments prevents excessive VLF generation.

When should DBAs choose availability groups over log shipping?

Choose Always On availability groups when you need automated failover, near-zero RTO, support for synchronous zero-data-loss replication, or read-only secondary routing. Choose log shipping when manual failover is acceptable, WAN bandwidth is limited, budget constraints favor standard licensing, or you require a simple, reliable standby copy.

How does SolarWinds SQL Sentry monitor availability groups?

SQL Sentry provides a dedicated Always On topology view showing real-time replica health, failover modes, and commit states across all cluster nodes. It visualizes log send queues, recovery queues, transfer latency, and estimated data loss metrics, enabling DBAs to spot replication lag and resolve sync bottlenecks before failover is compromised.

Tags:
database administrator
database management
database monitoring
dba
devops
information security
security information and event management