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

5. 100. Configure failover cluster instances on Azure VMs

Now in this video we’re going to install Failover Clustering. So we need to install a Windows Failover Cluster role and we do that in Server Manager by going to manage Add Roles and features again. So just skip down to the Features section. And here we have failover clustering. So click Add Feature Features and Next and install. And we’re also going to do that on the SQL Server number Two. So exactly, same thing, manage Add roles and features next four times and then Failover Clustering next a few more times and install. So it’s just installing.

So I’m just going to pause the video until it finishes. Now it’s finished and another restart is pending. So let’s restart the computer. Go to start. Power options restart. And this is a planned restart. And while it’s doing that, we’ll do the same wave SQL Server too. So again, start Power Options restart and this is planned. So now having done this, let’s connect again. So I’ll go to SQL Server One, go to RDP Remote Desktop Protocol, connect again. I think this is the last time we’ve got to connect, or last time we’ve got to reboot anyway. And then do the same with SQL Server Two.

And here we are logging into SQL Server One and in a separate window logging into SQL Server Two. But it’s just taking a little while to do that. So now we have got Failover as an option. So it’s not that we’ve actually installed anything to do with Failover, it’s just an option.

So now we need an actual Failover cluster. So again, it just takes a few seconds for these things to update. But what I’m now going to do is go to my old servers, right? And click on my server and we should have there Failover Cluster Manager. So here we are. And you can see at the moment there are no failover clusters. So here are the clusters, no items found.

So you can validate the configuration if you wish. But I’m going to go straight into Create Cluster. Right. So click next. We want now the names of all of the servers we want to have in the cluster. So we’ve got SQL Server One. Hopefully this should be added. This is this machine. So if you can’t find this machine, there’s something really wrong. And SQL Server Two, this might just take a second to find, but it’s found it fine. So next we’ve got a validation warning. Are you okay? And it asks, do you want to run the validation tests? Yes or no? Well, I don’t want to run the validation tests for this. Next, enter a name I want to use when administering the cluster.

So I’m just going to call it SQL Cluster. So you can see your limited to 15 characters confirmation of what you’re about to do. And the cluster is being made, right? The cluster has been made. You successfully completed the correct Cluster wizard. Of course, in a real setting, I would validate the configuration as well. But in this particular case I just want to create the cluster.

Click finish. And here we can now see that there is a cluster. So the SQL Cluster. And if I expand on the left hand side and going to nods, so you can see each of these computers has got a nod and they have got a vault as to whether the cluster or individual computers are up. And you can see both of these are up and you can see information about each of these computers.

And you can also pause and resume and you can stop the cluster service or you can evict a computer from it. So we have got our failover cluster. So it has got one particular cluster with two nodes and various storage and other things. So that is the failover cluster. May.

6. 107. Checking SQL Server Installation and preparing for AO

Now in this video we’re going to create a small database with a small table and we’re going to make sure that I connect to SQL Server Two database inside the computer SQL Server One. So let’s first of all create the database. So I will go in SSMS to connect and I will connect to SQL Server One using Windows authentication. So I’ll create a new database. So this is just going to be SQL Server database one. And in that I’m just going to use some code that we’ve used earlier in this course just to create a sample table. So it creates a table called My table and then inserts some dummy data into it. So we’ve used that in the past when creating the Mi and the VM earlier on in this course.

So if we now have a look at My table, they can see in my case 9801 rows. In your case it might vary. Next I’m going to see if I can connect to SQL Server Two. So I mean the computer SQL Server One connecting to the database called SQL Server Two, which is hosted on the computer SQL Server Two. And this is one of the advantages of being a domain, is that you should be able to share resources. And here you can see I can. Now I’m also going to do one additional thing in my SQL Server One and that is to prepare for Always on Availability Groups. But I’m also in the rest of the video going to show you some things that you can do if you aren’t able to connect both SQL Server One and Two.

So what I’m going to do is go down to the Start menu and I’m going to open up SQL Server Configuration Manager. So there it is. So in my case it’s 2019 and again I’m just going to reduce this inside so you can clearly see which computer we’re in. If I go to SQL Services, here you can see all the services and one of those is SQL Server and then in brackets the instance name. In this case it’s a default instance called Ms SQL Server.

I’m going to right and click on that and going to Properties and go on to the Always on Availability Groups and I’m going to check it enable always on Availability Groups. Allow this instance of SQL Server to use Availability Groups for HADR so you’ll notice things have been saved, but they are not effective yet. Until the service is restarted, it says Stopped and restarted. You can stop it and restart it, but restarting will just be as efficient. So this is something you need to do for the future videos. Now, if you’re finding difficulty connecting to SQL Server Two, then in SQL Server network configuration, we have the protocols for Ms SQL Server.

Ensure that TCP IP is enabled. If it isn’t, then right and click on it and go to Enable. Additionally, we might have problems with firewalls. If that’s the case, then what we need to do is go to Windows Defender Firewall, go to Advanced Settings inbound Rules and ensure that there is a rule for port TCP 1433. So if there isn’t, then you can click on new rule port TCP 1433. So 1433 and 1434, they are the standard ones that SQL Server communicates on. So TCP 1433 rule, there we go. So I don’t have to do this on the other computer, but if I had a problem then I would absolutely do this.

So now in SQL Server Services what I need to do is restart it. So let’s click on this right and click and restart. Now you can choose to stop and then restart if you wish, that’s absolutely fine. So if I’m doing that, then I’m stopping. Once the service is stopped, no one can access it. So don’t do this lightly. And then right and click Start. So the alternative is to go and restart. So what I’m going to do now is go into my second computer. So here it is, reduce it in size so again can clearly see which computer I’m on and let’s see if I can connect to SQL Server Two.

Hopefully that shouldn’t be a challenge at all even if there were problems elsewhere and see if I can connect to SQL Server One. And again no problems. So what I’m going to do now is going to SQL Server configuration Manager. I’m going to go to the particular service. So this is SQL Server Services. Go into SQL Server and the name of the service, right and click go to Properties, always on Availability Groups, check Enable, always on Availability Groups click OK, get the warning again and this time I’m going to rightandclick on it and restart. So it stops the service and then starts it back up again. So these are all the changes that we need to do to make sure that the servers themselves a can communicate on the different computers and b are ready for the Always on Availability Groups.