Key takeaways

  • Always On availability groups protect against server and site failure with fast failover. They also copy mistakes: a bad delete replicates to the secondary.
  • Log shipping keeps a warm standby that trails by a set delay. The delay can give you a window to catch a mistake, but failover is manual.
  • Backups are the only one of the three that restores to an earlier point in time. They are your defense against corruption, deletion and ransomware.
  • A backup you have not restored is a hope. Test restores on a schedule and time them against your recovery goals.

Start with the answer: these three features solve different problems. Availability groups reduce downtime when hardware or a data center fails. Log shipping gives you a simpler, delayed copy for disaster recovery. Backups give you history. If someone drops a table at 10:05, an availability group faithfully drops it on the secondary within moments. Only a backup, or a delayed log-shipped copy, takes you back to 10:04.

Two numbers to decide first

Before choosing, write down two targets for each system. The recovery time objective is how long the business can be without the database. The recovery point objective is how much recent data it can afford to lose. A payments database might need minutes on both. A reporting database might tolerate hours. The answers decide which features you need and how much you spend on them.

Always On availability groups

An availability group replicates a set of databases from a primary replica to one or more secondary replicas. In synchronous commit mode, the primary waits for the secondary to harden the log before it confirms a commit, which allows automatic failover with no data loss when the replicas are healthy. In asynchronous commit mode, the primary does not wait, so latency across distance is lower but a failover can lose recent transactions, and failover is typically manual. Microsoft documents the failover modes in detail.

What it protects against: server failure, operating system patching with short outages, and in the asynchronous case, loss of a site. Secondaries can also serve read workloads and backups, subject to licensing and configuration.

What it does not protect against: logical damage. Deletes, bad updates, application bugs, corruption written by software and ransomware that encrypts through legitimate connections all replicate. It also does not replace a backup. It adds operational weight too: a cluster, a quorum design, listener and network configuration, and careful patching order.

Log shipping

Log shipping is older and simpler. A job backs up the transaction log on the primary at an interval, copies the file to one or more secondary servers, and restores it there. The secondary stays in a restoring or standby state. Because the restore can be delayed on purpose, you can hold the secondary some hours behind the primary.

What it protects against: loss of the primary server or site, at a data loss equal to the interval since the last shipped log, and, with a delay, a mistake you notice quickly. It is a good fit for a low-cost disaster recovery copy in another location.

What it does not do: failover is manual. You must restore the remaining logs, bring the secondary online and redirect applications. Logins, jobs and other objects outside the database must be kept in sync by you. It works at the database level, and there is no automatic return path.

Backups

A sound backup plan uses full backups, differential backups and frequent transaction log backups, with the log chain unbroken. This gives point-in-time restore, so you can recover to just before a bad event. Microsoft's backup and restore documentation explains the recovery models and how the sequence of backups is used to restore.

Backups also address threats the other two cannot. Keep copies away from the server: in another account, another site or immutable storage, so an attacker who controls the database host cannot delete them. Encrypt them and protect the keys, and remember that you need the certificate or key to restore. Run integrity checks on the databases and on the backups. Our article on backups that restore covers the 3-2-1 rule, immutability and hurricane season planning.

A simple comparison

  • Server failure: availability group, log shipping and backup all help. Only the availability group fails over quickly and automatically.
  • Site loss: asynchronous availability group, log shipping and off-site backup all help. Data loss and recovery time differ.
  • Accidental delete or bad deployment: backup, and delayed log shipping if you notice in time. The availability group does not help.
  • Corruption or ransomware: clean backups. Replicas may carry the damage.
  • Planned maintenance: availability group gives the shortest outage.

How most businesses combine them

A common pattern for a critical database is an availability group inside one region for high availability, log shipping or an asynchronous replica to a second site for disaster recovery, and tested backups with off-site, immutable copies underneath. A smaller business may skip the first two and rely on backups plus a documented restore to a standby server, which is a legitimate choice when the recovery time objective allows it. What it should not skip is the restore test.

Write the runbook before you need it: who declares a disaster, the steps to fail over, how applications repoint, and how you return. Then rehearse it. Our team designs and operates database platforms and recovery plans. Start with our Managed IT Services or Cloud Management service.

Sources

This article is general information, not legal advice. Your obligations and the right steps depend on your systems, contracts and the laws that apply to you. Last reviewed .

Keep reading

Need help with this?

We run your IT day to day so it stays secure: help desk, devices, Microsoft 365, Exchange, Active Directory, backups, networks and monitoring.

Managed IT Services