Configuring High Availability and Disaster Recovery for Database Solutions: Exam DP-300 Study Notes

In this blog post, we will explore the considerations for properly deploying high availability and disaster recovery (HADR) solutions for database systems on Infrastructure-as-a-Service (IaaS) platforms like Azure. We will compare the process of using an Availability Group (AG) versus a Windows Server Failover Cluster (WSFC) and discuss the specific considerations for AGs in Azure compared to on-premises deployments. We will also cover the differences in implementing HADR solutions for Platform-as-a-Service (PaaS) solutions, which are configured within the database or database server rather than at the Azure level. By the end of this blog, you will have a clear understanding of what is required to deploy IaaS database platform solutions in Azure.

Overview of Configuring a Windows Server Failover Cluster in Azure

A Windows Server Failover Cluster (WSFC) is a group of servers that work together to provide high availability for applications and services. In Azure, a WSFC is used to ensure that an Availability Group (AG) or Failover Cluster Instance (FCI) remains up and running in case of a failure or disruption. To set up a WSFC in Azure, you must decide what to use for a witness resource, which is a core component of the quorum mechanism that helps ensure that the WSFC stays up and running. It is recommended to use a cloud witness, which is fully Azure-based and works especially well for solutions that span multiple Azure regions or are hybrid. You should also consider whether to cluster the Microsoft Distributed Transaction Coordinator (DTC or MSDTC), which is used by some applications, and whether to use AD DS and DNS. The WSFC requires a unique name and an IP address, and it is recommended to use a single virtual network card (vNIC). Before setting up a WSFC, you must enable the failover clustering feature on every node that will participate in the WSFC, and then run cluster validation to ensure that the configuration is suitable for clustering. Once the WSFC is set up, you can configure the AG or FCI to use the WSFC for high availability.

Key Considerations for Deploying an Always-On Availability Group in Azure

When setting up an Always-on availability group (AG) in Azure, you must consider the underlying cluster, storage configuration, and the Azure load balancer. It is important to ensure that the AG feature is enabled, and that the listener is properly configured, including setting up an internal load balancer and a probe port. If you have a multi-subnet configuration, you will need to set up a load balancer in each subnet and associate the probe port with the IP resource for that subnet in the Windows Server Failover Cluster (WSFC). You can use the PowerShell cmdlet Test-NetConnection to verify that the listener is configured correctly. To ensure that the AG is working properly, you should test failover and failback, as well as monitoring and troubleshooting the AG. It is also important to consider security, backup and restore, and performance and scalability when configuring an AG in Azure.

Exploring the Features and Uses of Temporal Tables in Azure SQL Database

Azure SQL Database and Azure SQL Managed Instance allow you to track and analyze changes to your data using temporal tables. These tables have special properties and a corresponding history table, which can be used to recover data that has been deleted or updated. Temporal tables have various uses, including auditing, historical trend analysis, anomaly detection, data protection, and tracking changes in data warehousing dimensions. However, storing historical data for a long time or performing heavy data changes can increase the size of the history table and affect storage cost and query performance. To manage and delete historical data, you can use options such as Stretch Database, table partitioning, custom cleanup scripts, and retention policies. There are also some limitations to consider when using temporal tables.

Increasing Availability with Active Geo-Replication for Azure SQL Database

Active geo-replication is a feature of Azure SQL Database that allows you to create a secondary database replica in another region that is kept up to date asynchronously. This replica is readable and can be used to programmatically or manually failover primary databases to secondary regions in the event of a major disaster. Active geo-replication is not supported in Azure SQL Managed Instance, and all databases involved in a geo-replication relationship must have the same service tier. It is recommended that the compute size of the secondary replica is the same as the primary to avoid replication overhead. Cross subscription geo-replication is a feature that allows you to configure a secondary replica on a different subscription than the primary database, but it is only available programmatically.

Using Auto-Failover Groups for Disaster Recovery in Azure SQL Database and Managed Instance

Auto-failover groups in Azure SQL Database and Azure SQL Managed Instance provide high availability for databases by replicating them to another region and allowing for failover in the event of a failure. Auto-failover groups have a read-write and read-only listener and can be configured with either automatic or read-only policies. Unplanned failovers may result in data loss, but this can be minimized by increasing the GracePeriodWithDataLossHours setting. One auto-failover group can contain multiple databases, which are automatically created on the secondary through a process called seeding. It's important to plan for the time it takes to seed large databases and to consider factors such as network speed.

Summary

In summary, it's important to carefully consider your high availability and disaster recovery (HADR) needs when deploying in Azure. This includes considering your recovery time and recovery point objectives, as well as the platform you are using. There are different options available for deploying HADR solutions in Azure, depending on whether you are using Infrastructure as a Service (IaaS) or Platform as a Service (PaaS). It's important to choose the solution that best meets your needs and requirements.