It's already a long time since I have blogged how

to configure your Windows- and SQL Servers for SQL Server 2012 AlwaysOn. In the last

3 months I was almost every week on the road (or better in the air) visiting a lot

of different clients across Europe, and helped them with their SQL Server deployments

to improve performance and scalability.

Now I'm finally at home for at least 8 weeks (till the beginning of September), which

means that I now also have some time for blogging, and other things. In today's blog

posting you will see now how to deploy your first AlwaysOn Availability Group (AG),

and how to monitor the AG through the integrated Dashboard. As I have already described

in the previous blog posting about AlwaysOn, I'm running my AlwaysOn Lab on 5 different

Hyper-V VMs with the following network configuration:

• Node1
  • DNS Name: ag-node1.sqlpassion.com
  • IP Address: 192.168.1.211
  • SQL Service Account: ag_node1_sqlsvc@sqlpassion.com
• Node 2
  • DNS Name: ag-node2.sqlpassion.com
  • IP Address: 192.168.1.212
  • SQL Service Account: ag_node2_sqlsvc@sqlpassion.com
• Node 3
  • DNS Name: ag-node3.sqlpassion.com
  • IP Address: 192.168.1.213
  • SQL Service Account: ag_node3_sqlsvc@sqlpassion.com
• Node 4
  • DNS Name: ag-node4.sqlpassion.com
  • IP Address: 192.168.1.214
  • SQL Service Account: ag_node4_sqlsvc@sqlpassion.com
• Node 5
  • DNS Name: ag-node5.sqlpassion.com
  • IP Address: 192.168.1.215
  • SQL Service Account: ag_node5_sqlsvc@sqlpassion.com

As soon as you have enabled for your SQL Server Instances AlwaysOn through the SQL

Server Configuration Manager (which needs a restart of the SQL Server instance), you

are ready to deploy your first AG. For that reason SQL Server Management Studio 2012

provides you the new node AlwaysOn High Availability:

Before you can create your AG, you also need at least 1 database in the FULL recovery

model, which will be part of that AG. In my case I'm creating 2 databases with 1 table

in each database.

USE master

GO

-- Create

a new database

CREATE DATABASE TestDatabase1

GO

-- Create

another database

CREATE DATABASE TestDatabase2

GO

USE TestDatabase1

GO

CREATE TABLE Foo

(

Bar INT NOT NULL

)

GO

USE TestDatabase2

GO

CREATE TABLE Foo

(

Bar INT NOT NULL

)

GO

Another prerequisite is that you have to do at least 1 full backup of each database

that will be part of your AG:

USE master

GO

-- Make

a Full Backup of both databases

BACKUP DATABASE TestDatabase1 TO DISK = 'c:\temp\TestDatabase1.bak'

BACKUP DATABASE TestDatabase2 TO DISK = 'c:\temp\TestDatabase2.bak'

GO

In the next step you can start the Availability Group Wizard from

SQL Server Management Studio that asks you in the first step for the name of your

new AG:

In the next step of the wizard you can choose which databases will be part of your

new AG. The wizard also tells you, if each of the databases have met their required

prerequisites.

In the next step you can specify how many replicas you want to have for your AG, if

you want to have an automatic failover between them (up to 2), if you want to have

synchronous or asynchronous data movement between the replicas, and if you want to

have read-only access for a secondary replica. So there are a lot of things that must

be configured in this wizard step.

In my scenario I'm initially spanning the AG between the first 2 nodes. Both replicas

support automatic failover, and in that case the data movement between them must be

also set to Synchronous Commit. If each replica is in the Secondary

role, I'm also allowing read only access.

Bear in mind that you have to configure here each replica from both sides

of their role: from the Primary role and also from the Secondary Role!

In subsequent blog postings you will see how you can add additional replicas to your

AG, and how you can configure Backup Preferences and an Availability

Group Listener for it. In the next step you have to configure how you want

to join your Secondary Replicas to your AG. There are 3 options available:

• Full
• Join Only
• Skip Initial Data Synchronization

Full means that the wizard will take a Full Database Backup, and

a Transaction Log Backup, and will restore both backups with NO RECOVERY on

the Secondary Replicas. This is the preferred option for very small databases, but

doesn't really work with larger databases. Of course, it will take some time to do

the full backup, copy the backup through the network, and finally restore it on the

Secondary Replicas. In that case you can already prepare the databases on the Secondary

Replicas and use the option Join Only. Join Only assumes

that the database on the other replicas is restored with the NO RECOVERY option.

You can prepare your database for example through Log Shipping or Database Mirroring

on the other replicas, and then join it finally to your AG.

In the final step the wizard validates all your configured settings, and you can create

your first AlwaysOn Availability Group. On the final screen you also have the possibility

to script out the whole setup of your AG, so that you can deploy it fully automatically.

In the next blog posting I will show you how to configure an AG through T-SQL.

When everything went fine, SQL Server Management Studio will show your created AG.

You can also right click your AG, and display the Dashboard. The Dashboard shows you

in one step the overall health of your AG, and if every Replica works as expected.

It's almost the same as the Database Mirroring Monitor that you know from previous

versions of SQL Server.

You can now work very easily with your created AG. Just insert some records into the

table Foo on your Primary Replica. The generated Transaction Log

records will be transferred to your Secondary Replica, and will be finally redone

from the Redo Queue. It's almost the same concept as with Database Mirroring.

Because you have configured read only access during the configuration of the AG, you

can now do a simple SELECT on the table Foo on the

Secondary Replica to read the records. That was not possible with Database Mirroring,

because here you needed a separate Database Snapshot to get a point-in-time view of

your mirrored database. Read-Only access is a huge improvement in AlwaysOn over Database

Mirroring!

Over the next blog postings we will now enhance our AG, and I will show several new

use-cases that were earlier not possible with traditional Database Mirroring.

Stay tuned!

-Klaus