How to Install & Configure SQL Server 2019 Fail over Cluster Step By Step

hi friends welcome to my channel and today in this video i am going to show you how to install and configure sql server 2019 failover cluster so this is going to be a basically a step-by-step guide to configure two node windows based sql server failover cluster so in this video we are going to configure two sql nodes and then we are going to install and configure first windows server failover clustering roles and features on those two nodes and then we are going to install sql server 2019 on both both nodes and then we are going to have a failover cluster for sql db instance so let's start step by step so this is my demo environment which i have created for this environment so i have total four virtual machines which is running on my vmware workstation here you can see i'm running vmware workstation on my machine and this is vmware verification 15 pro and just to let you know my physical machine having around 128 gig of memory and then i got one i7 processor so this is my physical machine configuration and on this i'm running these four virtual machines so the first machine is active directory and dns server which is win2k19dc01 and my domain name is labshandson.com and my actually dns server ip address is 192.168.1.101 so here is my active directory and dns rule you can see installed on this machine and this is win2k19 dc01 and this is my domain name and this is the ip address of domain controller and the next machine i got my iscsi target server this is basically a storage server which is providing a shared storage to both my sql nodes so i uh since if you have a physical environment and you are doing this for any corporate environment or a production environment then you will get definitely storage from any of the storage box like like you can say nimble dell emc or any other storage box but in this demo i am going to use a windows based iscsi target server to have a shared storage across my clusters node and then we have two sql nodes which is also running windows server 2019 so this is sql node 1 which is here is my first sans server iscsi server which has been 2k19 san and you can see i have two nicks on this machine so one for the domain connectivity which is 192.168.1.102 and second nick is for iscsi connectivity basically storage connectivity with 10.10.10.102. so this is my this machine and this machine having two vcpu and eight memory and my domain controller having two vcpu and six gig of memory so this is my window k 19 sql one so this machine having um join in my domain which is labs and so on.com and this machine got four nicks so i'm going to describe your next um network ip configuration on this page so just to give you idea we got four nicks on both the both the sequel nodes and these are two sequel nodes so this is wind to connecting sql sql1 and join in my domain and this is win2k19 sequel 0 1 0 2 with domain join and you can see both my sequel machines having one disc only which is c drive of 60 gig for the os installation so you can see this is this machines equal to also having 60 gig drive for operating system so this this all four virtual machines are running on windows server 2019 standard operating system you can see here windows server microsoft windows server 2019 is standard so this is my four virtual machines which i am going to use in this environment so now we need to discuss about the networking part and next page so this is ip address configuration for cluster node so i got two uh cluster nodes in my in our demo so we got four next so the first nick is for domain this is basically a domain connectivity with my domain which is labs and so on.com so this is my domain labsonson.com so the first nic is for domain connectivity so this require everything like dns configuration uh gateway configuration all other configuration so just to show you this is my domain connection you can see i have configured ip address gateway and dns so this is going to be a outside connect on my domain infrastructure or lan infrastructure so next machine is neck is our heartbeat network which is basically having no gateway or no dns so you can see so this is only having ip address so this is basically heartbeat a network nick you can say uh which is going to be a use for the windows failover cluster to check the heartbeat of the both sql nodes so i got heartbeat network on this machine also and then next next nick is going to be used for live migration you can see here this is going to be a basically a cluster network which is going to going to be used during the live migration from no sql db instance from one node to another node so i called it here live migration you can see this having only ip address configure so this is also a direct connectivity you can say and last one i have iscsi network which is basically also not having dns or gateway so since this is a lab environment so i don't require any on gateway or dns address for storage so you can see here both both uh both sql nodes are con pre-configured with the ip address and join in my domain so this is ip address configuration so now let's let's move to requirement page so this is the requirement for sql server 2019 cluster so the first thing which you need pre-installed windows server machine it it may be a physical or virtual and you need to join that machine and domain so this we already have we have installed and configured so i be configured on all next on sql node so we have already this done so next you need a service account for sql database instance and local administrator rights on sql node cluster so basically you need a service account in your active directory which is going to be used to install and configure and run the service uh run the sql db instance so if i go to my active directory server you can see here if i go to users you can see i got the sql admin account so this is basically my service account you can say which having password never expire and this account is added in both sql notes in a local administrator so it will get the local administrator rights on both the nodes you can see here labs and so on sql admin and you can verify same on the other node so you can see here so this is what you need from the rights perspective and next we got shared storage or learn on both sql nodes which is mentioned in my first slide here you see the shared storage is need to be available on both so we uh we have not configured it yet so we are going to do it uh during the during this step-by-step installation and configuration and next you need a iso file for sql server 2019 setup so this i already have and last one is you need a dns a host record pre created for windows failover cluster and sql db instance so i am going to create this now so to create this i'm going in my actual directory server and going to open the dns management console so if you don't have rights on a dns server and your sql dba you can request your active directory team to create this dns record for you before you go ahead and install the cluster windows fade over and sql cluster so this is the wind sql zero one so i'm going to create it when sql plus zero 1 and the ip address is going to be a 192 168 1.99 you can see here this is our dns so i am going to create so you can see now it's created and next i'm going to create sqldb01 so sqldb01 this is going to be our instance name for sql database so 100 so both the dns record is now created you can see uh and i got the reverse lookup uh record also created you can see 99 for the 99 ipads and 100 so we got both dns record created and now the requirement part is completed basically we can go ahead and uh to perform step by step installation and configuration for sql server 2019 so first step is configure ip address on all next and join sql server in domain so i i marked as a green because we have already completed this so next part next step basically failover clustering and multipath i o on both sql nodes so basically we need to install failover clustering and multipathing so i'm going on my sql node one first and then go to manage add role and feature so click next so these both are part of features so just go on feature page here you can see i'm on feature page and select windows fade over clustering and you need to scroll down and you will see multipathing i o so this is required if you um if you are using any hba basically or any uh since you are basically going to have a access on the same time on the shared disk which is going to be accessed by the node 1 and node 2 both so you require multipathing to allow this to be happen so you can see provide support using multi data path on storage device windows so go ahead and install this so i'm going to restart the destination server automatically if required so this is going to automatically restart my machine so same i am going to perform my sql node 02 so go to feature space and windows failover clustering and scroll down select multipathing i o click next and restart the destination server automatically if required and go ahead and now you can see the installation on a sql zero when it's going to be completed soon and then this machine is going to be automatically reboot so you can see now this machine sql01 is rebooting and same z sql zero t is also going to reboot soon so it's now booting and sql 02 is also located so i got login screen on sql 01 so i'm going to login with my credential with the service account which we have configured so i'm using service account to log into the sql nodes so you can see installation succeeded so this is good news and let's login on sql 02 next task is going to be a install and configure iscsi target server and create learn so i'm going to create this now so i need to go to on storage server but if you are our sequel admin or windows admin and you are creating a windows fader cluster or sql failover cluster you don't require to perform this install and configure iscsi target server because this is something which needs to be done by storage team and definitely if you are managing the storage devices then you need to create a learn for this windows failover cluster and sql cluster to be used so i'm going to do it for the lab purpose here and if you don't have the dedicated uh storage device in your organization or in your lab then definitely you can go ahead and install this so from the role this is going to be a role basically and i'm going to select win2k19 san and if you expand this file and storage services and file services you will see here iscsi target so i'm going to install this iscsi target server on this machine and just go ahead and install it so this is going to be a simple installation it's not going to take much time so just to tell you we we got one uh dedicated around two terabyte of disc for iscsi storage where we are going to create the lunch and going to store those lunch here so let's wait for the installation to be finished then i will create the learn so installation succeeded we are good now i'm going to click from the dashboard of the server manager i'm going to click on file and storage services and here we got iscsi so now you can see to create iscs start a new virtual disk wizard so i'm going to click on it and then select the iscsi virtual disk location so i'm going to save it on d drive so click next and then what is the desk name called so sql i'm going to call it coram disk so this is going to be basically a witness a witness disc for the windows failover cluster so i'm going to give it one gig only and then we need to assign the new iscsi target since we don't have any existing target so i'm going to click on next and then i'm going to call it basically prod sql nodes and i'm going to add both uh sql nodes ip address in access node so this is going to be there basically storage network since the storage net is going to be access this iscsi server on for the target uh iscsi target so this is the ip address on both node so 10.10.10.111 and second one is going to be 112 so i'm going to click on okay so now you will see both the ip address is added so we are good just don't require any enable any champ author authentication so just click on create now you can see the iscsi virtual disk is created and target is also created and now we need to create one more disk so for the sql installation database where we are going to store the database so now select the d drive and call it sql disk01 and click next and then i'm going to give it like 501 gig size and click next and i'm going to use same broad sql nodes just click on create and now we got two disk created for sql cluster so this is good we have created successfully um we have installed and configure iscsi target and created our lens and this is we have already done create dns record in our last step so create dns record for windows favorite cluster and sql database sensor it's already created and now we need to enable the multi pathing and restart both nodes so basically by enabling the multipathing you are going to tell server like you need to have a multipathing um feature enabled so i'm going to click on tools and since we installed the multipathing and windows failover cluster manager cluster you can see both is added now and i am going to click on mpio and then on the discovery multipathing path i am going to click add support for iscsi devices since i am using iscsi target so click on add you can see operation completed successfully and this is automatically going to uh reboot sometime your machine since this is 2019 this does not require i guess but i recommend you to go ahead and restart your machine after enabling the multipathing so i'm going to do it on sql node 2 so this is done and now i have rebooted both nodes so going to login on sql node 01 and the next step is discover iscsi target and add a disk on both sql nodes so this is the next step so first let's login on both sql nodes and now i have logged in so i am going to click on i squash initiator to connect the disk and this is going to say like microsoft uh microsoft excel server is not running this is required to be start and also if you automatically want to restart this service each time click yes button so yes now it's going to launch the iscsi initiator you can see here iscsi initiator is create open now and you can see we got only one disk which is operating system disk and now i'm going to add the target which is 102 so this is my basically a server ip address iscsi target server which is having the storage connectivity so now click on connect and you will see this is got connected and now you can see we got two disk here so first one i'm going to bring both the disk online and then i'm going to insert both the disk so now i'm going to create this uh first disk as a column disk so i'm going to assign this disk letter q so quorum disk and i'm going to format it and the second disk i'm going to assign s letters so this is going to be used for sql so sql disk 01 so now i have added disk on sql01 node and now need to repeat the same step on the other node so if i open the disk management you'll see it's got only one disk now so we need to launch the iscsi target initiator basically and click on yes and now i am going to provide the path so 192 168 1.102 uh this is going to be 10.10.10.102 and click connect so now you can see we got two disconnected but we don't require to create a partition just bring online you can see it's got the desk letter assigned so i want to correct the disk letter so qrm disk is going to be use q and this sql disk is going to be use s so this is corrected now so if you now go on both the sql nodes you will see the q and s disk is available on both the sql nodes just let's verify one so you can see so these two sql nodes having now same disk available so now we need to create a windows failover cluster and modify the cluster disk and network settings so now i'm going to create the windows failover cluster so from any of the two nodes you can basically create this so i'm going to click on from the sql node 01 i'm going to click on tools and windows failover cluster manager and then then basically i am going to you can directly create a cluster and you can run the validation also so i'm going to create a cluster with the validation so you just need to create right click on this windows failover cluster manager and then create cluster then click next and now you need to search for the nodes so i'm going to search for my two nodes so sql check name so you i got both the sql nodes here you can see so i'm going to add both here and you can see both the sql nodes is added now so click next and this is going to be a validation so i guess i want to proceed with the validation so it's going to i recommend strongly recommend to go with the validation for the cluster node so it will tell you if if there was any issue related to windows failover cluster services or any uh you are missing any configuration or anything so go ahead and run the test so this is before you begin so this is validate a cluster wizard so click next and now what type of test you want so i'm going to use run all test recommended so click on next and now it's going to run lots of tests so i recommend you to click on next and wait for some time so this is going to take some time while uh checking all the parameters for the windows fade over cluster so you need to wait for some time for this once uh this validation wizard to be finished and then you can see the results so i'm going to pause the recording since might be it's going to take two or three minutes to complete the validation and then once that validation is finished i'm finished i'm going to resume from there so now you can see everything got validated and we got most of the things successful so i recommend you to go ahead and click on view report to analyze all the reports and also i recommend you to save this report handy so you can refer this report in future if you need so you can see inventory got successful network got successful in storage and system configuration we got some warning so let's click on storage and see what warning we have so in storage i can see there was one uh warning related to my uh microsoft ampu-io based test so you can see disk 0 is unable to use the storage target path so this is this is fine since a disk 0 is let's verify once so so it's available disk 0 is basically my local disk so this can be ignored so now next if if you talk but mostly this is going to be you need to make sure this you don't have any storage warning also and and if you have any warning in storage i recommend you to go and fix the issue before you come so and actual windows failover cluster so now let's move to system configuration you will see everything is passed only the software level is having warning since the windows update is not done on both the sql nodes so if if you follow the correct resolution path like you need to patch your machine and you need to fix and if you having any storage like uh warning then you can fix those issues and you make sure everything is got success succeeded so you can go ahead and create the cluster however for me also this is going to be a fine so i'm going to click on finish now you will see create cluster name is showing here so in my um environment this is my cluster name and this is the ip address which i'm going to use so wind sql 01 so i'm going to type here so when sql plus zero one this is my going to be my cluster um hostname and 99 is ip address so i already having dns record created for this so i'm good so click on next and now you will see here a small checkbox which is add all eligible storage to the cluster so make sure you you mark that tick mark has been done on this basically small icon so click next and now you will see this is going to create your windows fade over cluster role so you can see forming cluster this and now same time i recommend you to go and in active directory and verify where you have a servers you will see one fade over cluster computer object created with the same name which you have done so now you can see on the sql server you have successfully completed cluster wizard so go ahead and click on finish and now we got our cluster wizard a cluster a windows favorite cluster and you will see here ip address name and disk all the information so now you need to go on storage and fix your disk name so this is going to be our witness so i'm going to call it like column disk so this is going to be quorum disk and the below one is going to be sql disk one so sql disk zero one so click on apply okay so we are good and next thing you need to rename the network so i'm quickly going to rename this also so this is going to be my storage network and this is going to be my heartbeat network so heartbeat and this is going to be my live migration so i'm going to mark this live migration and this is going to be domain connectivity so market market is as a domain then you have a small icon here or to set the live migration next so i'm going to click on this and i'm going to only enable live migration on this snake so this is going to be our live migration nick so click apply so okay so we are done with the configuration of the windows failover cluster so we have completed this seven tasks now and now we are finally to on the step to install sql server uh 2019 fee on a failover cluster on sql node first which is sql01 so i'm going to mount the installation media here so from the cd drive i'm going to use my iso repository microsoft and then i got sql and i got sql server 2019 so open this now you will see we got this sql installation disk so i'm going to run this disk set up from this disk so now you need to click on install and then you got here um new sequel server class fade over cluster installation so now i'm going to click i accept the license terms click next and then it's going to check some prerequisite and i'm going to untick this microsoft check for update now for now so now we are good to proceed with the install setup file so it's going to be ready soon so now you can see it's uh install failover cluster rule status is running so everything got passed a few things like windows firewall is missing so i can go ahead and click on next and then i'm going to select here the main role which is database engine services and when you click this you can see the sql server replication and few others are automatically going to select so i'm going to select with the default one so this is the basically going to be installed root for the sql database server on this machine itself but we require to configure the database engine configuration letter where we are going to uh define that disk so it's going to do some pre-request installation it's you can see already installed so we are good so it's going to give you the disk size also so we are good just going to click on next so now it's going to proceed with the feature and now you can see this equals sql server uh network name so here we have already created the dns record which is here sqldb01 for instance name you can see so i'm going to call it like sql db 01 is going to be my network name or cluster object name for this uh instance and the day for instance name i'm going to leave default mysql server so click next and then it's going to check the cluster resources like disk other other things and network configurations it's going to take few seconds here so you can see everything is looking good so global storage we got available storage and this is also good so we can go ahead and click on next now you can see the quorum disk is cannot be used but we got sequel discovers equalities which we we made available on both the sql nodes so now i'm going to click on next this is automatically selected so click next and then you got option to assign the ip address so i'm going to put it here 192 168 1.100 which we have mentioned here if you go back you can see this is the ip address and this is the database name so go ahead and click next and now you can see it's asking for the service account name so i'm going to provide my service account name here let's hands-on secual admin so i'm going to copy this and going to put my password for the service account so one thing you don't need to make the changes in startup type leave it uh as it is and you need to perform uh grant perform volume maintenance task favorites for sql database engine services so click next and now you need to add the windows authentication or sql authentication so i'm going to add my current user since this is a sequel um basically a sql service account so i'm going to add this and if you go to directories you can see it's automatically going to take that s drive as a data directory since since s drive is available on both the node and we have selected through the uh cluster disk selection so we are good rest you can see other details also time is also going to be saved in s so if you need some modification in data directories you can do it right away so now we can go ahead and click next and now you can see everything is ready just we need to click on install so now you can see the installation is started and it's going to take around five to ten minutes in on my machine uh to get this installation finished so now if i go here you can see we got both both nodes equal in one and sequel two and you can see here uh we don't have any role now so once this installation is finished and completed successfully we will soon see the sequel um db01 uh instance here uh basically a failover instance here so while editing feature setting it's going to take some time a few seconds to get the installation started so now you can see it's started so i'm going to pause the recording uh till the this installation is uh going on so i will resume once the installation is done so now you can see the installation is succeeded everything looks good just go ahead and click on close and now you can close the setup also which is running on this machine and now if i go here on my or this sql node 0 1 you can see here this sql server my sql server instance is running with the name sqldb01 and this ip address which we have configured so this is good and now you can see uh since the sql server is only installed on node one we need to install on note 2 also so now i'm going to install uh this one note 2 so connect the virtual disk and then go to the library and select the 2019 now you can see the disk is going to be mount so i'm going to run this installation wizard and this time and this is basically we have verified the sql server on a failover cluster and now we are on the last step which is add and install second node to top we took a 19 sql 0 on uh 02 on sql failover instance so this is what we are doing now so click on install and now you need to uh previously we have clicked on this new sql uh server failover installation and now we need to add in existing cluster so add node to sql server fade over cluster so just click on this this is going to be a small installation so let's click on next and now accept the agreement click next and going to skip the updates for now now basically it's going to copy the installation setup file in a local library local disk and then it's going to start from there so everything is looking good here we passed all the all the checks here we got two warnings so we can ignore it for now it's related to firewalls so now you can see uh this is going to automatically detect the sql phase over instance which is running in this cluster since this is also part of the same windows cluster where the sql database is running so we are good from here just close this and now you can see node this is on on nodes equal ones for now so name of the this node is equal 0 to so go ahead and click on next and then it's going to take everything automatically it's you you don't need to put anything like storage and all details manually you can see here this is going to take the ip address also so you don't need to configure the ip address also so click on next and now you need to put the password for service account with the sql server sql services running so i'm going to type my password and click next and now we are just one click away from the installation so click on insert and now the installation is going to be started so it's going to take another five to ten minutes to get the installation done and once the installation done i will uh try to fail over the sequel instance from cluster one to cluster two to just verify everything goes well and we have successfully created the sql server cluster so just waiting to finish this installation then we will try to failover then sql instance so pausing the recording for now so now you can see the installation is finished on our note 2 also so go ahead and click on close and we can close this installation wizard also now and now we are good to go and failover uh for the failover test so i'm going to migrate this uh rule from sql node 1 to sql node 2 so just select the node and click ok and now you will see this is going to bring all the resources offline and then going to be bring the uh this sql node sql db instance on node 2 so you can see now owner node is 02 and it's bringing all everything on and now you can see everything now running on sql node 2 so let's move once again to one so i'm going to move it back so making everything offline now it's bringing the instance online on window k19 01 so this is done now so this is how we can create the windows server based uh sql server 2019 failover cluster so thank you for watching and please subscribe me for more videos and if you have any query any question related to this you can always post him in my given meladies thank you once again bye

Channel: Labs Hands On

Views: 10,124

Keywords: sql server failover cluster step by step, sql server failover cluster installation, sql server failover cluster instance, sql cluster, sql server failover clustering, how to setup sql server failover cluster, ms sql server failover cluster, sql failover cluster installation, ms sql cluster failover, sql cluster failover steps, sql server failover cluster configuration, install a sql server failover cluster, sql server 2019 cluster setup, sql server 2019 big cluster, SQL Failover

Id: euSiQFcyVPg

Length: 41min 50sec (2510 seconds)

Published: Sun Sep 06 2020