9. High availability and disaster recovery

High availability and disaster recovery planning is essential to any database service or application deployment. In an on-premises SQL Server, database administrators have multiple options to configure high availability and disaster recovery solutions.

In this chapter, we will talk about high availability and disaster recovery options available for Azure SQL Database and SQL Managed Instance. Azure SQL Database and SQL Managed Instance come with built-in high availability and easily configurable disaster recovery solutions.

By the end of this chapter, you will be able to do the following:

• Describe the built-in high availability features in Azure SQL Database and Azure SQL Managed Instance
• Implement standard and active geo-recovery for Disaster Recovery (DR) solutions
• Implement standard and active geo-replication
• Implement the Accelerated Database Recovery feature
• Implement a failover group for Azure SQL...

High availability

High availability refers to providing service availability in case of any hardware, software, or network failure. Azure SQL guarantees up to 99.995% availability of service. Although Azure SQL Database and SQL Managed Instance are resilient to transitive infrastructure failures, such events might impact application connectivity. Applications can handle these failures by employing retry logic in code.

Azure SQL Database and SQL Managed Instance can quickly recover in the most critical situations, ensuring that your data is always available.

The availability of SQL Database and SQL Managed Instance depends on the service tier and underlying architecture model. Let's take a look at the high availability architecture models based on service tier configuration.

The basic, standard, and general-purpose service tier locally redundant availability model

This architecture is based on the separation of the compute and storage layers to ensure data availability...

Accelerated database recovery (ADR)

Accelerated database recovery, or ADR, is a new database recovery process that greatly increases availability and decreases database recovery time in scenarios such as crash recovery (database recovery in the event of a server/database crash) and long-running transaction rollback (for example, a large bulk insert or an index rebuild rollback).

An SQL database consists of data and a transaction log file. A data file contains the table data. A transaction log file keeps track of all the changes made to the data and the schema; for example, if there is an insert in a table, the transaction log file contains the insert statement and whether the insert statement was committed or not.

The standard database recovery process

To better understand ADR, let's first get an understanding of the standard database recovery process:

Figure 9.7: The recovery phase without ADR

Note

Image taken from https://docs.microsoft...

Disaster recovery

Disaster recovery (DR) refers to having business continuity during and after events that impact an Azure region, such as a natural disaster or hacking incident that terminates an entire Azure region.

DR for Azure SQL Database can be implemented through active geo-replication. An auto-failover group can be configured for Azure SQL Database and SQL Managed Instance as a business continuity solution. A failover group is designed to ease out the deployment and management of geo-replication databases at scale.

Active geo-replication

Active geo-replication uses Always On technology to asynchronously replicate data to a maximum of four readable secondaries in the same or any other Azure region. Active geo-replication is available across all performance tiers except Hyperscale. A typical active geo-replication environment is shown in Figure 9.17:

Figure 9.17: A typical active geo-replication environment

The DB 1 database is primarily...

Summary

In this chapter, you learned about the high availability and DR features of Azure SQL Database and SQL Managed Instance. High availability is built into Azure SQL Database and SQL Managed Instance and is managed by Microsoft, whereas DR can be achieved by configuring active geo-replication and failover groups as and when required.

You also learned about ADR, a new feature introduced with Azure SQL Database, SQL Managed Instance, and SQL Server 2019, which provides instant database recovery, transaction rollbacks, and aggressive log truncation.

Furthermore, you saw how zone-redundant configuration provides additional high availability by replicating the database in multiple Availability Zones within the same region.

In the next chapter, you will learn about how to monitor an Azure SQL Database and SQL Managed Instance using the Azure portal, dynamic management views, and extended events to help improve the performance of your application.