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 aspect | Active geo-replication | Failover groups | Always On availability groups | Failover cluster instances | Log shipping |
|---|---|---|---|---|---|
| Runs on | Azure SQL Database | Azure SQL Database or Azure SQL Managed Instance, the same platform at both ends | SQL Server, including Azure VMs; Managed Instance only through the Managed Instance link | SQL Server on WSFC (Windows) or Pacemaker (Linux) nodes, including Azure VMs | SQL Server, including Azure VMs |
| Unit that fails over | One database | SQL Database: the databases you add; Managed Instance: all user databases | The group's availability databases, together | The whole SQL Server instance | One primary database |
| Copies of the data | Up to four geo-secondaries, asynchronous | A geo-secondary on the partner server or instance, asynchronous | Up to eight secondary replicas, synchronous or asynchronous commit | One copy on shared storage | Secondary databases restored from log backups |
| Automatic failover | No; you start failover or forced failover | Only under Microsoft-managed policy, for regional outages, after a grace period of at least one hour | Yes, with synchronous commit, automatic failover mode, a synchronized target and cluster quorum | Yes, the cluster moves the instance to another node | No |
| Readable secondary | Yes | Yes, through the read-only listener | Yes, when the replica allows read connections | No | Only in standby mode, between restores |
| Client endpoint after failover | Changes to the new primary's server | Listener names stay the same | Availability group listener stays the same | FCI network name stays the same | Redirect clients yourself |
Decision tree
Cheat sheet
Unlock with Premium — includes all practice exams and the complete study guide.
References
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
- https://learn.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-sql-db
- https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-feature-overview
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups
- https://learn.microsoft.com/en-us/azure/azure-sql/database/transact-sql-tsql-differences-sql-server
- https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-overview
- https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql
- https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/fail-over-to-a-log-shipping-secondary-sql-server
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-and-failover-modes-always-on-availability-groups
- https://learn.microsoft.com/en-us/powershell/module/az.sql/switch-azsqldatabasefailovergroup
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-azure-sql-database
- https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-failover-ha
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-geo-replication-link-status-azure-sql-database
- https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-configure-sql-db
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-wait-for-database-copy-sync-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/failover-group-sql-mi
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/failover-group-configure-sql-mi
- https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server
- https://learn.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/troubleshoot-always-on-availability-groups-configuration-sql-server
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group
- https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/the-database-mirroring-endpoint-sql-server
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-endpoint-transact-sql
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-extended-events
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatic-seeding-secondary-replicas
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/join-a-secondary-database-to-an-availability-group-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-availability-replica-states-transact-sql
- https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-ha
- https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/wsfc-quorum-modes-and-voting-configuration-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-cluster-members-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-hadr-cluster-transact-sql
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/sql-server-always-on-database-health-detection-failover-option
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/flexible-automatic-failover-policy-availability-group
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover
- https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-availability-group-listeners-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-availability-group-listener-ip-addresses-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tcp-listener-states-transact-sql
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server
- https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-rhel
- https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/connect-with-strict-encryption
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-server-configuration-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-extend-availability-group
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-preparation
- https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-azure-shared-disks-manually-configure
- https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/install/create-a-new-sql-server-failover-cluster-setup
- https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-shared-disk-cluster-configure
- https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/failover-policy-for-failover-cluster-instances
- https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sql-server
- https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/business-continuity-high-availability-disaster-recovery-hadr-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/standby-replica-how-to-configure
- https://learn.microsoft.com/en-us/sql/relational-databases/system-views/sys-geo-replication-links-azure-sql-database
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-operation-status-azure-sql-database
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-hadr-database-replica-cluster-states-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-database-replica-states-azure-sql-database
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/troubleshoot-availability-group-exceeded-rpo
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/troubleshoot-availability-group-exceeded-rto
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/resume-an-availability-database-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-auto-page-repair-transact-sql
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-redirect-publisher-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-transactional-overview