Introduction
High availability on Azure SQL database is very important because data is critical to the application. Following are two main reasons why we needed High availability on Azure SQL Database.
Make sure there are no data lose because of failure
Make sure Applications are not impacted because of server maintenance
Microsoft Azure provides following two models for High availability (HA) and both models provides different service tiers for application needs.
Standard High availability
Standard models provide Basic, Standard and General-Purpose service tiers. In this model Compute layer (Sql Server) runs on different nodes and Data Layer (Database files) stored in azure blob storage which provides HA and redundancy. If compute layer fails, service fabric cluster initiate another node for compute layer with minimal latency
Premium High availability
Premium models provide Premium, v-Core based business critical service tiers. In this model Compute layer (Sql Server) and Data Layer (Database files) are integrated in single nodes and replicated. Because of this will get very low latency. Premium tier provides Read scale out feature which enables read only operations redirects to separate secondary nodes.
Read Scale Out - Azure SQL High availability
Premium tier provides Read Scale out feature which enables you to offload Read only query to separate read only replica instance. This way read-only traffics isolated from read-write replica and will not affect their performance.
If we mention “ApplicationIntent=ReadOnly” as part of connection string, Application will be redirected to a read-only replica of that database.
Zone Redundancy - Azure SQL High availability
The zone redundant feature uses Azure Availability Zones to replicate databases across multiple zones within same region. Using zone redundancy feature, you can make your general-purpose single databases on High availability