Azure SQL Database backups, PITR and LTR with Failover Groups

Question

Let’s say I have two Azure SQL DB servers which participate in a failover group (one server in east, the primary, and the other in west, the read-only secondary). There are roughly a dozen databases on the servers that belong to the failover group. These databases are a mix of DTU based and Hyperscale. 

I know the following:

• Hyperscale databases are backed up using snapshots
• DTU based are backed using full/diff/log.

 My 3 main questions are the following:

If the databases reside on both servers (due to the failover group), are they BOTH backed up, thus doubling my storage costs?

1. As far as LTR, should that only be configured on one of the servers, and not both?
2. Should LTR be configured on the primary server of the failover group, or the secondary?
3. If the databases reside on both servers (due to the failover group), are they BOTH backed up, thus doubling my storage costs?

It just seems redundant to have backups and LTR done on both nodes. Or am I missing something?

Let’s say I have two Azure SQL DB servers which participate in a failover group (one server in east, the primary, and the other in west, the read-only secondary). There are roughly a dozen databases on the servers that belong to the failover group. These databases are a mix of DTU based and Hyperscale. 

I know the following:

• Hyperscale databases are backed up using snapshots
• DTU based are backed using full/diff/log.

My 3 main questions are the following:

1. If the databases reside on both servers (due to the failover group), are they BOTH backed up, thus doubling my storage costs?
2. As far as LTR, should that only be configured on one of the servers, and not both?
3. Should LTR be configured on the primary server of the failover group, or the secondary?

It just seems redundant to have backups and LTR done on both nodes. Or am I missing something? Looking for best practices for database PITR/LTR when databases reside in a failover group.

Answer 1

Hi @Scott Klein,

Thanks for the question and using MS Q&A platform.

When you have a failover group set up, the primary server in the East region is responsible for handling backups. The backups for the databases are stored on the primary server and the logs are replicated to the secondary server in the West region.

Since backups for each database are not duplicated on both servers, you typically won’t incur double storage costs for backups. The secondary server only maintains a read-only copy of the databases and does not create its own separate backups.

LTR should only be configured on the primary server. The LTR backups capture the state of the database at the primary location, and since the secondary is a read-only replica, it doesn’t require its own LTR configuration.

This setup helps avoid redundant configurations and associated costs. Configure LTR on the primary server for the databases in the failover group.

It’s best practice to configure LTR on the primary server of the failover group. This ensures that you have the most relevant backup data for recovery, as the primary server is the source of all write operations.

The secondary server doesn’t need LTR configured since it only serves as a read-only replica. If the primary server fails, the secondary can be promoted, and it will have the most recent backup and logs synced from the primary.

I hope this information helps, please do let us know if you have any Queries.

Hope this helps. Do let us know if you any further queries. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.