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:

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.

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"

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

•Click Next on Introduction Screen

•Provide a name to Availability Group

•Select the user database to be added to Availability Group

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

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

•Summary 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

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 

•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

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

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

Step 8. Test the Failover

•To FAILOVER, follow below steps

Before FAILOVER

After FAILOVER – server roles will change

 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)

Comments

• Anonymous
  December 19, 2010
  What do you mean "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."  The BOL for Denali CTP1 say that you can either use a clustered instance or a standalone instance.  Why would you want to use two standalone SQL Server instances on 2 nodes in the same cluster?  What would happen when there is a failover event within the cluster?

• Anonymous
  December 21, 2010
  Hi Heather, Thanks for pointing this out. Seems we missed it at first place....so here’s the updated details:

1. For configuring AlwaysON, we can we can either use a failover cluster instances or stand-alone instances.
2. The "Two" standalone instances mentioned in this post are just an example for an easier demonstration. So assuming there are 2 standalone instances running on Windows Server Failover Clustering. In the event of cluster failover, there will be a failover from Primary to Secondary replica. Regards, Varun

• Anonymous
  January 01, 2011
  Hello Varun To force FAILOVER do you click on the secondary server? Why not on the primary server?

• Anonymous
  March 10, 2011
  Hi Varun, I've seen it suggested (www.brentozar.com/.../sql-server-denali-database-mirroring-rocks) that you'll be able to run your backup from a replica.  Do you know anything more about how this works? - for eg, would BACKUP LOG on the secondary replica cause the log file on the primary to be appropriately truncated? Thanks, Colin.

• Anonymous
  June 01, 2011
  Hello Varun, Thanks for a very informative post on AlwaysOn. The setup screen shots are really helpful in understanding the HARD setup.

• Pawan | SQL Server DBA

• Anonymous
  August 08, 2011
  Hello Colin First apologies for a late reply as I've been busy leveling some new assignments. Answer to your questions is YES, it’s possible to take LOG backups on a secondary replica in an AlwaysOn (HADR) configuration. A ‘BACKUP LOG’ T-SQL will work as usual on secondary replica/s and all such log backups WILL TRUNCATE T-LOG file on primary. Hope this answers your query. Varun

• Anonymous
  August 08, 2011
  Hi Uri A force causes the secondary replica to which the availability group was failed over becomes the new primary replica. This seems purely a by-design thing that such force failover is initiated from a secondary replica of the availability group. Varun

• Anonymous
  January 01, 2012
  Hi Bala, Thanks for the innovative information . could you please describe to us to take the backup on secondary replica.Thanks

• Anonymous
  February 11, 2012
  As you know, I am documenting my experience at the Tech Ed 2011 (India) held from March 23-25, 2011 at

• Anonymous
  March 10, 2012
  Hi, Is it possible to create alwayson for database in an instance in 3 node cluster with standalone as sceondary server

• Anonymous
  March 14, 2012
  JunDba, All machine participating in AlwaysOn MUST be part of same windows cluster.

• Anonymous
  April 16, 2012
  can uable to change group ID of availabilty group

• Anonymous
  July 04, 2012
  @Riya Why do you need to change the ID (which is a 'metadata') of an Availability Group?

• Anonymous
  August 30, 2012
  It will not work on SQL Failover Cluster, Only Windows Failover Cluster is required and both the node should have same drive later.

• Anonymous
  July 13, 2013
  Thanks for the post.

• Anonymous
  November 19, 2013
  Question on Force_failover is not showing up on management studio. I connected the endpoint from server1 to server2(primary to the secondary) and it synchronized properly. Therefore, Force_failover and listing of logical database management entity such as: data-tier applications, policy management and etc... does not show on management studio. Please advise! what did I missed on setting up sql configuration. PS: should it be connected to listener from both primary & secondary datbase? or just focus on primary connect to end point? Thank you very much.

• Anonymous
  May 02, 2014
  Hi Varun Good Morning Can you please help me or guide me how to set up always on for 2 nodes from the scratch.

• Anonymous
  August 25, 2016
  Hi ,Thanks dear.... excellent explanationThanksMalathi.S