SQL Server 2019 Always On Availability Group Configuration Step by Step Guide

let's configure the always on high availability on SQL Server 2019 first step is to install the software so click on tools and add role and feature wizard enroll in feature wizard click on next next next and you need to go to the features under features you have to select the feature name that is failover clustering so Select failover Plus string and add this feature and then click on next then install so restart the system when required enable this feature so that it will restart the machine if required during the installation installation will start repeat the same step on all your nodes if you have multiple nodes for your SQL Server 2019 you need to repeat this on all the nodes so this is going on on my node 2. now I will do the same thing on Node 1 as well so this is my node one my machine is started I will repeat the same step on node 1. so this is installation of features on node one in the meanwhile let's connect through the SQL Server management Studio and stay connected for rest of the work so in the meanwhile we can verify the machine's installation after the installation is complete or during the installation it will restart the servers both the nodes will be restarted then we will validate right now this is just configuring the SQL Server management studio now you can see the machines are restarting both my machines are restarting after you log in it will take you back to the same wizard now here on server one installation is succeeded on server 2 installation is still going on after restart also and now it is almost complete on server 2 as well so after this clustering software is installed on both of the nodes we will validate and create the cluster so this is succeeded on Machine 2 as well now go to that tools and then click on this cluster management failover cluster management the failover cluster management here you will click on this validate cluster to validate the cluster so the validate Vector first thing is we need to both we need to add both the nodes to the cluster so click on validate class configuration wizard will be displayed validator configuration wizard will be displayed now click on next Now search for the server one and server 2 these are name of my machines remember that these are name of my machine server one is name of my machine uh first machine and server 2 is my server second machine so search and add it will validate it will check the connectivity and it will add both the machines click on next both the machines are added to the cluster now and run all tests it will perform all the tests it will check the IP addresses connectivities and everything you may face an error due to the first IEP address which is net based IDP address 10.0.2.15 if you're using Oracle virtualbox and that IP address is not reachable so do not panic and this is what error which I am getting here because that is only the first IP address which is not reachable that is 10.0.2.15 if you look at this dialog box if you can pause and verify that that is not reachable you know that that IP address is not reachable so second IP address which is host only adapter IP address 10.0 10.10.0.100 those IP addresses are reachable so don't panic this is fine now before you begin create a cluster now now here you specify the name of your cluster give a name to your cluster let's say for example skillpedia is the name I am giving to my cluster and then specify the virtual IP address for this cluster by click on by clicking on this address so click here and specify the IP address keep this IP address different from the rest of the IP addresses like if you have a 10.10.0.100 so make it 10.10.0.102 that will be the IP address for this cluster now click on next yeah this is IP address for this cluster click on next so it will create the new cluster for your both the nodes the nodes which you have added server 1 and server 2. these are the two machines I have running in my Oracle virtual box with static IP address now you can verify the nodes also you can see Server one server two both the nodes are up so high availability the the failover clustering software is installed successfully that cluster is created successfully now we need to enable the always on high availability on my SQL server for that you first verify this all the services are stocked so you need to start all the services after all the services are started you need to change and enable the high availability for the SQL Server service so go to the properties of SQL Server service after restarting all the services let's go to the server this properties for SQL Server service yeah under this always on you have to enable this or enable always on High availability group and this is required for both the machines you need to repeat the same thing on server 2 as well so you can see that all the services are running and always on is enabled same thing you have to repeat on the second machine also so this is my server 2 go to the configuration manager restart all the services all the services are stopped during this installation of the software so start all the services go to the properties enable this always on availability group after this always on availability group is enabled you need to restart the service remember that so restart or if they are stopped start all the services so after all the services are started we are good to go connect to the database engine both the machines you can connect just for verification purpose through our SQL Server management Studio now check the database you have Adventure Works database so you should import it if it is not there from the backup you should have Adventure Works database and one more thing is that option for recoverability that must be full if you don't have recovery recovery model set to full it will not work so the clustering requires High availability required that always don't require that you set the recoverability to always on to full now go to this have on always on high availability and create a group give a name to your all always on availability group let's say tsp group one in my case configure this database Level Health now you see only one database meet the requirement because you have to ensure that the recovery model is set to full add replica server 2 you can see my primary and I'm connecting to my server too also I'm adding the server one also so server one and server two both the replicas are added that because the cluster is already configured so server one server to configured as my replica let's configure the endpoint also these are the end points this is a backup reference nothing too special here listener is required we need to create a listener here create a availability group listener give a name to your listener give a name to your listener and port number one four double three and add the IP address from the same side of block 10.10.0.102 give any name I like triple one in my case and then next next verify everything if you want just take a look once once again and click on next after that once you are satisfied next and we'll display these results and finish so it will configure the high availability group availability group listener and you can see that this will be repeated on both the nodes both my instances I'm in both my servers and exactly you can see that it is configured on both my servers so it is available on my first server as well and as second server as well as so server one and server 2 both have the same group and the database is also replicated immediately you can see the database also availability database if you click on availability database you will see the adventure Works database also you can check this see Adventure Works database is available and same thing you can see here also availability database so my high availability is configured

Channel: The SkillPedia

Views: 23,425

Keywords: sql server always on availability group configuration, availability group, sql server, always on availability groups, availability groups, sql server 2017 always on configuration, sql server 2016 always on configuration, sql server 2014 always on configuration, sql server 2012 always on configuration, high availability, sql setup always on availability group, always on availability group configuration, sql server alwayson availability group configuration

Id: mULFgfp4zgY

Length: 11min 11sec (671 seconds)

Published: Thu Feb 23 2023