SQL Server AlwaysOn Availability Groups - Part 1 configuration

By:   |   Comments (17)   |   Related: 1 | 2 | 3 | > Availability Groups


SQL Server has produced some excellent High Availability options, but I was looking for an option that would allow me to access my secondary database without it being read-only or in restoring mode. I need the ability to see transactions occur and query the secondary database.


Enter SQL Server 2012 AlwaysON High Availability Groups. 

The new AlwaysOn feature combines the powers of clustering and mirroring into one High Availability option, but also allows you to interact with the secondary databases something that clustering and mirroring do not allow.  In addition, AlwaysOn Availability Groups allows you to configure failover for one database, a set of databases or the entire instance again something you could not do with database mirroring.  Another feature is that you can create multiple failover targets where in the past database mirroring only allowed one failover partner.

In this tip I'll show you a basic look at how to setup SQL Server 2012 AlwaysON Availability Groups.  Note that this tip is separated into 2 parts: Part 1 will consist of installing and configuring the prerequisites for AlwaysOn and Part 2 will consist of setting up the Availability Groups and showing how they work.

Let's get started...

Environment:  I have setup two separate VM's running in VM Workstation with Windows 2008 R2 Enterprise Edition and SQL Server 2008 R2 Enterprise named Denali and Denali2.

First thing we need to do is make sure both servers have .NET Framework 3.0 Features and Failover Clustering installed. To do this, go to Server Manager, Features, Add Feature. Check .NET Framework 3.0 and Failover Clustering and Install as shown below.

alwayson features

Once .NET and Failover Clustering are installed we can configure the cluster. Go to Control Panel, Administrative Tools, Failover Cluster Manager and click Validate a Configuration.

cluster validate configuration

On the "Validate A Configuration Wizard" page click Next, then enter the names of the SQL Servers you want to configure. In this example we are using Denali and Denali2.

cluster configuration wizard

Click Next to run through some validation tests. If you receive any errors click View Report to view the errors.

cluster validation report

As you can see from above I received errors on my validation, because I am only using one network card. This means I have a single point of failure. In a production environment you would want to fix this, but this is only a warning and does not stop us from configuring clustering. From the main screen click "Create a Cluster"

failover cluster management

After clicking "Create a Cluster" and clicking Next on the main page you should see the "Access Point for Administering the Cluster". Here is where you will type in the name of your cluster. This doesn't need to be your server name. Use a name to distinguish this cluster from other clusters. In this example, I'll use DenaliCluster.

cluster access point configuration

Click Next to install the cluster.

create new cluster

After the creation, you can see the install report as shown below.

cluster created

From the Failover Cluster Management screen you can see I have an error regarding my storage solution (no quorum drive) by the yellow exclamation point over my Cluster Name. No worries in a test environment, but you would want to address this in production.


After creating the cluster, we'll need to install SQL Server 2012 on both servers (in this example, I'm using Denali CTP3). On the installation screen we could click "New SQL Server failover cluster installation", but since we're only using Availability Groups we can just use the standard installation, "New SQL Server stand-alone installation..."

sql server installation center

Accept all defaults, choose SQL Server Feature Installation and select Database Engine Services and Management Tools - Complete.

setup sql server role
sql server features

Accept defaults, specify a domain user on the Server Configuration screen and click Next. On the Database Engine Configuration screen specify Mixed Mode Authentication and enter a password for the sa account.

sql server configuration setup
sql denali server setuo

Click Next, Next, Next, Install. In a few minutes you should have a working copy of SQL 2012. Once 2012 is installed on both servers we'll need to do a few things to configure it.

Enable TCP/IP: Click Start, Programs, Microsoft SQL Server 2012 CTP3, Configuration Tools, SQL Server Configuration Manager. Click SQL Server Network Configuration, Protocols for MSSQLSERVER, and right click TCP/IP and enable.

sql configuration manager

Enable AlwaysON: In SQL Server Configuration Manager, click SQL Server Services, right click SQL Server (MSSQLSERVER) and choose Properties. Click the AlwaysOn High Availability tab and Enable AlwaysOn Availability Groups.

alwayson high availability

Add Startup Parameter: In SQL Server Configuration Manager, click SQL Server Services, right click SQL Server (MSSQLSERVER) and choose Properties. Click the Startup Parameters tab, type -T9532 in the startup parameter box and click Add and Restart Services.

sql startup parameters

Setup Logins: Open SSMS and connect to one of your instances. Click Security, right click Logins and click New Login. Create logins for the accounts you used for your SQL Services. For example, I used SQLBrady/SQLUser for my service accounts so I'm going to make a login for this user and give this login sysadmin rights from the Server Roles page. Do this for both instances.

