DP-300 Microsoft Azure Database – Recommend and test HA/DR strategies, and configure HA/DR Part 4

7. 98. configure quorum options for a Windows Server Failover Cluster

In this video we’re going to talk about quorum options. Now, always on availability groups and failover cluster instances, rely on the underlying Windows Server Failover Clustering Service. And you might see that with an acronym WSFC. It monitors the network connections and the health of the nodes.

So we have got these nodes, these clustered servers, and I’ve got a two nod cluster. Now this will function quite happily. If both of these are up, fine. If one is down, okay, that’s a bit of a problem now, because how do we know it’s down? One computer is not responding, the other says it may say it’s up. Really? We need an odd number of vaults, one to say definitively, yes, this computer is down and then we can do something about it. So for this to happen, we need something called a quorum resource. So it’s an additional vault.

So this quorum resource which will give us an additional vote, is called a witness. A quorum witness. And there are three different types of options. Cloud witness, disk witness and file share witness. So in this video we’re going to configure for a cloud witness.

So first of all, we need a storage account for the cloud witness. So here in the portal I will go to storage accounts and I will create a new storage account. I’m going to put it in the same resource group as everything else. SQL. H-A-D-R. Enter a name. So I’ll enter SQL Storage witness. But you notice I can only use lowercase letters, enter a region, the performance standard is five for this, the redundancy.

So locally redundant storage can be used. It’s the lowest cost option. And then all the rest of it we just say yes, no problem. So let’s review and create it. And it should only take probably a minute of that. So about 30 seconds later it’s been created. So I’m going to go to this resource and then on the left hand side I’m going to go to Access Keys within Security and Networking. So what I will need is the storage account name and the key.

So I’ll need to click on show keys to actually see them. So having done this, let’s go back into our SQL Server. So we’re in the Failover cluster Manager in SQL Server One. So I’m going to go to the actual cluster and I can right and click on it, or I can go to more actions on the right hand side. And I’ve got configure cluster quorum settings. So a new dialog box appears. And here you can set the quorum configuration options. Now there are three different options.

So if I use the default quorum, then it will just determine what it’s going to do. I can use Advanced Quorum configuration or I can select the quorum witness. So I’m going to do that and we can see that there are three configurations here. So first of all there is a cloud witness. That’s the one we’re going to be using. This needs Windows Server 2016 or above. It uses Microsoft Azure to provide a vault on cluster quorum.

It’s ideal for deployments in multiple sites, zones, and or regions. It’s very small, it’s about a megabyte, and Microsoft recommends that you use this whenever possible unless you have a failover cluster solution with shared storage. It uses general purpose and standard storage. Blob storage and premium storage are not supported. And as we have set up, you can

use locally redundant storage for replication type. It uses port four four three, which is the Https, the secure web for communication. So we’ll need a few details. As we’ll see, we’ll need the Azure storage account name, the primary access key corresponding to it, the endpoint server name. If you’re using a different Azure service endpoint, such as Microsoft Azure in China, we’re not going to be using that. Now, another possibility is a disk witness.

So this requires a small cluster disk in the cluster available storage group. The disk is highly available. It’s most resilient and can fail over between the various nodes or computers. It’s less than a gigabyte, so you’re not going to be paying a huge amount for storage. It can only be used when you have a cluster which uses Azure shared disk and it cannot be a cluster shared volume.

And then finally there’s a file share witness. This is configured on a file server running Windows Server. A file share on a separate virtual machine in the same virtual network. It is a file share on a separate virtual machine in the same virtual network. It needs to be separate from the cluster workload to allow equal opportunities to the other clusters.

And you should only use the file share if you can’t use the other two options, the disk witness and the cloud witness. So let’s configure the cloud witness. Click on next. So we’ve got the standard Azure service endpoint. We don’t need to change that. So we need the storage account name. So we go back into here and copy this and paste it in. And then we need the key. So we copy this key and paste it in. Now, I’m not actually going to finish off, I’m not going to select a quorum witness for what I’m doing here. But I just want to go through this again.

You’re going to more actions configure cluster quorum settings because there’s one additional page, and that’s the advanced quorum configuration that I want to show you. So here you can see you can select your voting configuration so you can have certain nodes having the vote. Maybe you don’t want one of the nodes having the vote. You’ve got seven nodes perhaps, and you’re saying not that particular one. You could also say I don’t want any nodes. So in this case, only the disk witness has a vault not recommended because there will be a single point of failure. So you can say all nodes, select nodes or no nodes. And on this screen you can say do not configure a quorum witness.

