Domain 5 of 5 · Chapter 3 of 3

Configure HA/DR for database solutions

Unlock the complete study guide + 25 practice exams totaling 1,500 questions.

Bundled into the existing Administering Microsoft Azure SQL Solutions premium course — no separate purchase.

14-day refund guarantee on one-time purchases and your first subscription charge.

Included in this chapter:

  • Two families of HA/DR mechanisms
  • Synchronous and asynchronous commit
  • Planned failover and forced failover
  • Creating an active geo-replication secondary
  • Sizing geo-replicas and the scaling order
  • Waiting for a geo-secondary after a critical commit
  • Geo-failover commands and removing a geo-secondary
  • Chained geo-secondaries and standby replicas
  • Geo-replication inside a network security perimeter
  • Failover group membership: SQL Database vs Managed Instance
  • Creating a SQL Database failover group with PowerShell
  • Preparing the Managed Instance failover partner
  • Failover policy: customer managed or Microsoft managed
  • Failover group listeners and read intent
  • Configuration that replication does not copy
  • Failover group size and elastic pool density
  • Managed Instance failover groups and backups
  • Availability group replicas and the WSFC
  • Preparing instances, databases and endpoints
  • Initializing secondaries: manual or automatic seeding
  • Requiring synchronized secondaries to commit
  • WSFC quorum and how to inspect it
  • Conditions for automatic availability group failover
  • Flexible failover policy versus session timeout
  • Planned and forced availability group failover
  • Listener connectivity on Azure VMs
  • Read-only routing through the listener
  • Availability groups on Linux with Pacemaker
  • Strict encryption for cluster connections
  • Managed Instance: built-in HA and the link
  • FCI shared storage options on Azure VMs
  • Installing a failover cluster instance
  • FCI failover, quorum and health detection
  • Configuring log shipping
  • Log shipping restore mode and restore delay
  • Failing over to a log shipping secondary
  • Monitoring active geo-replication
  • Monitoring availability groups with DMVs
  • The alwayson_health Extended Events session
  • Managed Instance failover group status
  • The log path from commit to redo
  • Troubleshooting an exceeded RPO
  • Troubleshooting an exceeded RTO
  • Resuming suspended data movement
  • Automatic page repair
  • Troubleshooting a stale log shipping secondary
  • CDC and change tracking after AG failover
  • Replication publishers in an availability group
  • Transactional replication after MI geo-failover
  • HA/DR mechanism choices by requirement or symptom

HA/DR mechanisms compared by what they protect and how clients follow a failover

Configuration aspectActive geo-replicationFailover groupsAlways On availability groupsFailover cluster instancesLog shipping
Runs onAzure SQL DatabaseAzure SQL Database or Azure SQL Managed Instance, the same platform at both endsSQL Server, including Azure VMs; Managed Instance only through the Managed Instance linkSQL Server on WSFC (Windows) or Pacemaker (Linux) nodes, including Azure VMsSQL Server, including Azure VMs
Unit that fails overOne databaseSQL Database: the databases you add; Managed Instance: all user databasesThe group's availability databases, togetherThe whole SQL Server instanceOne primary database
Copies of the dataUp to four geo-secondaries, asynchronousA geo-secondary on the partner server or instance, asynchronousUp to eight secondary replicas, synchronous or asynchronous commitOne copy on shared storageSecondary databases restored from log backups
Automatic failoverNo; you start failover or forced failoverOnly under Microsoft-managed policy, for regional outages, after a grace period of at least one hourYes, with synchronous commit, automatic failover mode, a synchronized target and cluster quorumYes, the cluster moves the instance to another nodeNo
Readable secondaryYesYes, through the read-only listenerYes, when the replica allows read connectionsNoOnly in standby mode, between restores
Client endpoint after failoverChanges to the new primary's serverListener names stay the sameAvailability group listener stays the sameFCI network name stays the sameRedirect clients yourself

Decision tree

SQL Server (VM or on-premises)at either end?NoYesBoth endsmanaged instances?YesNoFailovergroupBoth endsAzure SQLDatabase?NoYesPair notsupportedFail overas a unit, ora stablelistener?YesNoFailovergroupActive geo-replicationOther end amanaged instance?YesNoManagedInstancelinkOther endalso SQLServer?NoYesPair notsupportedWholeinstance,one copy?YesNoFailoverclusterinstanceAutomaticfailover orlistener?YesNoAvailabilitygroupLogshipping

