Configure AlwaysOn High Availability
This configuration has three servers Server1 (Primary Replica), Server2 (Secondary Replica Synchronous), Server3 (Secondary Replica Asynchronous)
Step1: Enable AlwaysOn High Availability Group on SQL Serer Instance on which you want to configure.
Open SQL Server configuration right click on your SQL Server Instance and select “AlwaysOn High Availabilty” tab.
this tab will show you “Windows failover cluster name”, here i have configured with “AGTesting”.
this option only available if you have created cluster if you have not configured windows clustering then follow this link to configure.Validate and Create Windows Cluster
Step2 : Connect primary server (here Server1) Right click on “AlwaysOn High Availability” and select “New Availability Group Wizard”.
Step3 : On New Availability Group specify availability group name.
Step4 : On “Select Databases” You can select number of databases here but you must have to take Full backup of database for which you want to add in Availability Group.
This wizard will show you all databases and we are going to configure “AGDatabase”
It shows database select is disabled in below screen as i have not taken any full bakup, so take a full backup to enable database selection.
Step5 : After taking full backup database is ready to select. click on checkbox and go to “Next”.
Step6 : On “Specify Replica” specify list of primary and secondary instance by clicking on “Add Replica” Button.
–> Server1 is primary server.
–> Server2 is secondary server with Synchronous commit and automated failover (this means Server2 automatically converts to primary server if Server1 is down).
–> Server3 is secondary server with Asynchronous commit and this is server is available as readonly mode(You can execute select query on this database).
Step7 : On “Specify Replicas” window select “Enpoints” tab. AlwaysOn High Availability uses endpoint “5022” and it must be enabled in firewall on all 2 servers.
Step8 : On “Specify Replicas” window select “Backup Preferences”.
AlwaysOn Availability provides 4 type of backup options to overcome the load of primary server.
–>Prefer Secondary : this option will perform backup on secondary replica on first priority if no secondary available then backup will perform on primary replica.
–>Secondary Only : this option will perform backup on only secondary replica.
–>Primary : Backup occurs on primary replica. this option has last priority as this option will created load on primary server.
–>Any Replica : Backup can occur on any replica.
“Prefer Secondary” option is most deserving option in AlwaysOn, so here we are going with “Prefer Secondary” option.
Leave “Listener” tab configuration for now will configure it later and click on “Next”
Step9 : Provide shared location for initial data synchronization.
Step10 : Just observe “Validation” and ignore warning for listener.
Step11 : Result and close.
Step12 : Now you have completed “SQL Server 2012 AlwaysOn High Availability Configuration” on your system.
Expand AlwaysOn Availability Group and you will find “AGSample” is created as Availability Group.
Now connect all 3 servers and check database status.
Thanks & Regards,