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