How to configure Availability Groups in 10 steps

Last week I installed SQL Server 2012 RC0 which was released a few weeks ago.

I was playing around with the AlwaysOn high availability groups and noticed some small, but nice, improvements in the Availability Group Wizard.

It has become very easy now to configure an availability group in just 10 steps.

In this post you can find the steps I followed, I will also describe the enhancements in the wizard that were not in the CTP3 version.

Step 1: Startup the wizard

  1. In Object Explorer, connect to the server instance that hosts the primary availability replica, and click the server name.
  2. Expand the AlwaysOn High Availability node.
  3. Right-click the Availability Groups node, and select the New Availability Group

Now here we have already the first difference compared to CTP3. In the old version you had to go to “Management” à Availability Groups. With RC0, Microsoft created a separated node called “AlwaysOn High Availability”

Step2: Specify Availability Group Name

Just specify the name for the availability group.

Step 3: Select Databases

Select the databases that you want to be part of the availability group.
If you notice that you can”t select one of your database, check the status column to find out the reason, for example your recovery model is set to simple or no full backup of the databases is available.
More information on Checklist: Requirements Availability Databases


Step 4: Specify replica

In this step, Microsoft changed a lot compared to CTP3.
For each replica, you can now choose if it should use Automatic Failover of not and if it should use Synchronous Commit mode or not.
In CTP3,you only had the option Replica Mode where you could choose the options Automatic Failover,High Performance and High Safety.

Automatic Failover à Synchronous Commit mode will be selected to because you cannot combine asynchronous commit mode with Automatic Failover
High Performance à Don”t select Automatic Failover and don”t select Synchronous Commit
High Safety à Select Synchronous Commit. Don”t select Automatic Failover. Only manual failover will be possible. If you should select Automatic Failover, you”re back to the first mode.

The readable Secondary column has the same functionality but Microsoft just put other “names”
In CTP3 you had Disallow Connection, Allow all connections and allow only read-intent connections



Disallow Connection No
Allow all connections Yes
Allow only read-intent connections Read-intent only



Step 5: Specify Endpoints

In this section you have to specify the endpoint that you want to use for your mirroring session.
This is actually the same like configuring endpoints for database mirroring.
Nothing changed much here compared to CTP3

Step 6: Backup Preferences

The backup preferences tab is a new tab that has been added in RC0.
It allows you to specify where your backups have to occur.
You can choose between:

  • Prefer Secondary
  • Secondary Only
  • Primary
  • Any Replica

The only “issue” is that the DBA has to implement the logic himself. You can use the sys.availability_groups table to find the backup preference. Also check the sys.availability_replicas to check the replica backup priority. By querying these views and tables, you can determine which replica should run a given backup job by running a simple script.

I”m currently working on that script, which will be posted later on.

More information can be found on Backup on Secondary Replicas (AlwaysOn Availability Groups)

Step 7: Listener

Your application should use the listener to connect to the databases. The listener is owned by the primary server, so for the application it’2014-03-28 19:35:30’s transparent which server is the active on because it”s connecting to the listener.
The big improvement in this wizard of RC0 is that you can specify a static IP address. In CTP3, you only had the option to specify DHCP. If you wanted to use a static IP, you had to skip it in the wizard and configure the Listener afterwards.

Just specify the DNS name, port and network mode.

Step 8: Select Initial Data Synchronization

In CTP3 you only had the option Full (formally known as “Perform initial data synchronization”) and Skip initial data synchronization.

In this new version, Microsoft added an extra option Join only. If you want to use this option, the database must already be restored on the replica.
This option will then just join the selected database to the availability group.

Step 9: Validation

Now your complete setup will be verified. Everything should be successful before you can continue with the next step.

Step 10: Summary and Results

The wizard creates a summary for the user. The script button will generate a script with all the settings that you have selected.
Might be handy if you want to test different configuration and you don”t want to go through this wizard every time.
When you click finish, the availability group is created and finally you should have a successful result




Leave a Reply

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