SQL 2012 – Always On feature installation and configuration

With SQL 2012, Microsoft has introduced a new high availability functionality called Always On.

This feature allows you to manage high availability for specific set of database called Availability Database.

For those who are familiar with Exchange 2010, this can be compared with DAG feature.

Prerequisites installation

To use Always On SQL feature you must deploy an Enterprise Edition of Windows Server as this is the one which includes cluster technology. Indeed, you still need cluster configuration to use Always On feature.

SQL Server 2012 Installation

First you have to install SQL 2012 to allow cluster services to use and configure SQL cluster services.

As installation option, you must select New SQL stand-alone option (not the clustered one)

image

Once setup program has passed all first steps, choose at the Server Role installation step SQL Features Installation to select the features to be installed

image

I’ll not detailed all next steps are this is a ‘classical’ SQL installation

Select the features you want to install (at least Database Engine Services must be installed of course) and configure your SQL instance (name it in case of not using the default one, define directory used…)

Cluster Creation

Then you can create the cluster.

From the Server Manager, go to Features section to add Failover Clustering feature

image

Then, before creating the cluster, I recommend to run the validation tool to ensure all settings are correct.

Open the Failover Cluster Manager console and click on Validate a configuration

image

Then follow the wizard, the only thing you have to do is to enter the server names which will be part of the cluster

image

Then choose to run all test and let the wizard work. Results will be shown as web page.

image image
image image

 

Once everything is correct, you can create the cluster itself by clicking on Create a Cluster.

image

Follow the wizard by adding all servers which will be part of the cluster

image

Enter the cluster name (NetBIOS name and this will automatically create a DNS record) as well as the IP address to associated with the cluster

image

Quorum Disk

Then create the quorum disk

From the cluster management console, right click on your cluster just created and choose More Actions\Configure Cluster Quorum Settings

image

Select the quorum settings you want to apply; in my case I choose Node and File Share Majority

image  image

More details about quorum settings, see http://technet.microsoft.com/en-us/library/cc731739.aspx, http://msdn.microsoft.com/en-us/library/hh270280(SQL.110).aspx and http://msdn.microsoft.com/en-us/library/ff929171(SQL.110).aspx

Configure SQL Server to support Always On feature

Launch the SQL Server Configuration Manager console and select the SQL instance from the service node to display his properties

image

Go to the AlwaysOn High Availability tab and enable it

image

Restart the SQL instance

Create Availability Group

As all prerequisites (SQL and cluster installation and configuration) are now done, we can create availability group.

From the SQL Management Studio console, connect to your SQL cluster. If the AlwyaysOn feature has been correctly enabled, the node AlwaysOn High Availability should be available

image

Before adding a database to a group, you must set his recovery model to Full and then perform a Full backup

Right click on it and launch the New Availability Group Wizard if you already have SQL databases defined to be part of it and follow it

Or choose New Availability Group to create an empty availability group

image

You will be able to associate database later after creating this empty group by right clicking on the group and choose Add Database

image

To add a replica, do the same but choose Add Replica

Leave a Comment

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


The reCAPTCHA verification period has expired. Please reload the page.