Introducing Azure SQL Database

Azure SQL High Availability

Your browser needs to be JavaScript capable to view this video

Try reloading this page, or reviewing your browser settings

This video segment walks the learner through building out a high available SQL Azure setup across multiple Azure regions.

Keyword

  • Azure SQL HA high availability disaster recovery cluster replication

About this video

Author(s)
Peter De Tender
First online
09 August 2019
DOI
https://doi.org/10.1007/978-1-4842-5276-5_10
Online ISBN
978-1-4842-5276-5
Publisher
Apress
Copyright information
© Peter De Tender 2019

Video Transcript

Hey, there. Welcome back in this Apress Azure SQL course, where in this last section, I want to talk about high availability options out of Azure SQL. This section will position two main high availability concepts that you can generate using Azure SQL.

The first one is geo-replication, where we’re going to replicate databases across multiple Azure regions. And the second concept called Azure SQL failover groups, similar to having a SQL always on across multiple virtual machines, if you want. Now, let me highlight geo-replication, one of the newer features in Azure SQL.

So we start with a running single instance database, where it’s high available in a single Azure region, but for some customers, they don’t want to take the risk for losing that one region. And the only option for better high availability is expanding to a second region. And that’s exactly what geo-replication does.

So you start with creating a secondary database in another Azure region. Next to that, once everything is up and running being replicated, you can define the failover from primary to secondary, if that failover is needed. So it’s mainly a manual process.

The secondary server can have a different admin accounts, which might be interesting for multi-regional administration, and also having an alternate pricing tier. So you could build your primary server as being the business critical, high-performing server. But in case of a disaster, in case of a failover to the secondary site, you could fall back to a smaller pricing tier, because not all your users or customers are connecting to that database, and not really needing all that performance.

So let me show you in a demo what it looks like to build that geo-replication, how the configuration works, and how to run that failover. I can select a geo-replication here. So the portal is going to show you a nice map from the world, and the only thing you need to do is selecting the second region.

Now my initial database was deployed in West Europe, and as you can see, it offers me to deploy alternate database in North Europe. It’s partly recognizing the Azure regions, and also taking compliance regulations into account. So I select my second region, North Europe, where I need to provide some information about the server I want to use.

Since I don’t have a server yet, I’m going to deploy a new one. If you watched one of the first videos, this should already be pretty straightforward. The North Europe PDT SQL Azure, where again, the name needs to be unique providing my admin accounts. This could be different ones than the one from the primary, but I’m going to make it easy for now.

So I got my server object, my secondary type will be readable, and the pricing tier is going to recognize the same one just to keep it easy. And that’s basically it from a configuration perspective. So selecting the database level geo-replication, specifying the second region, and waiting till the database has been created, the server instance has been created, and the data is in sync.

Now the other option we have in meantime is creating a failover group. Because the downside from this solution here is that I’m still connecting to individual SQL Server namespaces. So I have MySQL namespace in West Europe. I’ve got a different one in North Europe. So from a application perspective, it’s not fully, I would say, smooth for failing over from one region to the other.

So the other option I have is creating a failover group. And that happens on SQL Server level similar to your on prem, SQL always on cluster. Because what we can do here is creating a new failover group. But this time I’m going to create like a new named instance.

This could be the PDT failover group. It’s going to check what will become my secondary server. I can take my North one. Where this time, the read write failover policy is defined as automatic. I could switch back to manual, but since I rely on MySQL cluster high availability, this is probably what I want to do. And next to that, I can integrate my different databases.

Where now, since I have this shared single namespace, I could manage that environment just by connecting to that shared namespace. And in case of a disaster where one of the servers that’s hosting my database would not be available anymore the Azure SQL fail over group is going to detect it and connecting me to my replica, if you want, on the other side of the world.

And that’s pretty much it from a configuration perspective. So failover groups from a server, similar to creating in like an always on cluster in virtual machine terms, and using the geo-replication to actually allow you to replicate databases across multiple Azure regions. So in this section, I talked about the different failover options that we have available in our Azure environment.