You need to design a highly available Azure SQL database that meets the following requirements:
✑ Failover between replicas of the database must occur without any data loss.
✑ The database must remain available in the event of a zone outage.
✑ Costs must be minimized.
Which deployment option should you use?
To design a highly available Azure SQL database that meets the requirements of failover without data loss, availability during a zone outage, and cost minimization, the Azure SQL Database Business Critical option is the best deployment choice. Azure SQL Database Business Critical offers high availability by replicating data to additional nodes within a region, ensuring zero data loss during failover. It also supports automatic failover and cross-region replication, making it resilient to zone outages. While Azure SQL Database Serverless can be more cost-effective in some scenarios, it does not inherently provide the same level of high availability and data integrity guarantees as the Business Critical tier.
There are *16* variations of this question. Each of them offering different possible answers. The answer priority is as follows. If it exists then choose it. If it doesn't, proceeded to the next priority. Sometimes both appear in the same question, so make sure to select the higher priority. 1. Azure SQL Database Premium 2. Azure SQL Database Serverless 3. Azure SQL Database Business Critical
Thank you for the public service.
The given answer is correct
Azure SQL Database Business Critical is indeed more expensive compared to Azure SQL Database Serverless, but it provides the high availability and resilience required in the scenario. Azure SQL Database Serverless can be more cost-effective, particularly for infrequent, intermittent, or unpredictable workloads, because you pay for compute resources only when the database is active. However, it doesn't inherently guarantee the same level of high availability, resilience, and automatic failover without data loss across zones as the Business Critical tier. Azure SQL Database Business Critical best meets all the requirements, even though it may not be the most cost-effective option. If cost is a primary concern and the high availability requirements can be slightly relaxed, Azure SQL Database Serverless could be considered. However, based on the requirements stated, Business Critical is the recommended choice.
D is the answer. https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview?view=azuresql&tabs=general-purpose Serverless is a compute tier for single databases in Azure SQL Database that automatically scales compute based on workload demand and bills for the amount of compute used per second. The serverless compute tier also automatically pauses databases during inactive periods when only storage is billed and automatically resumes databases when activity returns.
https://learn.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla?view=azuresql&tabs=azure-powershell#general-purpose-service-tier-zone-redundant-availability Zone-redundant configuration for the General Purpose service tier is offered for both serverless and provisioned compute. This configuration utilizes Azure Availability Zones to replicate databases across multiple physical locations within an Azure region. By selecting zone-redundancy, you can make your new and existing serverless and provisioned general purpose single databases and elastic pools resilient to a much larger set of failures, including catastrophic datacenter outages, without any changes of the application logic.
Failover without data loss: Azure SQL Database Serverless does not provide the same level of high availability as Azure SQL Database Business Critical. With Serverless, automatic failover is not guaranteed, and there may be a risk of data loss during failover. Availability in the event of a zone outage: Azure SQL Database Serverless does not support the ability to span multiple availability zones. In the event of a zone outage, there is a possibility of downtime or unavailability of the database. Cost optimization: Azure SQL Database Serverless offers cost optimization benefits as it automatically pauses and scales based on usage. It is a suitable option for databases with unpredictable or intermittent workloads. However, cost optimization should not be the sole factor in choosing a deployment option if meeting high availability and data integrity requirements is crucial.
https://learn.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla?view=azuresql&tabs=azure-powershell#general-purpose-service-tier-zone-redundant-availability Zone-redundant configuration for the General Purpose service tier is offered for both serverless and provisioned compute.
Azure SQL Business Critical is the right answer imho. Serverless is cheap but does not guarantee failover without data loss
It should be C, this is an important concept for understanding business continuity from the perspective of Azure SQL. "The Basic, Standard, and General Purpose service tiers use the remote storage availability model for both serverless and provisioned compute." Meaning if the remote storage is down then there will be loss of data before you can provision/attach new storage. Hence it has to be business critical, reference link given below. https://learn.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla?view=azuresql&tabs=azure-powershell#:~:text=The%20Basic%2C%20Standard%2C%20and%20General%20Purpose%20service%20tiers%20use%20the%20remote%20storage%20availability%20model%20for%20both%20serverless%20and%20provisioned%20compute
Azure SQL Database Business Critical provides high availability with the capability to automatically replicate data to a secondary replica within the same region, ensuring zero data loss in the event of a failover. The deployment option also provides cross-region replication to ensure availability in case of a zone outage. Azure SQL Managed Instance Business Critical also provides high availability, but it is more expensive than Azure SQL Database Business Critical. Azure SQL Managed Instance General Purpose and Azure SQL Database Serverless do not provide the required level of high availability for this scenario. Therefore, Azure SQL Database Business Critical meets all the requirements while keeping costs minimized.
i think should be C, when talking about zero data loss during failover, shouldn't we use premium tier ? i've checked from the portal , cost from lowest to highest is SQL Preium < SQL BC < SQL MI BC.
it says no data loss... active geo replication is asynch, so would Serverless still count?
So reading the provided HA link for SQL DB it seems all four support Zone redundancy. The provided answer is the cheapest one. Is indeed Managed instance General purpose more expensive than SQL DB serverless?
Depends on the workloads but serverless automatically pause / resume so then you are not charged for Compute during this time. So when kind of workload is not specified we generally consider Serverless as the cheapest one.
I believe the correct answer is C. Serverless is available in vCore General Purpose and Hyperscale but both of them are not Always ON architecture. Business Critical in vCore and Premium in DTU use Always ON and no data loss. https://learn.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla?view=azuresql&tabs=azure-powershell
D. Azure SQL Database Serverless