How to: Configure SQL Server 2012 AlwaysOn – Part 4 of 7

How to: Configure SQL Server 2012 AlwaysOn

Setting up an Availability Group

The AlwaysOn Availability Group is a logical group of databases that you want to fail-over to your secondary site together. It is also a Windows Cluster Resource and should always be manipulated via the AlwaysOn wizards in SSMS or via T-SQL. Microsoft explicitly states that you should not treat the cluster resources the same as you would other resources and that they do not support altering these resources in any way with exception of via SSMS or T-SQL.

In each Availability Group (AG) you can have N number of databases and you may have N number of AGs per cluster. Microsoft has extensively tested configurations with up to 10 AGs with up to 100 databases per AG but states that larger numbers are supported. For larger AGs you will want to be cautious with your system resources. The following is a quoted warning from Microsoft about going beyond those tested measures.

Signs of overloaded systems can include, but are not limited to, worker thread exhaustion, slow response times for AlwaysOn system views and DMVs, and/or stalled dispatcher system dumps. Please make sure to thoroughly test your environment with a production-like workload to ensure it can handle peak workload capacity within your application SLAs. When considering SLAs be sure to consider load under failure conditions as well as expected response times.” (MSDN Citation)


There are three methods to creating a fully functional Availability Group (AG). You can use T-SQL, the AG Creation Wizard, or create each piece with SSMS. The below instructions outlines the SSMS method without using the wizard. This is done to demonstrate how each piece is configured individually in case only certain pieces are needed in different situations. The wizard would take you through all components at once and very closely matches the process of doing them individually with only minor differences.

T-SQL Steps are not being covered here but all of the same concepts apply. In addition, if you chose to use the Availability Group Wizard; at the end you are capable of clicking the Script button to export the entire configuration as T-SQL.

Group Creation

  1. Open SQL Server Management Studio.
  2. Drill down into the AlwaysOn High Availability folder under your instance in the object explorer.
  3. Right click Availability Groups in the object explorer and select New Availability Group.
  4. Configure all options on the General page. Each element will be discussed in detail below.


Availability Group Name

This is the name of your AG. This will be the name which shows up as a cluster resource in your fail-over cluster manager and in your SSMS object explorer.

Your AG name must be 15 characters or less. If you are using the GUI then it will constrain you to this limit. If you are using T-SQL you will be constrained to a 24 character limit. Even though the T-SQL scripts will allow 24 characters and your AG will be successfully created; if you ever create another cluster resource on that same cluster with identical first 15 characters then you will have conflicts. When the 24 character names are displayed they are actually being referenced as 15 character truncated names.

Availability Databases

At this time you can add databases to the AG but we are going to cover this step in the Adding Databases to the Availability Group section (below). Part of the reason for this is so that the steps for adding a database to an existing AG can be covered and the other part is because with the other wizard we will use, it is easier to see what databases are available and whether they meet the AlwaysOn pre-requisites.

You can leave this section blank if you want to follow along with this walk through step by step.

Availability Mode

There are two options for your availability mode; synchronous commit and asynchronous commit. When using SQL Server Failover Cluster Instances (FCI) like we are here you aren’t expecting to have any replicas in the local data center so asynchronous commit will be selected to prevent a large performance hit generated by large geographic distances.

If you are using this blog post as a point of reference and not seeking our Target Architecture here is a brief description of the two choices.

Synchronous Commit

This setting will commit transactions synchronously. What this means is that when a transaction is opened (whether implicit or explicit) on the primary replica; the same transaction will open on the secondary replica. Once the primary is finished; it will then wait for the secondary to send back confirmation messages indicating that the same operations were performed at the secondary and then both replicas will commit their transaction.

This adds over head and will always have some degree of performance degradation. It could be minimal for local servers but I do not recommend using this for servers which reside outside of your primary site. A large geographic distance increases the performance hit.

This setting is the only setting which supports automatic database level fail-over. What this means is that, with our target architecture, we won’t be using it because we already have automatic instance level fail-over with our SQL Server FCI.

Asynchronous Commit

This setting will transmit transactions asynchronously. This means that when a transaction begun on the primary replica; it will begin on the secondary replica as well. The difference between this and synchronous commit and that the primary replica will not wait for the secondary to finish. The primary replica will commit its transaction as soon as it is finished and there will be a send queue with virtual log files held as active until AlwaysOn finished transmitting all data to the secondary replica. The secondary replica will commit on it’s own, once the transaction is completed.

This feature does not support automatic database level fail-over and is often used for cross site manual fail-over.

Failover Mode

Your options are automatic or manual. Automatic is not supported in a SQL Server FCI and you want the instance level failover with shared storage to handle that process. Select manual so that all automated failovers are handled by the cluster and manual failovers can be triggered across data centers. In addition, automatic is not supported for the asynchronous commit setting due to potential data loss.

Connections in Primary Role

