Exam AZ-305 topic 3 question 17 discussion

Azure service or service tier "Azure SQL Managed Instance" Replication mechanis should be "Auto failover groups" Azure SQL Managed Instance is a fully managed, Azure-based version of SQL Server that can be used to host your on-premises SQL Server database in the cloud. It supports the deployment of multiple secondary, read-only replicas and can automatically replicate data between primary and secondary replicas. It also supports failover between primary and secondary replicas within a 15-minute RTO, which meets the requirement for the solution to have a recovery time objective of 15 minutes. Auto failover groups is a feature of Azure SQL Managed Instance that allows you to automatically failover between primary and secondary replicas in the event of an outage or failure. It supports the deployment of multiple secondary, read-only replicas and can automatically replicate data between primary and secondary replicas. It also supports failover between primary and secondary replicas within a 15-minute RTO, which meets the requirement for the solution to have a recovery time objective of 15 minutes.

1. Azure SQL DB 2. Active geo-replication https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison?view=azuresql#resource-limits Azure SQL Database -Read-only replicas Read scale with 1-4 high availability replicas or 1-30 named replicas 0 - 4 geo-replicas

WRONG 1. Azure SQL DB 2. Active geo-replication

* Multiple replicas, up to four read scale-out, zone-redundant HA * A choice of locally redundant (LRS), zone-redundant (ZRS), or geo-redundant (GRS) storage 1-35 days (seven days by default) retention, with up to 10 years of long-term retention available

Azure service or service tier: Azure SQL managed Instance This option supports high availability and automatic failover with multiple secondary, read-only replicas, making it suitable for enterprise applications needing high availability. Replication mechanism: Auto-failover groups Auto-failover groups enable automated failover for databases across Azure regions with a low recovery time, meeting the 15-minute RTO requirement.

Azure SQL Managed Instance Auto-failover groups

I think its Azure SQL DB and Active-Geo Replication The article below suggests that with correct BCP plan, Azure SQL DB with active geo failover can provide rto of less than 60 secs. Hence third scenario is also supported. The only confusing bit is the first requirement, i.e. whether they are talking about multiple read replicas within the service tier itself in which case Hyperscale will be the only option. refer to https://learn.microsoft.com/en-us/azure/azure-sql/database/business-continuity-high-availability-disaster-recover-hadr-overview?view=azuresql-db&preserve-view=true#rto-and-rpo

Given the requirements: Support the deployment of multiple secondary, read-only replicas. Support automatic replication between primary and secondary replicas. Support failover between primary and secondary replicas within a 15-minute recovery time objective (RTO). The best combination would be: Azure Service or Service Tier: Azure SQL Managed Instance or Azure SQL Database Business Critical (both can meet the high availability and read replica requirements) Replication Mechanism: Auto-failover groups

Zone redundancy and read replicas are 2 different things. Only the Hyperscale has read replicas and general, premium and business critical has zone redundancy, so box 1 should be Hyperscale.

I would say box 1 => Hyperscale as multiple read only replica as per below Hyperscale (Highly scalable compute and Storage): The widest variety of workloads, including those workloads with highly scalable storage and read-scale requirements. Offers higher resilience to failures by allowing configuration of more than one high availability secondary replica. The Hyperscale service tier is suitable for all workload types. Its cloud native architecture provides independently scalable compute and storage to support the widest variety of traditional and modern applications. Compute and storage resources in Hyperscale substantially exceed the resources available in the General Purpose and Business Critical tiers Box 2 => Active Geo-Replication

In the question, it's asking the service tier and also says multiple replica and read replica both. In option 1, only Hyperscale is the service tier. I am thinking, it should be Hyperscale as 1st option

Final Answer: 1. Hyperscale: For Multiple read only replicas 2.Active geo-replication

why do we need replication method here ? the question doesnt mention anything about multiple regions. all secondary replicas will be in sync automatically. also only hyperscale supports upto 4 read replicas Business critical and Premium only supports 1 read replica.

Look at Q51 on Page 37 (Standard page settings) - It's a very similar question and the chosen answer is 'An Azure SQL Database single Database'. However, the main difference is that it's specifically calling out 'Multiple Regions'. All this question states is for 'Primary and Secondary Replicas' and NOT Multiple Regions. That is why the chosen answer for this IS correct because Hyperscale only supports Primary and Secondary Replicas which is exactly what the question states as part of the requirement. This test is only testing on the parameters of the question itself, not what might be a better fit in a real-world/practical setting.

Please see this video: https://www.youtube.com/watch?app=desktop&v=-HTYxfXW3Fo John Savill explains clearly that: - Failover groups = Only 1 replica in different region (SQL + SQL MI) - Geo-replication = Up to 4 replicas (same region or not) (SQL MI is not supported) Thus correct answer is: - AZURE SQL DB - Active geo-replication

Answer to the repliation is Active Geo. The question states "Support the deployment of multiple secondary read only replicas" "What is the difference between auto-failover groups and active geo-replication? Auto-failover groups overview & best practices - Azure SQL ... Auto-failover groups support geo-replication of all databases in the group to only one secondary logical server in a different region. If you need to create multiple Azure SQL Database geo-secondary replicas (in the same or different regions) for the same primary replica, use active geo-replication." https://learn.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-sql-db?view=azuresql#:~:text=Auto%2Dfailover%20groups%20support%20geo,%2C%20use%20active%20geo%2Dreplication.

1 - SQL DB 2 - Active geo-replication SQL DB supports multiple geo-replicas. SQL MI only 1 geo-replica using auto-failover. source: https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison?view=azuresql#resource-limits