Configure Backup on Secondary Replica

Configure Backup on Secondary Replica.

AlwaysOn Hish Availability Groups provides Backup on Secondary database to overcome load on Primary Database.

As Secondary database is Read-Only replica so you can’t execute any code directly, you need to create Maintenance Plan for backup.

AlwaysOn High Availability Groups only Provides “Copy-Only Backup” at Secondary replica, it means differential backup is not available at secondary replica.

Configure backup on Server2.

Step1 : Expand “Management” and click on “New Maintenance Plan..”



Step2 : Apply proper name to maintenance plan and configure regular schedule.

Click on change will create a job same maintenance plan name and you can define a schedule to execute job and run it manually as per your need.



Step3 : On the select “Back Up Database  (Full)” on Select Maintenance Tasks windows and click on “Next”.



Step4 : Select “AGDatabase” on “Define Backup Database (Full) Task”.



Click on Check Box  “Copy-Only Backup” and provide backup folder path.


–> Select “Report Options” and click on Next.

–> Click on “Finish” to complete the wizard and close it.


Thanks & Regards,

Nirav Gajjar


AlwaysOn High Availability Manually Failover

AlwaysOn High Availability Manually Failover

In my previous blog i have written on AlwaysOn High Availability Automatic Failover (From Server1 to Server2) and now in this blog i will show you AlwaysOn High Availability Manually Failover (Back from Server2 to Server1)
Step1 : After Automatic Failover Server2 is working as Primary Replica and Server1 is Secondary Replica (Synchronous).

Expand Availability Groups at Server2 –> Right click on “AGSample” and click on “Failover”.

Step2 : You have 2 available Replica to make it as Primary Replica.

Here we are going back to Server1 as primary Replica.


You can select any of replica from Server1 and Server3 but Server3 is Asynchronous commit mode and it has possibility of data loss.
Step3 : Provide connection to Server1 to Connect Replica.

Step4 :  Check summary and click on “Finish”.

Step5 : Verify Result.

Step6 : Now Backup to Server1 as Primary.



Server1 is now again working as Primary Replica and Server2 is Secondary and server1 has now Automated Failover server Server2.



Thanks & Regards,

Nirav Gajjar

AlwaysOn High Availability Automatic Failover

AlwaysOn High Availability Automatic Failover

Always on High Availability provides automatic failover up to 2 secondary server while you configure Availability Group and Server2 is selected as Automatic Failover Replica without any data loss.

Note: If you are frequently restart your server or SQL Server service then this option is not advisable.


See the current scenario.

–>Server1 is Primary Replica.

–>Server2 is Secondary Replica (With Synchronous and automatic failover)

–> Server3 is Secondary Replica (With Asynchronous and manual failover with data loss)


–>Now stop SQL Server service of Server1 to test automatic failover.


–> Now check


Automatic failover has been occured by AlwayOn Availability Group and Server2 initiated as  Primary Server.

Server3 is now Secondary Replica of Server2 and Server1 seems to be down.

Now when you start SQL Server service of Server1,  Server1 will be Secondary Replica of Server2.

Thanks & Regards,

Nirav Gajjar

Configure Availability Group Listener

Configure Availability Group Listener.

Availability Group Listener is a Virtual Network Name which provides client connectivity for Availability Group database on primary or secondary available replica.

In our scenario Server1 is PrimaryServer, Server2 SecondaryServer with automatic failover.

Here i am going to create Availability Group listener with name “AGSample” which points to Server1, in any case if PrimaryServer fails and SecondaryServer will work as Primary this listener automatically point to Server2.

Step1 : Expand Availability Group right click on “Avalability Group Listner” and select “Add Listener”.


Step2 : Provide Listener DNS Name “AGSample”.


Step3 : Open your Management Studio and provide “AGSample” in ServerName.


Now type “Select @@SERVERNAME” which gives “Server1” as result.

For testing, stop the SQL Server Service of  Server1, by doing this Server1 fails and Server2 come up as primary server.

Again open your Management Studio and provide “AGSample” in ServerName.

Now type “Select @@SERVERNAME” which gives “Server2” as result.

Availability Group Listener Connection String for application

“Server=tcp: AGSample,1433;Database=AGDatabase;IntegratedSecurity=SSPI”

Thanks & Regards,

Nirav Gajjar

Configure AlwaysOn High Availability

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,

Nirav Gajjar

Validate and Create Windows Cluster

Validate and Create Windows Cluster

In this blog i am going to create windows cluster for my AlwaysOn High Availability Group configuration.

I have already enabled and installed failover cluster on all recommended  servers. FailOver Cluster

Step1 : Open Server Manger — Select “Failover Cluster Manager” under Features. Click on “Create Cluster” highlighted in red under Management panel. CreateCluster1

Step2 : This will open “Create Cluster Wizard”.

Read “Before you Begin” and click on “Next”. CreateCluster2

Step3 : Type your server name and click on add. Here i have added Server1, Server2 and Server3. CreateCluster3

Step4 : On the “Validation Warning” click on “Yes. When I Click Next, run configuration validation tests, and then return to the process of creating the cluster” this option will validates the clustering processes available on all server. CreateCluster4

Step5 : This will open “Validation Configuration Wizard” click on Select “Run all tests (recommended)” to check all type of test. CreateCluster6

Step6 : Confirm testing and click on “Next”.


Step7 : See the list of test validating.


Step8: Confirm testing and click on “Finish”.


Step9 : After completion of validating it will come back to “Create Cluster Wizard”.

Now give a name to your cluster. Here i am configuring cluster for availability group so i have created “AGTesting”.

Give any unassigned IP Address from your domain and click on “Next”.


Step10 : This window show you the details of your clustering like ClusterName, List of Failover servers and Cluster IP Address.


Step11 : See message “You have successfully completed create cluster wizard”.

If you want to see a creation report in detail then click on “View Report…” else click on finish to exit the wizard.


Step12 : Now check your cluster environment under ServerManager.

Here you can add or remove cluster node, configure service, configure applications etc…


Thanks & Regards,

Nirav Gajjar