This chapter teaches you about the built-in high availability in Azure SQL Database. This lesson also teaches you how to implement a Disaster Recovery solution using Standard and Active Geo-replication.
By the end of this chapter, you will be able to:
- Describe built-in high availability features in SQL Database
- Implement the Standard and Active Geo-Recovery for disaster recovery solutions
- Implement Standard and Active Geo-replication
High availability refers to providing business continuity when the physical server hosting the Azure SQL Database fails.
As discussed in Chapter 1, Microsoft Azure SQL Database Primer, an Azure SQL Database is hosted on three servers – one primary and two secondary servers:
Figure 8.1: Azure SQL Database is Hosted on Three Servers
The
toyfactory database consists of three servers; one primary and at least two secondary servers. The Azure SQL Server is just a logical server name used for connection. When an application sends a connection request, the gateway finds out the current primary server and routes the request to the primary server.
The primary and secondary servers are within the same datacentre and together form a quorum-set.
All read and writes are made to the primary server. Azure SQL Database uses a quorum-based commit method that makes sure that the data is hardened at the primary server and replicated to at least one secondary server before the transaction commits.
Although the chances of physical machine failure are very low, in a Microsoft Azure infrastructure with so many systems, the machines may fail:
Figure 8.2: Machine Failure in an Azure Infrastructure
Let's say that the primary server fails because of a hardware failure. The system automatically detects the failure and performs an automatic failover to one of the secondary servers. The process of detecting a failure and failover to the secondary server takes no longer than 30 seconds. However, during the failover time, the database is unavailable for new connections, and on-the-fly connections are terminated.
It is therefore important to implement retry logic in the application:
Figure 8.3: Creation of Secondary Server
Once the failover is done, the system automatically creates a new secondary server and adds it to the quorum-set.
As shown in the preceding diagram, the secondary server S1 is promoted to primary as the primary server (P) failed, and a new secondary server, S3, is provisioned and made part of the quorum-set.
This is how high availability is built into Azure SQL databases, and it is completely transparent to the users.
The high availability is based on the popular Always On technology which is also available in on-premises SQL Servers. However, you would have to configure, manage, and maintain Always On in an on-premises environment. In Azure SQL Database, it's configured, managed, and maintained by Microsoft.