How to Create SQL 2019 Always ON High Availability

hi guys my name is Mohammad chess other than today we are going to start a sequel high-availability video and today we will configure 2000 sequel 2019 always on high availability so let's go and start our practical it's so if you stay with me till to the end of the video you will learn each step what I am doing without headache-free so it's very smooth step by step if you guys have a notebook you can just note down all the steps or reverse my video to check these steps so let's go I have for these two servers in my previous video as you know you guys if you have seen my last video regarding the installation of sequel 2019 so I change the name of these two notes so we really call node 1 and node 2 so these two nodes will join always zones SQL always on so this one server one is our domain controller which is the IP address is 90 and second node one is 91 and second node is 92 first is 91 and second is 90 so let's start our steps one is we will join the domain before installing sequel server fresh sequel for always-on so I will go in node 1 and I will join this snore to the domain and this node also I will join to the domain okay so note 1 I will go local you guys should know that already the 90 should be in DNS address if we see I can show you details here is so DNS should be your pointer to your domain controller which is also having a DNS so it will resolve the name so we'll go this is node 1 I'll change to our domain which we made last time in Windows so it is DC 19 dot local hey account admin account to join welcome to the dc-9 ok all good ok close we start this one also we will join to our DC 1902 main VC 19 oh hey it's better to join a domain first so and then after restart you must log into the as a as a domain administrator so that domain administrator when you log in this machines so I'm going to install sequel server the sequel will automatically add that domain admin accounts in the in the in the log squirty logins so you can do it later also but for the step-by-step this is better to do everything first and then make a foundation first and then we'll the rest of the story okay so this one is also ready and I will go and just we will log in with the domain so see here is no DC 19 I will go for other this is a local administrator keep in mind this is a local administrator I will go other users okay and in other we will these type dc9 slash administrator so now I will go to into my domain account time when you after joining that domain you have to do this one here also if we see this one also will give us a look in to be logon but make sure after joining that mean you have to click on other users and then they seem 19 your domain name and administrator so it will login to your domain controller okay see we are signing to DC 19 in term it is wrong administrator here is our is missing a so and see this one is also logging okay make it empty okay so right now we can see our this server another server also have joined domain now this one also and first time the domain admin profile is creating on this server okay so let it clear the recycle bin also here okay so I will go local and we can see here work group will be DC $19 pel so it's a domain right now this one is already join a domain and this guy will also join in two men and I'll show you the domain controller as well so we'll go and we'll see our both nodes are there in the domain controller and then we'll put them in a sequel container okay we go okay second one this one also domain joint okay DC 19 developer so let's go back on domain controller and I will check our users and computers okay so in the computers we have seen our both but I'm gonna bring them here in the sequel container so I will go right click these two nodes and I will bring them move to sequel container here okay so good to go these are in the sequel containers so I will keep opening my domain here but I will close from here but this one will be still there so let's go back on this node one and two and let's start the sequel installation so I will close this manager here I will close this manager here and I will mount sequel iOS in these two and we'll go here we'll choose okay sequel nine evaluation edition connect okay and second also I will do the same job I have my iOS sequel iOS I have loaded into the data store so I can easily mount there okay okay let's go in a node one and see if the drive is there already mounted yes it is I will do double click and start installation I will go node 2 as well and I'll start parallel the guys who didn't saw my sequel installation video in the in the same series they can see it so I will walk pretty fast here because I already have a one video for how to install a sequel server so we are our focus is on always on so this one I will go installation and I will start standalone installation ok I'll close the back door windows here also I will go into the installation and start a standalone installation I'll close the back door once ok so node 1 and node 2 is going to install a sequel there as well [Applause] stay with me it will take a little time okay evaluation yes we need evaluation only yes except it will check for updates no we are not going for updates anymore next no updates next it will start and node 1 also we will go same [Music] okay this one is I will close the bag one window this one also going so okay so I will close the back door window okay so next sab the license next no need to go update because we don't have internet connected on these two virtual machines next next so it will start the installation so we will choose only engine sequel engine here because we will create a dummy database and then we will make that Erebus into always on sinking okay here I will check my database only the database service engine or you can also check your tools here if we have management tools no we don't have here will install separately okay next so you know in Windows plus a sequel clustering we choose these directory on our cluster drive disk so after this always on I'm gonna make my cluster also sequel cluster and video source stay till then so okay so we'll do next and same we will come on our first it's checking for the rules here also we will use our database service engine next I'll go on second okay instance you can change if you like but I will go default instance okay from the first also we'll go second it it's gonna check if this instance is available on the same server or not so it will go and check and then it will continue server configurations so here we go with the service server configurations here is the some services accounts so you can do it if you want to change the service account it's better to change but it still right now it's running with the anti service so I would if if wanna change you can change it but we can do it later so when we are starting our another other step so let's go this one has a default okay so I will go for mix mode in case of something happened we have our SA account as well as with the domain account so this is the password for sa account and it will be automatically available in the security and secondly I have to because I'm saying mixed mood sa count it's a password here and Windows tenth occasion we have to choose here add current user which we are login with this traitor account we have login with that I will choose the current account so it will choose the DC 19 administrator you go for next this is the summary and start installation here we go let's go on a node second ok we will do next same I'll go through the mix mood in our front user as well next and here is the summary good to go and install so our sequel installation is started or secondly meanwhile this is going to be install I'll copy my you know sequel management studio huh so we will go black on my another machine sorry is that setup is not iOS base I have a no issues we'll we'll copy that if we can copy here [Music] yes it's my other domain this machine it is set up here okay softwares inning systems and this is the sequel their management studio software I'll copy this one on my domain here on the C Drive we can copy an ADIZ and then if we can copy here paste and for air I will take it on these two nodes so we can install sequel management studio also this one also reached but no need we can cancel this thing they do need okay note 1 Note 2 and this one is still there so we can pick from our 90 domain controller okay here we go we have 18.5 latest one I have downloaded from Microsoft website the sequel 19 management studio I have poppy on my domain C Drive so when the installation completed here the sequel then we will start installing our management studio Sall most complete near to complete okay during the completion of this I will copy the sequels the management studio set up here I will go on my domain II on this earth I will put on a seat wrap later on we can run yeah yeah okay here we go so on our first note the sequel server already installed I will close I will copy here this on a C Drive text so our sequel also need to be complete here equal server is also completed so if you see here in our sequel in the node 1 we don't we cannot open this sequel server in any studio so let's install the studio here also so I will run the studio hair doll cake and also note - I will run you parallely there we go it will take a little time to load its node 1 and node 2 area is run node 1 started noise this they're also I will run I will minimize here okay install installing a loading packages installed restarting the learning package [Music] [Music] so this one will take a little time until now I will post a video when these studios are completed on both nodes so then I will come back okay our studio has been completed it's asking for restart I will do we start here and the second node we will check yes is also ready we'll do restart okay so this one will be after restart we'll come back so I'll bring my domain controller on this side okay and these two notes which we just restarted okay no one is coming and will do no two also okay make sure this in nineteen [Music] after installing oh it's a bit heavy some machines are little taking time okay one is ready second also hair so meanwhile I will do sequel I will start here she's still taking time I will login this one or again your login with the admin account starring one management studio here see in studio this guy will minimize I'll come on a second okay here also I will start management studio [Music] okay many men stood is starting here also server manager and management studio will start here I'll minimize this manager double time clicked minimizes fun after installing a sequel management studio first time is take up time because these two my machines have a four 4gb RAM and still see it's a fully backed and after it's loaded it will be released this memory so that's why it's just taking a little time [Music] let's go and check if it is too many studio bhamini both are running and still 4gb back to back because we restarted the machines and it's still taping a little bit time to okay so in the two we already have yeah so if I go login with the windows 10th occasion you see the DC 19 administrator which we gave already there so I will connect my this service engine yes connected and here we don't have any database and in this first node also with the same it's connected so I will do or two more Saints like first I will change these two machines services with the domain account so I will go administrator Windows administrator tools and to the services and in the sequel I will run agent and main sequel as with not with auntie I will start with our at domain admin so I will check with the entire directory and I will say it ok available and I'll give a password I a ok ok and we must restart every time after changing love and user ok again for the agent also our ego properties dog and has admin change this one this one okay so admin is also this one is also so it's a still manual so let's go and read this one we restarted already and this is the unknown one let's go and do the same thing on node two I miss Oh there we go double click logon has to study not a local computer looking directly that mr. okay change the password [Music] apply okay okay and it should be restock change this one also my agent doth change the directory enjoy and check okay and give a password okay okay done these two things are done so next is when you completed these two things you make sure that your server properties and connection is for remote connection is checked okay and check another thing in the log ends you have your dc9 account with the cysts admin rule rights to the other server rules sysadmin public and secure able if you see here sequel connection c sequel connection should be connected and for assays connected so this one also we have to connect okay granted for our also okay it's undone let's gonna second one will close this one will make sure our remote connection is checked okay and our domain admin have a rights of sysadmin and sequel connection is granted missile - okay see sad man and it's curable we'll go and check our well by default is granted for SI but you can run for this one also okay okay so these two things are completed so let's start another story which is our failover cluster so I will go on my node one and I will do I will go in my server manager I will add a role on features there we go we'll go next next yes and in the features I will choose failed or cluster here we go add features failover clustering next and install and no - I will go same on my server manager add features next next next next and fail or clustering third feature next and install so this will install the failover clustering feature manager on these two nodes okay this one still running okay are you moving yeah okay good so after these two we will join them into the cluster one cluster these two node before that I will go on my domain and I will give full right to these two nodes yes they are going to make a cluster on in the cluster object will go Active Directory users and computers and here it on this computers I will give them a full rights properties right click properties in the security will choose remove this one old entries the servers are deleted so let's go for this one and objectives computers and here we go in 19 so we have to sequel once equal to I will give our full rights apply why and okay as well as on this container because these are belongs to these two maybe they are going to make a object here that entries which I deleted it's my old two nodes which was clustered so I delete that node that's why that dummy into is myself so we will choose when 9 don't worry you will not have that increase okay so these one full rides apply second node also rides apply here we good to go with the domain let's go back on node one and see it's completed you must restart okay we will restart started second also finish you must restart will born forest are here we go so it will take a little time this is our object it ran [Music] [Music] [Music] because we have installed a feature that's why it's configuring the feature okay this one is restarted we will just go and click on a login menu this one is little taking time [Music] [Music] okay so now again the admin domain admin account also we are near to login okay the domain admin make sure you are logging oh they're starting our server manager we don't need the server managers to open automatically I will just go here in the manager and I will choose our manager properties and we will choose do not start our big claws and no need this one anymore and we'll open our cluster manager here which we just installed before the restart here we have cluster manager I will open this one here and in the second we'll do the same in your server manager properties do not I will open a cluster here cluster manager okay you fake here there is our cluster manager is ready so we will build a cluster now here also so either you can start from node 2 or node 1 so both side will be same at one node what able to validate configuration next browse the servers to servers location is this so when 19 it will check for the hours I want this to be into the cluster [Music] which both of them me okay so both nodes are here so you go next okay test operations all this next we are near to star all the tests okay so test is started it will give you a network think and the secondly will give you a like disk that we don't have a disk we are not going to use any disk because in always-on you have two separate databases which is syncing to each other and in sequel clustering we have a wonderer base in the central point and the both nodes are hitting that Nara database so a lot of in corporate sector people likes always on because they have two places if one goes down they still have a sync data on the other side but in the cluster your SanDisk are your lungs is gone then the both nodes are free so you have to recover that cluster disk and then that both node will be join so that's the lead little difference okay it's gonna check and if the validations is okay it will give us a check point here to check and proceed for the cluster it will take a little time to go through all these death [Music] okay almost completed [Music] we can see a long test here soon these to the meaning and it's validate the Windows Firewall as well if it's passing through all the cluster traffic [Music] [Music] [Music] yeah it's okay network is limoges for us okay saying that there is some major things maybe one issue we'll check [Music] there is an error retrieving the qf information from mode one okay no shoes okay even though we'll create our clusters and okay let's do one more thing so we may need close the yeah okay we will close the firewall will check the firewall again and then in both nose somewhere manager Kara firewalls are off wells or oh okay so firewalls are off that's to run again on second mode so this one closed on this server I will run again a run validation next balls most at second added next next [Music] next [Music] [Music] after this validation will see a report so what is the related to the errors if it is regarding the disc or regarding the network in another network that that's fine so because we have a single network so some time is preferred in the cluster you have a data network separate and then the cluster network separate on these two Nate no so you have to add two new clocks but here we go let's see for always-on it's enough our software update validation is failed no shoes if we see here you can see a soft validation error is there let this completely see the network validation network communication [Music] [Music] serious threats the notion that only one it will be regarding network or maybe it's update or some other things so let's create a cluster anyway so we have everything is okay except these two okay these two warnings okay we'll go finish and check create the cluster and cluster they started to creation a cluster who will go next so sequel - the world cluster a and here we need a third IV 9390 is our domain controller 91 is this node 1 and 92 is no - and this is 93 next okay add all eligible disks we don't have any disk so I will uncheck this one next and it will start creating a cluster [Music] okay you have completed successfully finish and after finish we'll see our cluster is here and is created and if we see our both mods are up and go ready to go so I will check one more thing before our seed I will go back on my domain controller and I will check if the cluster object is created under the active directory users and computer created here let's go here it's rated here so see it's created here that's why we give a full rights to these two nodes so what I will do on this I will give up food right again to these two nodes is security because these two nodes gonna manage it okay computer object okay apply and to once again control apply more thing because this cluster will create another AG object here so I will give this cluster full rights on these computers as well and on the on this one also so you go here add another computer object okay which one SQL - W windows there and also here properties security add air computer object and it is sq L over cluster full rides apply okay so good to go so the main part is also done so we will go here and we'll close our this manager here and let's run at some pre-testing that cluster is all good so what we will do will go in the robes and we will create a dummy row empty over here create a empty row and let's move this right now its own ass node one I have to move it - no - and check my cluster things are ok ok yeah it's up and running and if I come on my node one and the fresh I will close he'll open again I must see on my both side ok here we have both nodes right now is running or not chiku my empty both nodes are ok here and I will bring it back on to move into past available again on one so it's one and running so I will delete this one this is only for 4 year old you can create empty role for testing I will delete ok gone so right now we have a cluster everything one more thing you have to do before you go to start making always on that is I will run first here go and run my management studio first thing you have to do here after making a cluster come on your sequel configuration configuration manager I will come here I will just enable my protocols like this one also okay and after that I will thus we start this one before but before we start that an A one protocol enable secondly I will do right click properties and I will choose always on on this guy unable always on availability group I will do apply and I will do oh he is asking for a star the service I will do restart ok so these two things you have to do again I will go here I will enable this one also and I will go on my services on my sequel service I will right click properties and I will do always on on this guy which the cluster we are make sure first make a cluster and then otherwise this cluster will not be visible here apply and then asking for he stars okay and then we will do we stop okay so this one is restarted there is running they're also running now we will do will open our studio okay management studio here I will go ahead this is also started automatic I will close and I will go for in my management studio one more thing I have I will have required at share folder I will let you know why we require that share folder let me make the one share folder here on my domain controller you can put it on a third location I will make it here SQL at so I will right click for berries and I'll make it share watch permissions everybody okay and when I use it okay okay okay so SQL data TR on why I made it I will let you know when this studio is open here is second node that here we are and first note connect okay so one thing we don't have any database here so I will create a one database so that one we can put into our always on for testing and whatever the database is for the application you have you can put one by one or you can remove when you you can do I will do it database this is my database test one so you guys can have in the corporate the sector or production you have a real application databases this Kurian don't need anymore okay I have one okay so this one is done let's go and check now we are ready to go make a always on group I will go I will ask create new availability group okay so here we have a little bit resolution problem let's see I will make resolution a bit acceptable so you guys can also see no it's very little better but maybe this one yeah this is better keep it okay so now you guys can see full screen this one let me check if I can okay no problem so this is the wizard I'll go next and he'll you have to give any your group name I will say sequel AG group maybe this astra type windows server failover cluster and we'll go next select the databases see it's not meeting the requirement now we have to take a full backup so I will go back on my management studio I will do a full backup right click and take a backup okay send a person and took a backup so I will minimize and we will refresh and it's same meets the prerequisite okay next one okay this is the replica one automatic failover yes and readable yes okay so let's send the second replica which is our two automatic failover yes invadin okay so these are two our replicas are will go next to servers added I will choose full and I will give the folder which I made on a hair what it will do this node will copy that database here and restore from here to this side that's why I made this SQL data TR so I'll go back here and I will go here and I'll see my double backslash 192.168.1 hundred dot ninety my that server IP and it is SQL data I'll do next is checking everything is fine it's now checking the network location okay same the listener is not created yet so later on we can create a listener also I'll do next finish and it will start making our derivates always-on group okay all success our all the things see here it's it's took it's created this one and then it's joined the group and then validated then took a full backup of test database and put it on our network location the folder which we made on a domain controller and then it start restoring on the other side node two and then it's done okay so we will go in our here I will bring back our resolution so you guys can see easily okay so it's bigger now so we will see here there we go we have always own availability group here it is the both replicas primaries kendry and tests is already inside the availability databases and listener we didn't create it we can create a listener add a listener here so listener is a third one AG - you have to give it rasa choose from statics and port is one for three three our clients will connected on this so we will our client we will give them this listener IP a so I will give them this IP so they can applications need this IP so applications don't know which node is active and H is passive so they will keep learning talking to the listener and listener will give the direct of the e active No so our listener is also ready so we go these are if I do refresh here so this database will be changed the status synchronized and if we go on an our node 2 and we'll check HERE no databases is there restoring refresh again and it's in restoring it will take a little time so this one will also so if we go here into the primary secondary both are here our always on still there because it's restoring and this one is our AG code so see guys this is the our cluster always on how we created and if we see you can see the always-on status here both primaries country and then our databases so you can create and if we go in our failover cluster cluster manager both nodes are up AG group is it'ld and runnin see this group is created by default from the AG sequel age so this was the video you can now fail over on the other side and also you can change it if you want to fail over on a node - you can do it so hope you guys liked my video and if you have liked this video just click if you're facing any problem just give me in the comments I will give this will give you the solutions and kindly do not forget to subscribe my channel and hit the bell icon for upcoming videos take care bye bye

Channel: Muhammad Shehzad Arshad

Views: 16,744

Keywords: how to configure alwayson in sql server 2016 step by step, sql always on, sql server alwayson requirements, sql always on cluster, sql setup always on availability group, sql, sql 2019, sql alwayson, sql 2019 alwayson, how to configure alwayson, how to configure sql alwayson, sql availabity group, sql cluster alwayson, sql cluster, sql alwayson step by step, sql alwayson configurations, sql tutorials, sql alwayson setup, how to configure sql alwayson step by step, sql server

Id: 3Lo6KWhMQqs

Length: 66min 39sec (3999 seconds)

Published: Wed Apr 22 2020