DP-300 Microsoft Azure Database – Recommend an HADR strategy for a data platform solution Part 2

3. 94. configure replication – Active Geo-replication

In this video, we’re going to configure replication. And I’m going to go to my SQL database here. And on the left hand side we’ve got on the data management Replicas. Now, this keeps moving. It was in the settings, for instance, in 2020, and then it was called Geo replication early in 2021. Now. It’s called replicas. So you can see no Replicas found and indeed read.

What we’re seeing here is quite different to what was there in 2020. So this bit keeps changing. So I’m going to create a new Replica. And you can see that we have here the initial, the primary database details. All folding can’t change them. And then we’re going to have our secondary, our Replica. So I’m going to create a new server here. So this is going to be the DP 300 database Replica, and it’s going to have my previously used password and username, and I can give it a different location.

Now, you can create readable secondary databases in the same or different regions. However, if it’s in the same region, it’s not that good for disaster recovery. Let’s say you have got your primary and your secondary in East US, and then something happens, a hurricane. Well, both of them could be taken offline in exactly the same way if they’re in the same region. So you may well want to consider different regions. And this is a great way for being able to have your database replicated around the world. No need for huge infrastructure. It’s now just click and it can be done.

So the database name remains the same. Because it’s a Replica, the compute and storage needs to be minimum what you’ve got in the primary. So in this case, it’s going to be basic. And in fact, I won’t actually be able to change this particular one, I’ll just need to leave it as a basic for this. Now, it’s strongly recommended that you have at least the same compute size as the primary, which is what I’ve been saying about previously. So we’ll click apply and we can do any of the other stuff. So networking allow Azure Services and resources to access this server.

Yes, there will. I’m not going to start a free trial and I’m just going to create. So, while it’s creating, let’s talk a bit more about this Replica. So this is for Azure SQL database only, so it’s not for Azure SQL managed instance. As we will see, azure SQL managed instance can use auto fail of groups, but not Replicas. So up to four secondaries are supported in the same or different regions, and they can be part of an elastic pool. They can be used for read only access queries. And it replicates changes by streaming database transaction logs. So that’s different to transactional replication, which we’ll look at much later, which replicates changes by executing DML commands like insert, update, delete. Here it’s the database transaction logs, just like you have got for backups and you can fill over to secondary databases. So you can use it for database migration from one server to another with minimum downtime. So all you need to do is have this second copy and then you’re fine.

You just say okay, everybody have a look at the second copy. You can create an extra secondary as a failed back copy during application upgrades. So if the application upgrade doesn’t work, then you can fail back with this secondary. As I said before, it uses asynchronous replication so that the transactions are committed on the primary before being replicated. It’s not waiting for confirmation from the secondaries and you can have planned and unplanned failover, right? So I’m going to pause the video until this secondary is created so the deployment is complete.

So I can go to this results. So this is my database replica in the east US. Now if I go back to my SQL database, my original the primary, then you can see if I go down to replicas that we have this primary and this georeplica in the US and it is readable. So what I’m going to do is connect first of all to my primary in SSMS. So if I connect there is my primary. So there is a password so you can see what tables and other things that I have got. Now, what I can also do is go to my geor replica and unlike my primary which has this server name, we have a different server name here. So DP 300 database replica. So if I connect to that using the same login and password, but the login username is different, so they are now connected to this second database.

So what I’m going to do is firstly note that I can actually query this replica. So if I have a new query, I can go select star from the address and so that reduces the workload from the primary. So with the primary again, I can read but I can also write. So if I try to do an update statement. So update this address table set city equals buffalo with one L, where city equals buffalo or buffalo with two LS. So if I try to do this on the replica, then I fail to update it because the database is read only. Now if I just have here where city like bo and then the percentage sign, so I’m just getting the four items which are bothal or barthel, I can see that they exist in my primary. I will update my primary so that we lose one of the LS. Let’s have a look at the latest. So you can see we are one L down. Let’s have a look at the latest in the secondary. And it’s that quick or it can be that quick.

So it did take a few milliseconds for the data to fly from the primary to the secondary, but that was extremely quick. So if I do it going back so again from the primary to the secondary and see how quickly I can get it done. You can see even in that split second that it took me to click to another query and say execute, it was done. However, you can’t guarantee that it will be done that quickly. It uses asynchronous replication, so it sends it through across, in this case from the United Kingdom to the United States. It might encounter network traffic. Transactions are committed on the primary before being replicated on secondary. So this is replication. You can create up to four readable secondary databases from a primary. You can also create additional secondaries from the secondary that you just created and you saw how quick it was to execute the commands.

And you can use read-only access on these geo replicas so that can take some of the load off the primary. Now, let’s do one more thing. Let’s fail over. And failing over is very easy to do. All you need is to go to the stop dot and we got free options pin to dashboard. So you can have dashboards in a dur, stop replication. So stop the data going through and a forced failover. So I’m going to do the forced failover. So it could be that there was a major problem in the UK south, maybe there was a hurricane coming into the UK, so let’s just read it. Forced failover will switch this replica to be the primary role. The old primary will automatically become the new secondary once it’s online, all TDs sessions, that is a tabular data stream, so that’s a protocol for accessing. It will be disconnected and this operation, because it’s a forced failover, may call data loss. I show you want to proceed. I’m going to say yes and notice what happens. Request submitted.

So it might take a little time to do so. I’m just going to pause the video until it’s done. And now we can see the replica state is failover for the geo replicator and pending for the primary. Right, so now that’s done, you don’t actually receive a notification that it’s been complete, but you can see that the replica is now the primary and the geo replica is now what used to be the primary and is now the secondary. And you can connect, but don’t getty if you’re connecting to the primary, you’ve got a different primary to connect to.

