DBA 165-Setup AlwaysOn Availability group on SQL instances installed in 2 nodes Cluster mode Part1

welcome to tech brothers in this video we're going to learn a special scenario and special scenario is how to set up always-on availability group when sequel server instances are installed in cluster mode the reason it is a special scenario is because when it comes to always-on availability group the best practice is that you installed sequel server instances in standalone mode not in cluster mode and but this this special scenario is given to me by a friend and he wanted me to go through some testing and see if it is possible and we run into a lot of errors when we set up availability group and I have put together those error lists right here no need to basically read it right now because we're gonna go through each error and reproduce that error and set up our availability group on sequel server instances that are running in cluster mode and I'm going to go ahead and upload these error lists on our blog too so that you can later on you can go go ahead and take a look on different errors that you might run into when you are going through this particular scenario so that may give you overview of our cluster right here is my cluster as you can see sequel prod is one instance of sequel server and sequel UAT is another instance of sequel server both are installed in cluster mode and I have two node cluster node one node two one thing I do wanted to mention that as you can see that both sequel server instances are running on one node and obviously when we install sequel server in cluster mode our main intention is that they should be available highly available so when node one goes down all the sequel server instances that are running on node one should go go to node two and because that is the failover that we defined and run on that particular node in this case that is the same scenario they are both running on one node but let's see that what it impact when it comes to availability group when both sequel server instances are running hit on one node because you are going to run into this situation if you have cluster that and sequel servers instances that are running in cluster mode so I'm going to go ahead and fire up sequel server management studio and try to create availability group between these two sequel server instances sequence every prod is going to be my primary sequel server where I want to create availability group quick connect and here is always on high availability group I have already configured both of these instances so that I can go ahead and take advantage of always on high availability group and you do need to configure in sequence every configuration manager and define that what cluster because that is must follow always on availability what cluster these sequence every instances are going to use in order to create successfully create always on high availability so right now I don't have any availability group let's go ahead and try to create a new high availability group so we're going to go ahead and use the wizard click you can click do not show this message right here click next and give it a name so in my case I'm going to go ahead and give a name AG test sequel cluster mode what it says that that we're testing you know availability group and when sequence entries and sensors are in cluster mode click Next I have one database that meets the requirement prerequisites so I'm gonna select that click Next and here is where I want to add the other replicas that I want this availability group to go to so click on add replicas and other replicas in my case is going to be UAT and click connect and right here is our first error that is saying that both sequel server instances are running on one node as the name suggests availability group it is not going to be high available because if node one goes down our availability group has nowhere basically to failover to so that is our first insert error right here that's the error our so in order to resolve this iron I'm gonna go ahead and click okay cancel this cancel this one too and fail my note sequence every instance you eighty to another node so it's it's going to take a minute or two to failover to know too all right so sequel UAT is up on other node we shouldn't get this error so we're gonna go ahead and we're gonna go ahead and try to create availability group again sorry about that click Next and here the here is the database that I want to select next at replica and connect the UAT it should not give us that error as you can see it led us at that secondary replicas so I'm gonna go ahead and make this readable secondary right here is very important warning right here it says that this is available cluster instance failover clustering instance do not support always on automatic failover as you can see right here the automatic failover is grayed out so every time when something happens and you need to failover your availability group you have to do it manually when your sequel server instances are installed in cluster mode so that is right here important warning and I wanted to mention that this is very important so let's go ahead and I'm gonna keep endpoint backup preferences just same default and I'm not going to go ahead and create listener because there are other stuff that we need to take care that's more important what's the availability group is set up it's not difficult to create a listener so I'm going to go ahead and click Next and this is the shared where it's going to take the this is the default what what full means that it's going to go ahead and take the backup of this database and restore it on our secondary replicas and this is the shared location where is going to store the backup and then secondary replicas is gonna come and grab that file and restore it keep in mind one best practices that if you know you wanted to create this shared location created where it doesn't reside on any of the node that is part of our availability group otherwise we will have a single point of failure so I'm going to keep it this this is not part of my cluster right here this machine name so I created shared right here tibias clustered his it was already created so I just grabbed it so you can create another share and put that there click Next all right we got the errors right here validation errors let's take a look on the first error it says the database sales order and screen quality exist in sequel server instance that hosts the secondary replicas that's okay we will delete that and let's take a look on the second error right here it says the following folder location do not exist and if you take a look on the second error right here I have put that here what it's doing is it's when you are basically creating via wizard right here it takes the backup and restore it with the default location of the files that our database that we selected and in when it's in cluster mode obviously those discs are not going to be the same and they're different discs and when it tried to restore the database though that up here this right here is the location right here becomes invalid and it cannot restore it from there and obviously since the it is in cluster mode we cannot create usually what happen is if we get into this error when sequel several instances are installed in standalone mode we usually do get this error what we do is basically go ahead and create that folder and what our drives are basically the same but in cluster mode drives are mostly not the same and we will not be able to create l backslash if you take a look on my let me show you right here right now is ft and L that is resided and these disks are used by one sequel server instance right here and they are on node 1 and they are not available on node 2 so how do we take care of this error we're going to go ahead and click OK and leave that here let's go ahead and connect with our sequel server u8e and let's take a look right here this is our database that it was complaining that it's already there so we're gonna go ahead and restore the database bring it online and delete it the reason I need to bring it online because if you try to delete the database that is in restoring mode you will get an error that you cannot do that so I'm gonna go ahead and delete this database now we shouldn't get that error invalidation if we revalidate but we should still get the error that it cannot find the files the location as you can see that the first error we got that the database already exists that is taken care of but the second error that we had that it cannot find the file locations that is still there in order to basically resolve this issue what we're going to do is initialize if you have ever done mirroring in mirroring we have two options either to let wizard initialize our database what we can go ahead and take the backup of our existing database and initialize our database for mirroring on our own so this is basically availability group is combination of mirroring and replication so it does that it goes through the mirroring process so if we go ahead and basically initialize our database then it is going to ignore this part all we need to do is go ahead and join the database that is already initialized this is part of initializing this error right here so if it is not initialized if it is already initialized then it will we have a choice to skip this whole thing and just tell it that go ahead and join it because we know that our database is up to date and it is in a right state to join so let's go ahead and leave that here and we're going to go ahead and restore the database on our secondary so let's go ahead and restore the database I do have a backup right here and I put it in a location where both but I could access from sequel prod instance node 1 and node 2 so here's my sequel sails back up when I'm go ahead and select that alright and I'm gonna go ahead and say right here that relocate all these files this is the resolution as you can see that is trying to restore on F and L I don't want that because my target is SNR for MDF is s for LD f it's R so I'm going to go ahead and collect relocate the files and it changed right here and click OK as you can see that our database already restored so let's go ahead and go to our availability group wizard let's go to previous and what this time we want we didn't want it to go ahead and initialize we want it to join now so join means that we have initialized our database so go ahead and just join so let's go ahead and click join now join only click Next as you can see that the validation part is taken care of right now this value a warning right here is that we're not creating listener and we'll create the listener separately but we are through with the validation so click Next this is just the summary that is telling you that it's going to go ahead and create availability group named AG underscore test sequel cluster mode and all other good stuff click finish and click on more detail so that we would know that what is going through and as you can see that we got an error let's take a look on error it says failed to create join at replica two availability group because TBS node is a possible owner TBS node one is a possible owner for both replicas so obviously we have a two node cluster we have a sequence that were installed in cluster mode and both of the both of those nodes needs to be possible owners so that we can fail over and that is by default by design when it comes to installing sequel server or any other application cholesteral where application and we wanted to take advantage of Windows cluster so this is by design but it is complaining about that that you know there's the sequel server instance sequel prod and sequel server instance equal you eighty node 1 and node 2 our possible owner of both of these sequel server instances so it cannot create availability group so let me show you right here after passing AG validation this is the error we get so let's go ahead and see how we can take care of this error like okay and go back to our cluster right here this is our cluster this is our sequence every instance prod and if you take a look on sequence every instance right here right click on that go to properties and go to advanced policies you will see right here is that the possible owners right here as you can see right here sequel prod is possible owner usually by default this is from a previous demo when I was testing all that usually it's like this so you can take the node two out and hit apply and click okay and now let's go ahead and know to sorry I mean go ahead and close this and open our failover clustering n this is a very local cluster and when we go to the rolls let's click on sequel UAT and because with the resources right here is our sequel server engine go to the properties and go to the advanced and as you can see that possible owners are TBS node 1 and TBS no - right now it's running on node 2 so it we will not be able to change that these both cannot be the possible owner because right now TBS no 2 owns this role so we cannot take this off so we can take node 1 off let's go ahead and apply ok so now at least we shouldn't get this message that there are possible owners let's go back and click on finish and see if we get that message all right we are through with the availability group AG to come online and everything we are it is online however joining failed right here let's take a look on the error it says the database sales new is not in a recovery mode so if you remember in mirroring the sequel server database needs to be in recovery mode in order for us to have a successful mirroring so let's go ahead and click OK and let me show you right here this is the fourth error that we're going to we have reproduced and we're going to take care so I'm gonna go ahead and leave that just like this and go back to my sequel server instance right here is UAT I'm gonna restore this database again because I do have the backup already so I'm gonna go ahead and restore my back restore my database in non recovery mode because this is what it's complaining about so I'm gonna go ahead and click on restore database from device get the same backup because I know that I have not made any changes so backup reside that in my case it's a central location this is my backup it's selected go file relocate the files and an option up here in options we will restore it as no recovery keep in mind that I'm intentionally going through all these steps so click OK as you can see that it is in restore mode so in mirroring the database has to be in restore mode in order for our mirroring to be successful this is exactly the same way because it's a combination of mirroring and replication but during setup during initialization it goes through the mirroring part and then after that it goes through the replication part for functionality purposes so let's go ahead and go back close this we're gonna go ahead and delete this quickly recreate again select the database at the secondary replicas make it readable for other applications click Next join we're good with that finish now since our database is in restore mode it shouldn't really complain about the database being in restore mode but we have an error again let's take a look on error while now is complaining about that no transactional backup was restored on this database remember first it complained about that it just needs to be in restore mode but now is complaining about that well usually in mirroring we take the full backup or full backup differential backup and the log back a transactional log back up basically you don't need transactional log backup when you have differential but anyway so in mirroring we do the transaction log back up in no recovery mode and that's how we set it up and this is exactly what he is complaining about that yes you have database is in restore mode but it is not in a right State where I want it to be so in order to take care of this one we'll go ahead and click OK click close keep in mind our availability group is created but it is not joined there's no secondary replicas for this availability group so what we're going to do we're going to go ahead and restore this database and then we're gonna go ahead and restore this database with the transactional log backup that I have already taken keep in mind when I'm doing all this I have taken the transactional log backup and there was no transaction made after that so if you have a scenario where I'm gonna go ahead and delete this when you're doing it on production transactions are happening so you need to take the latest transaction and restore that and let's go ahead and restore this restore database right here with the transactional log backup so right here is my full backup I'm gonna go ahead and click okay I'm sorry I needed to add the transaction log backup all right full backup click Add and here's my transaction log backup click OK it's selected a full backup and transactional log backup click on files relocate all the files and click on option and we're going to go ahead and do no recovery and we're going to go ahead and restore this and then after that we're going to add a replica into an existing availability group and see if we're successful all right it's successful as you can see the sales order in the school new is in restore mode so we're gonna go ahead and click on availability group keep in mind that this availability group was created but there was no secondary replicas in it so we're going to go ahead and add a replica and this is our secondary replicas we're going to go ahead and click and connect as you can see that it is connected click Next hang on a second I think that we need to add that and right here are our database remove database from availability group this is first we need to do we're gonna go ahead and remove it and add the database again click Next and this is our database you can remove the whole availability group and recreate it again I'm just trying to save some time join only connect next and as you can see that it went through the validation fine and right here is our sales orders let's go ahead and click finish and see if everything goes fine as you can see it joining is successful this time click close and refresh our secondary databases to see that if R is in sync as you can see that it is synchronizing before that it was not synchronizing so our availability group is all set but keep in mind that in order to set this availability group we have gone through these errors and we have gone through the resolution of these errors don't consider this a success because in next video I'm going to what we did on our cluster it seems like that if we make availability group happy our cluster is not happy if we make cluster happy availability group is not happy but I'm going to go through all those tests and share with you in my next video and I hope this helps

Channel: TechBrothersIT

Views: 14,632

Rating: 5 out of 5

Keywords: Always On SQL Server, High Availability SQL Server, SQL Server 2016 High Availability Features, SQL Server Tutorial, Tech Brothers, Microsoft SQL Server (Software), Computer Cluster (Field Of Study), Windows Cluster, AlwaysOn Availability Group, How to Setup Alwayson Availability Group on Windows Cluster, Windows Failover, AG Failover, TechBrothersIt, DBA Training, Advance DBA Tutorial, How to Setup AG in Cluster Mode SQL Server

Id: tji63pChEG8

Length: 28min 7sec (1687 seconds)

Published: Thu Jun 18 2015