Disaster Recovery with Azure SQL Managed Instance

This overview describes the capabilities that SQL Managed Instance provide for business continuity and disaster recovery.

This overview lists capabilities which are provided automatically or by default. SQL Managed instance also provide several business continuity features that we can configure to mitigate various unplanned scenarios.

+Automatic/Default available capabilities

1. Full Database backups weekly

2. Differential database backups every 12 hours

3. Transaction log backups every 5–10 minutes

https://docs.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?tabs=single-database

By default, the backups are stored in Geo-redundant backup storage (RA-GRS) for at least 7 days. All service tiers except Basic support configurable backup retention period for point-in-time restore, up to 35 days.

By default, SQL Database and SQL Managed Instance store data in geo-redundant storage blobs that are replicated to a paired region. This helps to protect against outages impacting backup storage in the primary region and allow you to restore your server to a different region in the event of a disaster.

To mitigate the local hardware and software failures, SQL Database includes a high availability architecture, which guarantees automatic recovery from these failures with up to 99.995% availability SLA.

High availability architecture differs between various service tiers. Please go through below link which describes how service tiers handle high availability.

https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla

1.Backup Usage

We can use these backups to

1. Point-in-time restore of existing database.

2. Point-in-time restore of deleted database.

3. Geo-restore.

https://docs.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?tabs=single-database

2. Backup scheduling

The first full backup is scheduled immediately after a new database is created or restored. This backup usually completes within 30 minutes, but it can take longer when the database is large. For example, the initial backup can take longer on a restored database or a database copy, which would typically be larger than a new database. After the first full backup, all further backups are scheduled and managed automatically. The exact timing of all database backups is determined by the SQL Database or SQL Managed Instance service as it balances the overall system workload. You cannot change the schedule of backup jobs or disable them.

3.Backup storage

A backup storage redundancy of a managed instance can be set during instance creation only. The default value is geo-redundant storage. Other options are locally-redundant, zone-redundant and geo-redundant backup storage.

+Configurable capabilities

1.Long-term backup retention

Many applications have regulatory, compliance, or other business purposes that require you to retain database backups beyond the 7–35 days provided by Azure SQL Database and Azure SQL Managed Instance automatic backups. By using the long-term retention (LTR) feature, you can store specified SQL Database and SQL Managed Instance full backups in Azure Blob storage with configured redundancy for up to 10 years. You can then restore any backup as a new database.

https://docs.microsoft.com/en-us/azure/azure-sql/database/long-term-retention-overview

https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/long-term-backup-retention-configure

2.Auto-failover groups

The auto-failover groups feature allows you to manage the replication and failover of a group of databases on a server or all databases in a managed instance to another region. You can initiate failover manually or you can delegate it to the Azure service based on a user-defined policy. The latter option allows you to automatically recover multiple related databases in a secondary region after a catastrophic failure or other unplanned event that results in full or partial loss of the SQL Database or SQL Managed Instance availability in the primary region. A failover group can include one or multiple databases, typically used by the same application. Additionally, you can use the readable secondary databases to offload read-only query workloads. Because auto-failover groups involve multiple databases, these databases must be configured on the primary server. Auto-failover groups support replication of all databases in the group to only one secondary server or instance in a different region.

https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-overview?tabs=azure-powershell#terminology-and-capabilities

+General Guidelines

As you develop your business continuity plan,

1. you need to understand the maximum acceptable time before the application fully recovers after the disruptive event. The time required for application to fully recover is known as Recovery time objective (RTO).

2. You also need to understand the maximum period of recent data updates (time interval) the application can tolerate losing when recovering from an unplanned disruptive event. The potential data loss is known as Recovery point objective (RPO).

Different recovery methods offer different levels of RPO and RTO. You can choose a specific recovery method or use a combination of methods to achieve full application recovery.

Recovery method

3. Use auto-failover groups if your application meets any of these criteria:

· Is mission critical.

· Has a service level agreement (SLA) that does not allow for 12 hours or more of downtime?

· Downtime may result in financial liability.

· Has a high rate of data change and 1 hour of data loss is not acceptable?

· The additional cost of active geo-replication is lower than the potential financial liability and associated loss of business.