So if that happens, then the cluster quorum is the majority of voting nodes in the active cluster membership. So it’s the computers that decide if you want a witness, then you can add it here. So don’t recommend no majority, just the disk witnesses only. So I’ve just successfully configured this quorum setting so that it says, okay, these are the particular nodes that I want you to use, and this is how I want your quorum to be set up. So if you want to configure your quorum options for windows server failover cluster, then you go to failover cluster manager.

You go to the cluster itself, then you can right and click, or you can go to more actions on the right hand side. So either way you get to more actions and then configure cluster quorum settings. You go into the wizard and you can use a default. You can select a quorum, or you can go into more advanced versions where you can say, I want particular nodes not to be configured. And then you can choose a cloud witness, a disk witness, or file share witness.

8. 95, 97. create an Availability Group

In this video, we’re going to prepare databases for the Always on Availability Group. We’re going to also set up the Always on Availability Group as well. So the way we do this is in the node called Always on High Availability. And you can see it is not enabled, it says, so let’s just make sure it is enabled. And so we go into the SQL Server Configuration Manager and let’s just check that it’s running. So we need to go into Properties so you can see it’s checked. So what I’m going to do is stop this and then restart it. So now that that’s done, hopefully this is going to work. Let’s close this down again. Let’s open it up. It could be simply because I hadn’t closed down SSMS, right? So we’re back up. So let’s go into our server and hopefully now this time now we’ve rebooted SSMS.

I can go into always on high availability, right? And click on availability groups and go to New availability wizard. We won’t be getting very far, I should point out right at the moment. So I’ll click Next, I’ll put in my Availability Group name, so it could be anything. So I’m going to put in SQL availability group. We have a cluster type. I’m going to leave it as a WSFC Windows Server failover cluster and click on Next. And then I’m going to say, what do I want to back up? And I want to back up this SQL Server database. One. And I can’t and here’s the reason. A full backup is required before I can do this.

So I’ll close this down and I’ll go into the database and hopefully now you know very easily how to back it up. We’re right and click on it, go to Tasks, go to Backup. We want a full backup. Just click OK and it’s backed up. So we’ll try this again. Going to always on high availability, right? And click on availability groups and go to New Availability group wizard. And this time we should be making much more progress. So I’ll type in my group name.

So now you can see meets requirements, so I will check that. And now I can say what the Replicas are going to be. So where is it going? What is the group? So currently we’ve got SQL Server One. Hopefully now we can add Replica and sign into SQL Server Two. So this was the point of being able to connect to it earlier. So there we are. And we can see they have an initial role primary and a secondary. So do we want automatic failover? If we do, we need synchronous. Commit. Asynchronous commit will not allow for such swift automatic Failover. So asynchronous commit means Data comes into SQL Server One.

For example, it gets written and then it gets sent out to SQL Server Two. Synchronous commit means data comes into SQL Server One. It gets written but not committed. It gets sent out to SQL Server Two. SQL Server Two gets it written. SQL Server Two says to number one, I’ve written it, it’s fine, and all of the other secondaries do as well. And then the primary then commits. In this way there is no data loss. We can remove Replica if we wish to.

And while it is a secondary, do we want it to be readable? So yes or read intent. Connections. So this will only allow connections which say I only want to be able to read it as opposed to I want to connect to it. So if you just have a general connection saying I’m happy to connect read, read, write, then saying Read Intent only will say no thank you. You have to say read only. So it’s your choice what to do here next, we have the data synchronization. So how do we want the data to get there? So we can do automatic seeding. So SQL Server automatically creates the databases. It requires that the data and log file paths are the same on every SQL Server instance. And that could be a problem. We can do a full database and log backup. We can just join only.

So that starts data synchronization where you’ve ordered, restored database and log backups. Or we can skip this initial data synchronization. I’m going to go for automatic seeding in this particular case. So it’s just going to go through everything. There is a warning about the listener configuration. Don’t worry about it at this stage. Click. Next. Click. Finish. And the new Availability Group is being set up. Well, initially, as you can see, the computer had an error, though I was able to get round it fairly swiftly and get the Availability Group working.