sql server logins creation

Create file share for backups and replicas: If you've ever setup log shipping you know you have to have a file share on a server and this is the same for this new feature. Create a file share on one of the servers and give read/write access to all your service accounts. Once clustering is setup, 2012 is installed and configured, we can create our first Availability Group for AlwaysOn.

Next Steps
  • Stay tuned for part 2 for creating Availability Groups

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Saturday, October 3, 2020 - 11:56:13 AM - Sandeep Sawant Back To Top (86591)
Very Nice Article ..I liked the step by step approach...

Wednesday, July 26, 2017 - 7:14:09 AM - Johann Engelbrecht Back To Top (63276)

Nice article.

I have a question, can I setup AlwasyOn highavailibilty groups between two versions of SQL. I want to setup between SQL 2012 and SQL 2016. This way I can seemlessly migrate to new SQl without any downtime.

Friday, September 23, 2016 - 12:13:17 PM - Zach Back To Top (43402)


Wish this was updated w/ SQL 2012 or 2014 or 2016. 

Sunday, November 16, 2014 - 7:53:40 PM - SAMSON Back To Top (35305)

Thanks  but when you start you wrote down Server manager where is the location to get server manager? 'If you've ever setup log shipping you know you have to have a file share on a server and this is the same for this new feature' but you didn't saw us how to implement it. Can you clarify to me step by step?


Tuesday, April 23, 2013 - 2:35:32 PM - Brady Back To Top (23527)

Sorry for the late responses regarding the trace flag. This tip was written when SQL 2012 was in Beta so it required trace flag 9532. Since the release of SQL 2012, this is no longer needed.


Monday, February 18, 2013 - 5:12:49 AM - Hans Back To Top (22223)

Good article but I'am also curious concerning the trace flag (T9532)
No further info to find about this particular trace flag.....

Wednesday, January 23, 2013 - 4:53:19 PM - Mahidhar Vattem Back To Top (21662)

So you have, combination of Cluster and Mirroring, in the event of cluster failover (assuming it is active/passive), what happens to mirroring?  Also, I didnt see your response to -T9532, do you have to enable as a startup parameter?



Monday, December 3, 2012 - 9:13:28 AM - Lorie Back To Top (20709)

What is the trace flag for?   –T9532

Wednesday, October 3, 2012 - 9:17:11 AM - Lorie Back To Top (19774)

What is the trace flag for?   –T9532

Sunday, September 9, 2012 - 6:16:19 AM - Vahid Back To Top (19441)

Very nice article. I have a question though. ihave 2 node and sync and automatic failover seting. if i want reset  primary server what happen? that switch on secondary server or no?

Sunday, March 11, 2012 - 1:34:36 AM - JunDBa Back To Top (16336)

Is it possible to create alwayson on a 3 node clusterd. alwayson replica will be on a independent standalone  server

Friday, January 6, 2012 - 12:14:00 PM - bass_player Back To Top (15537)

For AlwaysOn Availability Groups, you do not need shared storage. You only need to enable the Failover Clustering feature in Windows Server 2008.  Take a look at this webcast for more information http://www.mssqltips.com/webcast2.asp?id=105

Monday, November 21, 2011 - 3:52:37 PM - Sumeet Back To Top (15173)

Very nice article.  I have a question though.  Does this mean that one requires shared storage(SAN etc) to setup the clustering framework if one needs to use Always on features like failover-groups etc?  Thanks.

Wednesday, November 9, 2011 - 10:54:58 AM - Brady Back To Top (15057)

It looks like AlwaysON will be included with BI or Standard edition but limited to only 2 nodes. More details here: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx

Wednesday, November 9, 2011 - 10:29:56 AM - Fred Rosenberg Back To Top (15056)

Hi and thanks for a great article,

now...what could be accomplished if you don't have the Enterprise Edition? That costs big bucks, and some of the smaller shops don't have that. (e.g. ours, for now).



Wednesday, November 9, 2011 - 8:51:38 AM - Brady Back To Top (15054)

I believe it will be better than log shipping for the simple fact that it doesn't put your secondary database in restoring mode. You can actually add a table on your primary database then query it from your secondary database.

With this said, Log shipping is not going away. There will still be uses for it.

Wednesday, November 9, 2011 - 8:40:03 AM - StuRD Back To Top (15053)

I know this article does not cover it, but I figured I would ask.  Is AlwaysOn really going to be better than log shipping?  I have used log shipping extensively and we can customize it to do anything we want.

get free sql tips
agree to terms