And if we’re talking about disaster recovery, then the old primary would not yet necessarily be online. So you’d have to use a new connection string to connect to your current primary. So this is how you can create Replicas. You go to your database, you go to Data Management Replicas and you can just create a Replica. You can create up to four read only Replicas which can be used for read only access queries. You can have additional secondaries based on those georeplicas and you can fill over two secondary databases, either planned or unplanned.

4. 88, 89, 96. configure auto-failover groups and test HA by using failover

Now in this video we’re going to configure auto failover groups and this is used in both Azure SQL database and managed instance. So what we’re going to do now is go back not to our database but to our server. So we currently got the Replica as being the primary at the moment. So I’ll go to that server and under data management for the server, not the database we’ve got failover groups. So failover groups designed to manage replication, connectivity and failover of a set of databases. So I will add a new group. So this is DP 300 failover and I can select a server so I’ll select the Replica. But notice what it says, you cannot choose a server from the same region. It happens to be exactly the same server but it’s just worth highlighting you can’t choose a server from the same region so I’m going to choose a different server, so I will choose the agent database that we’ve previously set up.

Next, let’s have a look at the read write failover policy. It can be automatic or it can be manual. Now, automatic means you can do manual failover as well but then look at this, a read write grace period in hours. So you remember that when we were talking about RPO RTO I was saying that the recovery time objective starts at 1 hour. So part of this is because you’re in the cloud, certain problems might happen and they might just be very tempering get resolved where we don’t necessarily want to fail over if there’s a minor problem. But secondly, you remember that all of this is done using asynchronous data transfer. Well, what happens if all of the data hasn’t been received? So let’s say that server A goes down, then we will then have an hour for all of the data that has been changed to go to server B before B fails over. So I’ve got databases within a group, I haven’t configured any databases so let’s configure a database and you can see I’m going to configure the DP 300 database so I will select that. So you should use auto failover groups when it’s mission critical, when your SLA your service level agreement doesn’t allow for twelve or more hours of downtime.

Remember, GeoRestore has a recovery time point of 12 hours when you don’t want to lose up to an hour of data. Like at Georgia, auto failover groups have a recovery point objective of 5 seconds. But do bear in mind you could be going the other way and finding out that actually fail of groups is too expensive for what you want. So you can see that the fail of group is being deployed. So I’ll just pause the video until it’s done and you can see it’s just taken a few seconds. So we have a failover group DP 300 from a primary server to a secondary server.

We have a policy whether it’s automatic or otherwise and we have a grace period of 1 hour. And you can see when I click on it that we have got this in more visual form. So the primary is in blue and the data is being transmitted to the green, the secondary. Now, I could put in one particular database. I could put in 100 databases in the auto failover group. So we have here a read write listener point and a read only listener endpoint. So these are used rather than the individual endpoints for the individual databases. So if I try to connect to this endpoint so here I am in SSMS and these are the credentials that I use to connect to the database replica. And you can see I can connect. So I will disconnect that and instead I will connect to this new failover. So if I paste it in there, so there is my failover server name and connect using the same credentials, then I can connect. So here I’m connected.

Now this will be the case, this actual connection, DP 300 failover, that will work regardless of which of these two is the primary or the secondary. Now let’s just have a look at what we got at the top. Oh, by the way, we’ve also got a read only listener endpoint as well. So we’ve got add databases. So if you want to add additional databases into that failover group from the primary, you can do that. You can edit the configuration. So edit the failover policy and the grace period. You can remove databases if you so wish, you can delete the group and you can have a failover and a forced failover. The difference between the two is that the forced failover may cause data loss. So you can see that doesn’t happen in a standard failover. So I’ll click yes. Now notice what happens.

So we’ve got this secondary which is in green, communicate with the primary, making sure that it’s up to date. And then, so after about 30 seconds in my case, that these two then switch over. So the primary is now still in blue, but is now the other database. And so we have a secondary in green. So the two have swapped and then you can swap again if you so wish. But all the time you can connect to this listener endpoint. So even though it’s now a different database, different server, we’ve still got the same end point. The only thing that you need to make sure is that you have the same users with the same passwords. So this is when using something like Azure Active Directory or using other forms of identification could be useful. So if I now connect again to this failover, but I’m going to have to use my other login because I don’t have the same users on both computers, I can now connect. And if I go to this read only listener endpoint, which is the secondary, then I can connect using whatever credentials I was using for the previous primary.

So as I say, it’s important to keep them up to date synchronized. Otherwise you’ll have to remember two sets of login and usernames and passwords, which really is not the best. You’re trying to reduce the connection difficulties. So this is failover. So failover is done from a server, not a database. So if I go into a server, so this is now the DP 300 agent server and I go down to failover groups. You can see we’ve got a primary server, secondary server, we have a fail of policy, the number of grace minutes to allow the data to go through and how many databases are in the fail of group that you’ve got in the primary server.

So hopefully now we’ve done this display of geo replication and failover group that this now makes sense. So failover groups have automatic failover. You can fail over multiple databases simultaneously. It does have SQL managed instant support and you use the same connection string. Georeplication needs to be in any region, it can be in the same region as the primary, whereas the failover group needs to be in a different region.

With Georeplication you’ve got multiple reticles, whereas with failover group you just got the one. And both Georeplication and auto failover groups support read scale, so both of them have got a read on the endpoint, which will hopefully take some of the pressure off the primary, which can read and write. So this is how we configure and test auto failover groups.