If you want to get started with Azure SQL, the boot camp is a great way to do it. You’ll get Azure experts like Anna Hoffman and Bob Ward, who is also a long time SQL Server expert, teaching you how to leverage the cloud for SQL Server. On day three, Anna does a couple of demos on the subject of HA that demonstrate the capabilities of the Azure platform. Other days have demos of other concepts as well.
Not only will you learn specifically about SQL Server in Azure, but this series is going to teach you other foundational concepts about Azure that are applicable no matter what you’re trying to do in Microsoft’s cloud. Things like cloud networking and the security aspect that needs to go into that networking, as well as how to give people access to resources in a secure way, are relevant for every part of Azure. With that, let’s dig into what day 3 had to offer.
Day 3 of the Azure SQL Boot Camp was all about availability. When done right, availability solutions keep your environment up and running when things don’t go right. Here are the key takeaways for Day 3 regarding high availability:
- Azure has you covered in terms of backup/restore.
- A full backup is taken weekly.
- A differential backup is taken ever 12 hours.
- Log Backups are taken every 5 to 10 minutes.
- Backups are stored on Read Access Geo-Redundant Storage, which means there are multiple copies of backups in differing locations. This protects you from losing the backups.
- Point in time restore is available.
- Backup integrity checks are peformed and there is even automated, random testing of those backups via automatically restoring some of them from time to time.
- Backup retention is by default at 7 days. You can go up to 35 days or even use the Long Term Retention service and keep backups for as long as 10 years.
- With Managed Instance a copy only backup option is available.
- On premises backups can be used to restore to a Managed Instance as part of a migration strategy.
Let’s be honest, how many of you have a backup strategy that is this solid? Some of you have databases that are in full recovery model with no log backups happening. Yikes! Don’t believe me? Check into your backup history information in msdb with a little T-SQL and you can verify for yourself whether your environment is up to snuff in this regard.
How many of you are writing backups to the same location as the data? And, the big question is this: How many of you are testing whether you can use your backups to perform a restore? Remember, just because you have backups doesn’t mean you can restore.
If you don’t have these sorts of things in place, you might be doing a lot of mopping up after a disastrous situation. “Clean up on aisle 4!”
- Azure has you covered when it comes to High Availability
- In the general purpose tier, Azure customers get a service that behaves like a SQL Server Failover Cluster Instance.
- Auto failover is possible and is handled by SQL Server and the Azure Service fabric.
- There is local SSD and local Tempdb with data files in premium storage.
- Backup files are geo-redundant.
- In the Business critical tier, Azure customers get Availability Groups behind the scenes – set up and configured for you automatically. With this service, you get 3 secondaries, only one of which has to harden transactions in order for the primary to commit transactions, and one replica for read only routing.
- In the hyperscale tier, you get snapshot backups taken by Azure, up to 4 secondaries and they can all be readable, as well as redundant data and log files through the log service and long term storage on Azure Standard Storage.
- Services like Zone Redundant Configuration, or Availability Zones, provide additional HA services for Azure SQL DB, but not in all regions. This method of HA is currently not available for Azure Managed Instance.
- Geo-replication and auto-failover groups are a central piece to HA in Azure. Geo-replication has multiple replicas and supports read scale out. With this service offering, you do have to update connection strings after a manual failover is initiated. It is asynchronous but you can use a system stored procedure to force synchronous commit prior to a failover. Geo-replication does not allow for failing over multiple databases together, and is available only for Azure SQL DB. Auto-failover groups can failover multiple databases at once with no changes to the application connection string. It supports read scale out and is available only in Managed Instance.
Again, let’s do a gut check. How many of you are running important or business critical SQL Servers on stand alone servers and your only real option for recovery if the machine has an issue is to create a new SQL Server and restore all your backups? In this scenario, that important or business critical SQL Server doesn’t have a second copy via log shipping or some sort of replication, mirroring or an Availability Group.
Go on…. think about it. List your important and critical business servers in a Word doc. Now examine the list and ask yourself what would happen if any of those servers died and couldn’t be recovered. Would some part of your business stop while you tried to build a new SQL Server and restore? If so, I think you just found an urgent action item to put at the top of your to do list. As you determine your solution, think about whether some form of SQL Server in Azure can give you what you need.
This is an important feature that is on by default in Azure. It is available in SQL Server 2019 on-premises, but it is not on by default. It uses a persisted version store to house old data rows. Those old data rows, and the most recent checkpoint, are used for a faster rollback. This addresses the problem of a long rollback from a long running transaction that was killed.
Well, that is the summary for Day 3 of the Azure SQL Boot Camp. There is more that was discussed, but remember, I am only trying to give you a summary of key takeaways. Enjoy as you dig into SQL Server in the Azure cloud!