Always On Availability Groups
High availability disaster recovery (HADR) is an integral part of any organization’s data strategy. It is also an essential string to every DBA's bow. In an online world that operates 24 hours a day, going offline or losing customers' data cannot be tolerated. This course examines the features that Azure provides to help you make sure your SQL databases, whether they are managed in the cloud or on-premise, are not the point of failure in your systems.
High availability disaster recovery encompasses two fundamental concepts. Firstly, how to minimize the amount of time your databases will be offline in the event of unforeseen events like hardware failures, power outages, or any number of natural disasters. Secondly, it looks at how to minimize data loss when any of these events occur. It is one thing to be offline, but it is another thing to lose data that is already in your custody.
In this course, we will cover the full range of Azure options from turnkey to custom and do-it-yourself solutions. If you have any feedback relating to this course, feel free to reach out to us at firstname.lastname@example.org.
- Understand the concepts and elements of high availability and disaster recovery
- Learn about hyperscale databases and how they are used
- Learn how to combine on-premise with the cloud to replicate and sync your data
- Understand what always on availability groups are and how they operate
- Implement geo-replication and failover groups
- Learn about the wide range of options you have for backing up your databases with Azure
This course is intended for anyone who wants to implement high availability and disaster recovery procedures in place for their Azure SQL databases.
This is an intermediate to advanced course and so to get the most out of it, you should be familiar with SQL Server management studio, database operations like backup and restore, and T-SQL, and also have some familiarity with basic active directory concepts like users and permissions.
Hyperscale is the most recent addition to the Azure SQL database line up, and as the name suggests its focus is on scaling, which is up to databases 100 TB in size. In the context of high availability and disaster recovery, Hyperscale databases have some interesting features which can be best explained by looking at their architecture.
What is most noticeable is how functionality is separated. Instead of having a monolithic architecture of a database engine with files, the database engine functionality is separated from a page server function which in turn is separated from the actual data files. The page servers are storage engines that control between 128GB and 1TB of data and serve read requests up to the compute nodes while updating data via the log service.
One of the notable features of Hyperscale is that backups and restores aren’t done in the traditional database way, that is there are no full, differential, and log backups. Backups are snapshots of the data files which translates into very fast backup and restore operations, so similar to file snapshot backups when SQL Server is accessing data and log files in blob storage.
Microsoft says backups are almost instantaneous and even multi-terabyte databases can be restored in minutes instead of days or hours. This architecture means that the SQL Server engine is not burdened by backups or restores. Now you don’t have to create a secondary replica with Hyperscale, but if you do that will allow you to shed some of the read operations to those replicas.
I guess the question is how up to date are those replicas? Well, amazingly they are updated from the primary node in the order of milliseconds. This gives the recovery point objective a target of zero minutes and because of the snapshot restore the recovery time goal is less than 10 minutes. If you are only running a primary node, that is no replicas, if there is a failure hyperscale will automatically create a secondary replica for the recovery.
The creation of the secondary replica will add time to the recovery, so for optimal performance for both recovery and offloading read operations, you would want to run a replica. There is one significant caveat with all of this, that Hyperscale does not support Geo-replication, so a data center outage would present some significant business continuity issues. Having said that you can restore a Hyperscale database to a new region.
Course Introduction - Overview - High Availability and Disaster Recovery Concepts - Combining On-Premises with the Cloud - Always On Availability Groups - Failover in the Cloud - Database Backups - Course Summary
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.