Availability Groups (AG) on named nistances? (Part 1/2)

SQL Server 2012 introduced AlwaysOn Availability Groups, a feature intended to replace clustering, database mirroring, log shipping, replication, and other technologies by giving us one easy-to-manage feature for high availability, disaster recovery, and scale-out.

If you are new in this technology, I highly recommend to check Brent Ozars Blog. There you can find many useful posts describing AlwaysOn Availability Groups.

AG has numerous recommended prerequisites for Windows and SQL Server configuration. You can use them to successfully deploy AG in your environment.

Here is the short list:

Windows Server configuration:

• Create a single instance account for all SQL Servers
• You can use different accounts per node if NO Kerberos auth. will ever be used for listener.
• Use same drive letters for across all nodes (No C:\ drive please)
• Use static IP addresses for all servers & subnets.
• If SQL Failover Cluster instance (FCI) will be used:
• Reserve IP also for SQL instance & Cluster management
• Configure DTC if clustered

SQL Server Configuration:

• Prefer default instance over named
• Use single AD service account for all instances
• You will be able to use Kerberos authentication in future
• Also use same collation on all instances
• Use same drive letters on all nodes
• Apply latest Service Pack for SQL Server 2012
• Enable AlwaysOn HADR via Powershell or Configuration Manager.
• Configure SQL Instance as you do usually after new installation

So, today i would like to focus on one recommendation. Prefer default instance over named.

This could be an issue in some environments where you want to use more SQL instances on one physical server and you plan to configure AG for all instances. So why we need to use default instance?

Lets start with configuration. Is it possible to ever configure AG for named instance? Answer is yes, but it will not work in all scenarios. We will talk about this later in second part..

Cluster Installation step by step

Install required features

Create a cluster via failover cluster manager

You can go through validation tests. Storage test can be ignored if FCI (Failover Cluster Instance) not present in our topology.

Give a new cluster name for administration. Cluster name is not a name of the SQL Server so, you can pick the name whatever you want. Also pick network which will be used to communicate between nodes (AG partners).



After you finish, results could looks like this.

Last step in cluster installation will be reconfiguration of cluster votes. You need to tell cluster on which nodes you want to failover and which nodes will build up the quorum. Because you can setup to your topology a standby server located on disaster recovery site, on which you plan to failover just manually.

SQL Installation step by step

Because in this scenario we want to use just Availability Groups, install standalone
installation of SQL server. Cluster will be used just for sharing name of Availability group (AG Listener). 

First step will be enable AlwaysOn for all instances. Note that this option is available only if machine is already in cluster.

Then you can add correct permissions to all SQL servers and backup databases which you will want to use in Availability Groups.

Next we can setup AG.

Specify name for AG, this still not represent name which application frontend will call. Also pick databases which you want to failover together in one group.

Database prerequisites are checked here.

• Full Recovery mode
• Full backup must exist

Now setup your topology.

• Add you Instances
• Pick Roles
• Primary
• Secondary
• Choose Automatic failover for HA (Only Two)
• Select Replica Mode
• Synchronous (Up to 3)
• Asynchronous
• Decide whether Secondary should be readable

Ensure that correct endpoints are chosen.

Build your backup strategy for DR. Here you can choose on which servers will be backup done.
One prereuisite, is to use of a checking function with backup job. Function will look for the settings you have configured on image below. In SQL Server 2012, maintenance plan already using this function.




Create AG Listener to provide centralized Client management. This will be data source you use in application in order to connect to AG.

Select Initial Synchronization, or how databases should be prepared for sync. Database could be restored from backup, or if you manually restored it there is a option of just join between replicas. Last optin is just create topology without sync.

And you have successfully created Availability Groups on SQL Server 2012.

In the next post, I will talk more about testing AG groups and use with named instances. Also how clients should connect to AG Listener and read only routing. Stay turned