Cheat sheet

  • Creating a geo-secondary seeds a database on a different logical server before ongoing replication
  • Active geo-replication does not require the primary and secondary to share a pool layout
  • Waiting for copy synchronization can protect selected committed work without changing the replication mode
  • Promoting a geo-secondary and removing its replication link target different servers
  • Geo-replicated read distribution can extend beyond the direct-secondary limit through chaining
  • Network security perimeter admission checks do not stop established geo-replication data movement
  • Availability group creation requires prepared instances, databases, and transport endpoints
  • A manually prepared secondary joins its availability group while still unrecovered
  • Automatic seeding creates secondary database files but still needs explicit authorization and capacity
  • Automatic Windows availability group failover requires more than synchronous commit
  • Availability group read-only routing requires both server configuration and client read intent
  • Azure listener routing and client reconnection must match the subnet topology
  • Managed Instance exposes a link workflow rather than user administration of its internal availability groups
  • Linux availability group HA uses external cluster control rather than Windows automatic mode
  • Requiring synchronized secondaries can stop primary commits when acknowledgers are unavailable
  • Manual availability-group promotion must match target readiness and cluster ownership control
  • Availability-group server-health policy is different from replica session timeout
  • Strict WSFC connections use the configuration scope of the protected SQL resource
  • SQL Database and Managed Instance use different failover-group membership boundaries
  • Managed Instance failover partners require a coordinated cross-region topology
  • Customer-managed and Microsoft-managed failover policies have different response boundaries
  • Failover-group listeners preserve role-based endpoint names through a geo-failover
  • Read-only listener failover is disabled by default to protect primary workload capacity
  • A geo-secondary needs its own short-term retention configuration for its future primary role
  • Failover-group secondary zone settings inherit differently for Hyperscale and other architectures
  • Failover-group recovery predictability depends on database count and shared-pool capacity
  • Managed Instance failover-group PITR history belongs to the instance that was primary at the requested time
  • SQL Database failover-group commands distinguish creation, enrollment, inspection, and promotion
  • Azure shared disks make FCI storage shareable through explicit attachment and reservation support
  • FCI shared-storage options have different placement and SQL feature constraints
  • FCI setup joins nodes to one clustered instance rather than creating independent SQL copies
  • FCI recovery needs surviving quorum and storage as well as a new service owner
  • Log shipping depends on a coordinated backup, copy, and restore pipeline
  • Log-shipping restore mode and delay determine secondary usability and freshness
  • Log-shipping failover requires explicit recovery and redirection rather than automatic promotion
  • Geo-replication monitoring must distinguish data progress from provisioning progress
  • Availability group monitoring separates runtime roles from database synchronization and cluster readiness
  • The alwayson_health event session provides a history of availability group state changes
  • Managed Instance failover-group readiness depends on successful seeding rather than a status label alone
  • WSFC quorum health must be inspected separately from database synchronization
  • Managed SQL replica health must interpret synchronization state in the context of commit mode
  • Asynchronous RPO problems arise before transaction log is hardened on the secondary
  • A large redo queue can delay recovery even after log records are safely received
  • Read-only reporting can block DDL redo through schema locks
  • Resuming an availability database must respect where its data movement was suspended
  • A geo-secondary must keep up with the primary before it can be a reliable recovery target
  • Healthy database copies still need compatible server-level recovery metadata
  • Log-shipping troubleshooting must separate data-pipeline failures from monitor-connection failures
  • Automatic page repair needs a successful partner-page replacement rather than healthy replication alone
  • Availability-group role changes require change-processing jobs and consumers to follow the correct role
  • An AG-protected Publisher needs listener-based Distributor redirection
  • Managed Instance geo-failover requires role-specific transactional replication recovery

Unlock with Premium — includes all practice exams and the complete study guide.

References

  1. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server
  2. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  3. https://learn.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview
  4. https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-sql-db
  5. https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server
  6. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-feature-overview
  7. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups
  8. https://learn.microsoft.com/en-us/azure/azure-sql/database/transact-sql-tsql-differences-sql-server
  9. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-overview
  10. https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server
  11. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups
  12. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql
  13. https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/fail-over-to-a-log-shipping-secondary-sql-server
  14. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-and-failover-modes-always-on-availability-groups
  15. https://learn.microsoft.com/en-us/powershell/module/az.sql/switch-azsqldatabasefailovergroup
  16. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-azure-sql-database
  17. https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-failover-ha
  18. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server
  19. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-geo-replication-link-status-azure-sql-database
  20. https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-configure-sql-db
  21. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-wait-for-database-copy-sync-transact-sql
  22. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/failover-group-sql-mi
  23. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/failover-group-configure-sql-mi
  24. https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server
  25. https://learn.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr
  26. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/troubleshoot-always-on-availability-groups-configuration-sql-server
  27. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group
  28. https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/the-database-mirroring-endpoint-sql-server
  29. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-endpoint-transact-sql
  30. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-extended-events
  31. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatic-seeding-secondary-replicas
  32. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server
  33. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/join-a-secondary-database-to-an-availability-group-sql-server
  34. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-availability-replica-states-transact-sql
  35. https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-ha
  36. https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/wsfc-quorum-modes-and-voting-configuration-sql-server
  37. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-cluster-members-transact-sql
  38. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-hadr-cluster-transact-sql
  39. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/sql-server-always-on-database-health-detection-failover-option
  40. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/flexible-automatic-failover-policy-availability-group
  41. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover
  42. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-overview
  43. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-availability-group-listeners-transact-sql
  44. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-availability-group-listener-ip-addresses-transact-sql
  45. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tcp-listener-states-transact-sql
  46. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server
  47. https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-rhel
  48. https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/connect-with-strict-encryption
  49. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-server-configuration-transact-sql
  50. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server
  51. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-extend-availability-group
  52. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-preparation
  53. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-azure-shared-disks-manually-configure
  54. https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/install/create-a-new-sql-server-failover-cluster-setup
  55. https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-shared-disk-cluster-configure
  56. https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/failover-policy-for-failover-cluster-instances
  57. https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sql-server
  58. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/business-continuity-high-availability-disaster-recovery-hadr-overview
  59. https://learn.microsoft.com/en-us/azure/azure-sql/database/standby-replica-how-to-configure
  60. https://learn.microsoft.com/en-us/sql/relational-databases/system-views/sys-geo-replication-links-azure-sql-database
  61. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-operation-status-azure-sql-database
  62. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql
  63. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-hadr-database-replica-cluster-states-transact-sql
  64. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-database-replica-states-azure-sql-database
  65. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/troubleshoot-availability-group-exceeded-rpo
  66. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/troubleshoot-availability-group-exceeded-rto
  67. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups
  68. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/resume-an-availability-database-sql-server
  69. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server
  70. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-auto-page-repair-transact-sql
  71. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability
  72. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-redirect-publisher-transact-sql
  73. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-transactional-overview