So what I needed to do apparently was give some permissions to the login called Nt Authority underscore System. So I will add this into the SQL code document that you’ve got. So this first bit I ran, I didn’t need to because this login was already available and then I needed to add permissions to alter any Availability Group. And I ran this for both SQL Server One and SQL Server Two.

So now you can see it has created this new Availability Group and we have this in the left hand side. So now we’ve got a new Availability Group wave available Replicas, available databases, and the possibility to have Availability Group listeners.

9. 89, 97. Failover and integrate a database into an Always On Availability Group

If we want to go into the SQL Availability Group, I can right hand click on it. Not only can I fail over, from there, I can also show the Dashboard. So let’s do that. So here we can see the current state of our Availability Group. So currently synchronizing, it’s not yet synchronized. Now that means that if I attempt to fail over, there could be some data loss. So let’s see what happens if I fail over. Which means that the SQL Server Two would then become the primary.

So I’m going to go to the top right hand corner of this box and click on the link to the Start Failover Wizard. Click on next, I would select what the new primary is going to be. So in this particular case, data Loss Warnings. So be careful. Click Next. We would confirm the potential data loss if there was going to be some. So it might say that there will be no potential data loss. So I’m going to check this to confirm failover with potential data loss.

And then I would connect to the Replica. I would click Connect and then I would do the Failover. So you can do this in TSQL by saying alter availability group name of group. So in this case, SQL availability group failover. Now, if you do failover within a Failover group, all databases are then failovered. So there’s only one primary and one secondary for the databases. It’s not like it could be primary for one set of databases and not for Nova. It’s either primary or not in a Failover group. Now.

You can also use the PowerShell commandlets invoke Hyphen AZ SQL something Failover. So it would be AZ SQL instance failover and you would put in the resource group and the managed instance and that would fail over an Azure SQL managed instance. The database failover would fail over a single database in an Azure SQL database which could be a single database in an elastic pool without affecting other databases in the same elastic pool. For that, you would need the server name, the resource group name, and the database name. And finally, there is the elastic pool Failover.

And this fails over all databases in an Elastic pool. So you would need the resource group name, the server name, and the elastic pool name. Now that you’ve got to your Availability Group, if you want to add additional databases to it, you can write and click on Availability Databases and click on Add Database. And then you can select databases to be added to your group, just as we’ve done previously.

In TSQL, we’d be using Alter Availability Group name a group Add database, and name a database to set always on Availability Group options. On a secondary database, you can use Alter Database Name or Database set HADR Availability Group equals and the name of the Availability Group. So this is how we can create an Availability Group. We would go into Availability groups. We click on New Availability group wizard.

We would select the name of the Availability Group group, what databases were going to be included in that group. So this one is already part of an Availability Group. Then we’d select the replicas, whether they’re going to be read only secondaries or not accessible. And then we’re going to select the data, synchronization how the data was going to be synchronized, and that is how you create an Availability Group.

10. 99. configure an Always On Availability Group listener

Now we’ve got these two replicas, and at the moment I have to connect to one or I have to connect to the second. I can’t just connect to whatever’s available, or you can do that with an Availability Group listener. So if I have one of those, then I can connect to the listener, and I don’t even need to know what instance name I need to connect to.

So let’s create an Availability Group Listener, and it’s actually be fairly easy to do. We just go to Availability Group listeners within that particular Availability group, right? And click and add Listener. And you can see that there’s only one page here. So we need a DNS name. So this is up to 15 letters in SSMS, so it can be letters, numbers Hyphens or underscores. So listener DNS. Then we need a port to be used by the listener. So I’ll just pick a port 1433. Then we need a network mod.

So is it going to be DHCP or is it going to be static IP? So DHCP, this is a dynamic host configuration protocol. It means that the computer picks the particular network TCP IP address not recommended. The alternative is a static IP address, and you must specify a static IP address for every subnet that holds an availability Replica, including the subnet and IP address, as you can see here. So in other words, this listener must be able to communicate with all of the replicas.

And if you’ve got one that’s in the ten five range and another that’s in the ten six range, then you need to specify static IP addresses which fulfill both of those requirements. So you’d have a subnet of ten. So once you’ve done that, you just enter the details and click. OK, so this is how you can configure an always on Availability Group listener.