The options include allow all connections and allow read/write connections only. Here you will select allow read/write connections only. The reason for this is because, if you have data center 1 up and data center 2 also up, you don’t want to off load your read-intent only connections to the other data center. This will produce large performance problems. Allow all connections should only be used if there are local replicas available to fail-over to; thus making this replica capable of accepting read-intent only connections when it is a secondary.

Readable Secondary

For this setting you can select yes, no, or read-intent only. Here we will choose No. This is because we don’t want to allow anyone to send read-intent only connections to the replica secondary site. This is, again, because we don’t want unintentional performance issues due to hitting a remote site unnecessarily.

When a fail-over occurs; the replica will become primary and this setting will not apply until it becomes a secondary again.

Session Timeout

This is the setting which dictates when a replica is considered offline. This is mostly used for automated fail-over. Play with this setting only if you have a reason to expect that your sessions could be non-responsive for longer than the default 10 seconds.

5. Next we will select the Backup Preferences page.

6. Select Primary: All automated backups for this availability group must occur on the current primary replica. Similar to our Connections in Primary Role setting; we are choosing this only because we have a SQL Server FCI and a dual data-center architecture. We don’t want to off-load backups to a secondary site due to performance hindrances. If you are ever in a situation where you have local replica(s) then you can select Prefer Secondary and ensure that your Backup Priority is weighted properly in the Replica backup priorities group box.

7. Click OK.

Adding a Listener Across Multiple Sub-nets

  1. With SSMS object explorer open; drill down into AlwaysOn High Availability –> Availability Groups –> “your AG name”.
  2. Right click on Availability Group Listeners and select Add Listener.
  3. Set the Listener DNS Name (15 Character Limit)
  4. Set Port to anything you want noting that this is the port your application connection strings will have to use.
  5. Set Network Mode to Static IP.
  6. Click Add to add a new IP address to the listener (a minimum of one is required but in our architecture you would need two, one per subnet).
  7. Select the appropriate subnet from the drop down box.
  8. Enter your IPv4 static IP address and click OK.
  9. Repeat steps 6-8 for each subnet in the cluster.
  10. Click OK to create your new AG listener.

Adding Databases to the Availability Group

  1. With SSMS object explorer; drill down into AlwaysOn High Availability -> Availability Groups -> “Your AG Name”.
  2. Right click on Availability Databases and select Add Database.
  3. Click Next on the Introduction page.
  4. Ensure that next to the database you wish to add, the Status column indicates meets prerequisites. The most common message here for new databases is “full backup required”. Correct any issues in the status and then check the check box next to the database(s) you want to add.
  5. Click Next.
  6. Select the Full data synchronization method and specify a network share that both the primary instance and all replica instances can access using their SQL Service domain account. Do not put this share on the data or log drives for the instance in use.
  7. Click Next.
  8. Upon successful completion of the Validation click Next.
  9. On the Summary page; click Finish.
  10. View your results and click Close.

Adding a Replica to a Database

  1. With SSMS object explorer; drill down into AlwaysOn High Availability -> Availability Groups -> “Your AG Name”.
  2. Right click on Availability Replicas and select Add Replica.
  3. Click Next on the Introduction page.
  4. On the Replicas tab; click Add Replica.
  5. How-to-Configure-SQL-Server-2012-AlwaysOn-Part-4-of-7-New-Replica-Wizard-Replica-Page
  6. If this replica is in your primary data center change the Readable Secondary field to Yes else leave it to default which is No. Automatic Failover and Synchronous Commit should be unchecked for remote replicas but checked for local replicas.
  7. In the Backup Preferences tab; ensure backup priorities are set with the primary data center replicas weighed higher than remote data centers.
  8. How-to-Configure-SQL-Server-2012-AlwaysOn-Part-4-of-7-New-Replica-Wizard-Backup-Preferences-Page
  9. Click Next.
  10. Select Full on the Select Data Synchronization page since this is likely an initial creation of the replica. Change the default share listed to one on the backup storage (not the clustered or local storage for the serve) and to one where the service accounts for SQL Server for both primary and secondary instances can access the share.
  11. How-to-Configure-SQL-Server-2012-AlwaysOn-Part-4-of-7-New-Replica-Wizard-Data-Sync-Page
  12. Click Next.
  13. Upon completion of the Validation checks; click Next.
  14. Click Finish on the Summary page.
  15. Click Close once completed on the Results page.


At this point you have a functioning Windows cluster with two SQL Server FCIs using AlwaysOn for multiple sub-net fail-over. In the coming three parts we will get into the couple of components that you’ll see in the SSMS object explorer and Windows Fail-over Cluster Manager and then discuss fail-over procedures and how to use the AG listener to your full advantage.

Follow the links below to move on through this tutorial.




4 responses to “How to: Configure SQL Server 2012 AlwaysOn – Part 4 of 7”

  1. […] secondary replica to accept read-intent connections. Steps on how to conduct this were covered in part 4 of this blog series under Group […]

  2. […] Availability Group or when you add a new replica to an AG. We briefly illustrated these options in part 4 of this blog series and BOL addresses the full scope of options here so I’m going to just touch on them a little […]

Leave a Reply

%d bloggers like this: