How to Implement HADR (a high-availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots

Leave a comment (7) Go to comments

SQL Server Denali (2011) has introduced a new feature named "Availability Group"

"HADR" is a high-availability and disaster recovery solution introduced in SQL Server 2011 Named "Denali". Deploying "HADR" involves configuring one or more availability groups.

Note :This Post is focused specifically on the "How to set up HADR", please check other previous posts to understand concept of HADR or Download the Microsoft Presentation .

Infrastructure Required To test the HADR / High Availability Group

  1. Domain Controller for Authentication
  2. Two Virtual Machines with Windows Server 2008 R2 with Failover Clustering installed on each Node.
  3. Local Instance of SQL Server (Not Clustered Instance) installed on Virtual Machines with Windows Server 2008 R2 (which are mentioned in point 2)

In this Demo, this was the setup configuration, which I used


  • Configuration for Domain Controller
  • RAM : 512 MB
  • Windows Server Name : DC
  • Domain Name : DEMO
  • IP Address : 192.168.0.1

Configuration for Virtual Server – 1

  • RAM : 1 GB
  • Windows Server Name : DENALI-SQL
  • Windows Failover Cluster Name : DENALI_CLUSTER
  • SQL Instance Name : DENALI-SQL\INST1
  • Member of Domain : DEMO
  • Note :
    • This was One node Cluster
    • As this a One node cluster no need to create a shared storage
    • Server IP : 192.168.0.11
    • Failover Clustering IP Address : 192.168.0.15
    • SQL Server Cluster IP Address : 192.168.0.12

Configuration for Virtual Server – 2

  • RAM : 1 GB
  • Windows Server Name : DENALI-SQL2
  • Windows Failover Cluster Name : DENALI_CLUSTER2
  • SQL Instance Name : DENALI-SQL2\INST2
  • Member of Domain : DEMO
  • Note :
    • This was again a single node Cluster
    • As this a One node cluster no need to create a shared storage
    • Server IP : 192.168.0.21
    • Failover Clustering IP Address : 192.168.0.25
    • SQL Server Cluster IP Address : 192.168.0.22

Note : All Servers are in same subnet of 255.255.255.0 and domain administrator account is being to complete this configuration.

STEP By Step Guide to BUILD HADR (AlwaysON)  in SQL Server DENALI

Step 1 : Enable the HADR (AlwaysON) service on both clustered DENALI instances

  • Open SQL Server Configuration Manager
  • Select SQL Server Services
  • Right-click on your SQL Server, in my case SQL Server (INST1) and select Properties

How to Implement HADR (a high availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots vailability Group LAB sql server sql server hadr lab sql server hadr example sql server denali HADR sql denali high availability options sql denali hardon sql denali ha options sql denali ha option sql denali DR options implement HADR How to Implement HADR High Availability Group sql server High Availability Group hardware requirement for hadr sql server denali hardware requirement for Availability Group sql server denali hadr lab sql server denali hadr implement sql server Enable the HADR service disable HADR service denali HADR delete Availability Group sql server denali availibility group sql server denali Availability Group LAB Availability Group example sql server Availability Group example

  • Select the SQL HADR tab and click the checkbox Enable SQL HADR Service

How to Implement HADR (a high availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots vailability Group LAB sql server sql server hadr lab sql server hadr example sql server denali HADR sql denali high availability options sql denali hardon sql denali ha options sql denali ha option sql denali DR options implement HADR How to Implement HADR High Availability Group sql server High Availability Group hardware requirement for hadr sql server denali hardware requirement for Availability Group sql server denali hadr lab sql server denali hadr implement sql server Enable the HADR service disable HADR service denali HADR delete Availability Group sql server denali availibility group sql server denali Availability Group LAB Availability Group example sql server Availability Group example   

  • Click Ok on the warning dialog box

How to Implement HADR (a high availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots vailability Group LAB sql server sql server hadr lab sql server hadr example sql server denali HADR sql denali high availability options sql denali hardon sql denali ha options sql denali ha option sql denali DR options implement HADR How to Implement HADR High Availability Group sql server High Availability Group hardware requirement for hadr sql server denali hardware requirement for Availability Group sql server denali hadr lab sql server denali hadr implement sql server Enable the HADR service disable HADR service denali HADR delete Availability Group sql server denali availibility group sql server denali Availability Group LAB Availability Group example sql server Availability Group example

  • Restart your SQL Services (using Failover Cluster Manager, select your SQL Server Instance, and take it offline. When everything is offline, bring it online again)

Step 2 : Create an Availability Group

  • Open SQL Server Management Studio on DENALI-SQL\INST1
  • Select Management
  • Right-click Availability Groups and select New Availability Group

How to Implement HADR (a high availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots vailability Group LAB sql server sql server hadr lab sql server hadr example sql server denali HADR sql denali high availability options sql denali hardon sql denali ha options sql denali ha option sql denali DR options implement HADR How to Implement HADR High Availability Group sql server High Availability Group hardware requirement for hadr sql server denali hardware requirement for Availability Group sql server denali hadr lab sql server denali hadr implement sql server Enable the HADR service disable HADR service denali HADR delete Availability Group sql server denali availibility group sql server denali Availability Group LAB Availability Group example sql server Availability Group example   

Click Next

How to Implement HADR (a high availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots vailability Group LAB sql server sql server hadr lab sql server hadr example sql server denali HADR sql denali high availability options sql denali hardon sql denali ha options sql denali ha option sql denali DR options implement HADR How to Implement HADR High Availability Group sql server High Availability Group hardware requirement for hadr sql server denali hardware requirement for Availability Group sql server denali hadr lab sql server denali hadr implement sql server Enable the HADR service disable HADR service denali HADR delete Availability Group sql server denali availibility group sql server denali Availability Group LAB Availability Group example sql server Availability Group example

  • Give your new Availability Group a name and click Next

How to Implement HADR (a high availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots vailability Group LAB sql server sql server hadr lab sql server hadr example sql server denali HADR sql denali high availability options sql denali hardon sql denali ha options sql denali ha option sql denali DR options implement HADR How to Implement HADR High Availability Group sql server High Availability Group hardware requirement for hadr sql server denali hardware requirement for Availability Group sql server denali hadr lab sql server denali hadr implement sql server Enable the HADR service disable HADR service denali HADR delete Availability Group sql server denali availibility group sql server denali Availability Group LAB Availability Group example sql server Availability Group example

 

  • Select which user databases you want to add to your Availability Group. Then click Next

How to Implement HADR (a high availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots vailability Group LAB sql server sql server hadr lab sql server hadr example sql server denali HADR sql denali high availability options sql denali hardon sql denali ha options sql denali ha option sql denali DR options implement HADR How to Implement HADR High Availability Group sql server High Availability Group hardware requirement for hadr sql server denali hardware requirement for Availability Group sql server denali hadr lab sql server denali hadr implement sql server Enable the HADR service disable HADR service denali HADR delete Availability Group sql server denali availibility group sql server denali Availability Group LAB Availability Group example sql server Availability Group example

 Note : If your database is not listed, you can select Show user databases not meeting requirements.

In the Specify Replicas screen, you can add the instances you want to be enable as HADR in the secondary role, after you have done this, click Next

How to Implement HADR (a high availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots vailability Group LAB sql server sql server hadr lab sql server hadr example sql server denali HADR sql denali high availability options sql denali hardon sql denali ha options sql denali ha option sql denali DR options implement HADR How to Implement HADR High Availability Group sql server High Availability Group hardware requirement for hadr sql server denali hardware requirement for Availability Group sql server denali hadr lab sql server denali hadr implement sql server Enable the HADR service disable HADR service denali HADR delete Availability Group sql server denali availibility group sql server denali Availability Group LAB Availability Group example sql server Availability Group example  How to Implement HADR (a high availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots vailability Group LAB sql server sql server hadr lab sql server hadr example sql server denali HADR sql denali high availability options sql denali hardon sql denali ha options sql denali ha option sql denali DR options implement HADR How to Implement HADR High Availability Group sql server High Availability Group hardware requirement for hadr sql server denali hardware requirement for Availability Group sql server denali hadr lab sql server denali hadr implement sql server Enable the HADR service disable HADR service denali HADR delete Availability Group sql server denali availibility group sql server denali Availability Group LAB Availability Group example sql server Availability Group example

  • Next you have the overview screen, click Finish to start configuring the HADR setup

How to Implement HADR (a high availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots vailability Group LAB sql server sql server hadr lab sql server hadr example sql server denali HADR sql denali high availability options sql denali hardon sql denali ha options sql denali ha option sql denali DR options implement HADR How to Implement HADR High Availability Group sql server High Availability Group hardware requirement for hadr sql server denali hardware requirement for Availability Group sql server denali hadr lab sql server denali hadr implement sql server Enable the HADR service disable HADR service denali HADR delete Availability Group sql server denali availibility group sql server denali Availability Group LAB Availability Group example sql server Availability Group example

  • click FINISH to finish the HADR setup, where you will get a progress screen

How to Implement HADR (a high availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots vailability Group LAB sql server sql server hadr lab sql server hadr example sql server denali HADR sql denali high availability options sql denali hardon sql denali ha options sql denali ha option sql denali DR options implement HADR How to Implement HADR High Availability Group sql server High Availability Group hardware requirement for hadr sql server denali hardware requirement for Availability Group sql server denali hadr lab sql server denali hadr implement sql server Enable the HADR service disable HADR service denali HADR delete Availability Group sql server denali availibility group sql server denali Availability Group LAB Availability Group example sql server Availability Group example

This Completes you HADR Implementation.

EOF - How to Implement HADR (a high-availability and disaster recovery solution) in SQL Server ? Step by Step Guide with screenshots, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

7 Comments.

  1. How are you supposed to restart the SQL Services with the Failover Cluster Manager when the SQL was installed as a NON-Clustered resource?
    Thanks!

  2. A Great writeup is being posted by one of community member Remus at :
    http://rusanu.com/2010/11/11/alwayson-high-availability-and-reads-scale-out

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.