SQL Server Denali - AlwaysON (HADR): Step-by-Setup setup guide

As you might be aware that SQL Server Code-Named “Denali” has introduced a new feature called Always ON for enhancing the availability of user database in a enterprise environment. In simple terms, AlwaysON is a high-availability and disaster recovery solution, which will enable you maximize availability of your critical user databases by use of “availability groups”

WARNING: The blog-post is based on pre-release software so things could change. For more details on CTP, please refer SQL Server Code-Named "Denali" CTP1 Release Notes

So what are “Availability Groups”?

Availability groups are set of failover partners (a.k.a availability replicas) to provide high availability to user database in Denali. Sure, this is lot more in depth and I suggest reading Denali Books-on-line for details.

In this blog post, I will guide you on how to configure AlwaysON – STEP-BY-STEP? Lets get going then !

Step 1. Windows OS and Cluster Configuration

a. For the purpose of testing, I have created a Windows 2008R2 two NODE cluster on virtual machine using Hyper-V and a Domain Controller

Machine configurations:

Machine Name

Role

OS

IP Address

INDNUKE- BANGALOREDC

Domain Controller

Windows 2008R2

192.168.1.1

INDNUKE- DELHI

Cluster Node 1

Windows 2008R2

192.168.1.2 Public X.X.10.1 Heartbeat

INDNUKE-MUMBAI

Cluster Node 2

Windows 2008R2

192.168.1.3 Public X.X.10.2 Heartbeat

If in case you need step-by-step guide on setting up windows cluster using Hyper-V (Guest Clustering), please refer my team blog:

https://blogs.msdn.com/blakhani/archive/2010/04/01/how-to-install-clustered-sql-server-2008-on-windows-2008-cluster-using-hyper-v-guest-clustering-part-1.aspx
https://blogs.msdn.com/blakhani/archive/2010/04/28/how-to-install-clustered-sql-server-2008-on-windows-2008-cluster-using-hyper-v-guest-clustering-part-2.aspx

Step 2. Install “Standalone” Denali instance on both the nodes

Yes, you’ve heard it right. AlwaysON is NOT a clustered SQL Server Instance. As such a standalone SQL Server setup is required on both the clustered nodes.

Standalone

I’ve installed 2 DEFAULT instances on both the clustered nodes as below:

INDNUKE-DELHI\MSSQLSERVER

INDNUKE-MUMBAI\MSSQLSERVER

Step 3. Enable AlwaysON feature on both the instances

•Open SQL Server Configuration Manager
•Select SQL Server Services
•Right-click on your SQL Server, in my case SQL Server (MSSQLSERVER) and select Properties
•Select SQL HADR TAB and check "Enable SQL HADR service"

clip_image002

Note: SQL Server service needs to be re-started for changes to take affect

Step 4. Create a sample database on both SQL Server Instances

For this testing, I will be using AdventureWorks sample database

Step 5. Create Availability Group

•Choose any one instance to become PRIMARY (say INDNUKE-DELHI\MSSQLSERVER)
•Open SQL Server Management Studio on INDNUKE-DELHI\MSSQLSERVER
•Expand Management folder
•Right-click Availability Groups and select New Availability Group

clip_image003

•Click Next on Introduction Screen

 New_availabilty_grp

•Provide a name to Availability Group

availability_grp_name

•Select the user database to be added to Availability Group

 select_database

•Specify Replicas – Add the other instances (INDNUKE-MUMBAI\MSSQLSERVER) to assume the role of secondary of this user database

 specify_replica_server

•Optionally, you can specify endpoint details (similar to database mirroring) or leave them default

 endpoints

•Summary Screen

 summary

•Result Screen

 result_screen

IMP: Once done, you will be able to see the Availability Group as a Cluster Resource in Windows Server Failover Cluster Manager

 availability_grp_as_cluster_resource

Step 6. Start Data Synchronization

•This step will allow you to synchronize your user PRIMARY and SECONDARY server by taking backup of user database and restoring this on secondary server 

 start_data_sync

•Specify a shared location, where both SQL Server start-up accounts has read/write access and press OK

•Once synchronization completes, expand the tree and you will be able to see the Availability Replicas (along with current role ) and Availability Databases

see_availibility_replica

 

Step 7. Test the Availability Groups

•To understand how failover functions in AlwaysON, follow simple steps

1. Create a simple table (can use below script) on PRIMARY (INDNUKE-DELHI) and insert some rows (can use below script)

Use AdventureWorks
GO

CREATE TABLE [dbo].[New_Table](
[ID][int] NULL,
[NAME] [varchar](50) NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.New_Table values (5001, 'NORTH')
GO

SELECT * from dbo.New_Table
GO

 create_insert_on_demo_table

2. Then, connect to SECONDRY (INDNUKE-MUMBAI) and try selecting the rows. This will work!!

 select_on_secondry

Step 8. Test the Failover

•To FAILOVER, follow below steps

Before FAILOVER

 force_failover

After FAILOVER – server roles will change

after_failover

 Conclusion,

AlwaysON by design looks similar to database mirroring, but is actually a combination of both databases mirroring and clustering. The goal is to maximize the database availability and also give benefits of disaster recovery. And we term this – AlwaysON!!!!

Varun Dhawan
Support Engineer ,Microsoft SQL Server Support

Reviewed by

Balmukund Lakhani, Nikesh Mhatre
Tejas Shah, Amit Banerjee (SQL Server Escalation Services)