SQL Server 2012 AlwaysOn

Introduction

One of the greatest new features introduced in SQL Server 2012 in terms of high availability is the AlwaysOn technology.
This is divided into two types:

1. AlwaysOn Failover Cluster Instance: Protection at single instance level, improvement of the traditional  SQL Server Cluster installation, mainly in the health monitoring  and Multi-Subnet implementation.
2. AlwaysOn Availability Group: Database-level security, evolution of the  earlier SQL Server versions DB mirroring.

Source: Microsoft Learning

First solution, in terms of architecture, just adds elimination of the need to create a VLAN for cluster nodes, while Availability groups are a true novelty, perhaps one of that particular for which it would be worth to migrate to SQL 2012.

AlwaysOn Availability Group


General


This technology takes advantage of the Windows Server Failover Cluster (WSFC) to provide a high availability solution for SQL Server databases. It 'an evolution of DB mirroring technology, introduced with SQL Server 2005 version, but offers these fundamental additional advantages:

1.  Nodes  number is no longer limited to 2
2.  Failover is no longer handled by the client, but it's a server side event done in a transparent way to the client
3. And' possible to perform read-only access to a passive node of the availability group
4. And' possible to perform the backup operations on a passive node of the availability group
   
   

Although this technology is based on the Failover Cluster is not necessary:

1. Have a storage shared among cluster nodes
2. Perform an SQL Server Cluster installation
   

Then each node will have a standalone SQL Server installation with data on local node storage.


Note:

In Windows Server 2012 Failover Cluster feature is available also on the standard version

There is no problem in creating virtualized nodes, if not the usual recommendations for SQL Server running on a virtual machine


Terminology

Source: Microsoft Learning

AlwaysOn Availability Group: a group of servers (cluster) that provides high availability to one or more databases

Primary Replica: SQL Server instance that hosts the read and write Database copy (only one among group)

Secondary Replica: node that hosts a Database copy on which you can enable read-only access

Listener: consists of a DNS name, TCP port, and one or more IP addresses through which clients connect to the availability group


Availability Modes: how data is replicated between the primary and a secondary member, can be:

1. Commit-Synchronous: the primary replica waits for confirmation that the changes have been made on the secondary member before closing the transaction. Reliable but dangerous in terms of  performances.
2. Commit-Asynchronous: the primary member sends changes to the secondary and close the transaction without waiting for any confirmation of the outcome. Not reliable but very performing.

Failover: process through which the role of the Primary Replica is passed to a Secondary Replica, called failover target. There are 3 types of failover:

1. Automatic failover (without data loss) available when both the primary  that the secondary replica are operating in synchronous mode (Commit-Synchronous). In this case the failover mode in the group is set to automatic and the passage of roles takes place without any administrative intervention.
2. Planned manual failover (without data loss): as in the previous case, the two members operate in synchronous mode, but, since the Group was not set to Automatic failover. requires an administrative intervention to send the failover command.
3. Forced manual failover (with possible data loss): the two replica members operate asynchronously and then the administrator can force the failover knowing that any transactions that have not yet been synchronized from the primary member will be lost.

Active Secondary Replica: to improve performance and manage more efficiently the availability group resources you can route  read-only and backup requests to a secondary replica; secondary replica(s) acting in this way is/are called 'active'




Installation

Note: the objective of this article is centered on the Availability Group and not on the Failover Cluster configuration, so I will not go into detail on this part.


1) Install the Failover Cluster feature on each Server

2) Create a Failover Cluster that includes all servers

in our example we have created a cluster called  MIA-CLUSTER including the servers:


MIA-CLUST1

MIA-CLUST2

MIA-CLUST3


Note that there is no shared storage in the cluster.

3) Install a Standalone instance of SQL Server 2012 on each server

Note:


Use a domain account for the SQL Service

You can install only the Database Engine feature, optionally also management tools


4) Trough the SQL Server Configuration Manager enabled AlwaysOn Availability Group on each server

Note: Restart the SQL Server service


5)  Create a network share for the backup files used to synchronize the replicas

6) Create Database(s) we want to protect on the Server that will become your Primary Replica

In our case the Server that will act as Primary Replica is MIA-CLUST1 and we'll put in High Availability the  Sales database


Check that the Database Recovery Model is Full

7) Now you can create your AlwaysOn Avalaibilty Group

 Launch the Wizard on the Primary Server

  

Specify your AG name (in our case MIA-SQL-AG)

 We select the Sales database

Add the Secondary Replicas: Add Replica ...

Configure the Listener

We set the synchronization folder

 Then let's go Next

 Control the outcome

Note: the warning I received about the WSFC is not significant



Final Results:


The Sales database is replicated on all three Servers

 

 In the cluster was created a service whose onwer is our Primary Replica

Configuration

Now let's do a configuration example.


We enter our Group Availability properties

We set MIA-CLUST2 in synchronous mode and we enable automatic failover
Let MIA-CLUST3 in asynchronous mode, and then the failover can only be manual
We make both Secondary Replicas readable 

Let's analyze deeper the available options.

Remember that each server can potentially act in two modes: Primary and Secondary, here we have options available for each server based on the role it plays (state is intended to be dynamic):


Connections in primary role: when acting as a primary replica server can accept all or only write connections

Readable Secondary: when acting as a secondary a server can: don't accept connections (passive), accept any connection  (but operation will fail if you try to change data) or accept only connections that explicitly define Intent = Readonly

Then we go in the Backup Preferences



We leave the default to Prefer Secondary, in this way the backup will always be performed on a secondary replica, if available.
We also change the backup priority by putting up MIA-CLUST3 Server.

Our settings results are also visible from the AG Dashboard

READ-ONLY ROUTING


After making our secondary replicas readable it's already possible to access them pointing directly to the server name.
Access example to MIA-CLUST2

Access example to MIA-CLUST3 (set to read-only intent, note the error in the first attempt)

But if we want to create a mechanism that automatically addresses read-only requests to Secondary Replicas we have to implement Read-Only Routing.

For details about the configuration you can refer to this article, but let's do it togheter:

• You must have configured a Listener and we did it in the AG configuration wizard:

            DNS: MIA-SQL-CLUSTER
            IP: 10.10.0.40
           TCP Port: 1433

• Identify each AG server member connection URLs, in our case:

           TCP://MIA-CLUST1.AdventureWorks.msft:1433
           TCP://MIA-CLUST2.AdventureWorks.msft:1433
           TCP://MIA-CLUST3.AdventureWorks.msft:1433
    As you can see URLs contain the Listener Configuration (TCP 1433) and the server FQDN


    However to obtain URLs you can use this script created by Matt Neerincx that provides an output like :

• Configure Read_only_routing using these TSQL commands on your Primary Replica:

__________________________ Start _________________________________________


-- Configure Read_Only_Routing

-- This script needs to be paramatrized with your deployment data!!!!

-- by Ruggiero Lauria


-- Enable Server as Readable when acting as Secondary Replica

-- This command is not strictly necessary because we have already configured it

-- trough AG propriety. But for completeness I preferred to include it in the script


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST1' WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));


-- Define the Server Read_only_routing URL


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST1' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://MIA-CLUST1.AdventureWorks.msft:1433'));


-- Enable Server as Readable when acting as Secondary Replica

-- This command is not strictly necessary because we have already configured it

-- trough AG propriety. But for completeness I preferred to include it in the script


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST2' WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));


-- Define Server Read_only_routing URL


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST2' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://MIA-CLUST2.AdventureWorks.msft:1433'));


-- Enable Server as Readable when acting as Secondary Replica

-- This command is not strictly necessary because we have already configured it

-- trough AG propriety. But for completeness I preferred to include it in the script


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST3' WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));


-- Define Server Read_only_routing URL


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST3' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://MIA-CLUST3.AdventureWorks.msft:1433'));


-- Define Read Routing List when Server is acting as Primary Replica


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST1' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST2','MIA-CLUST3')));


-- Define Read Routing List when Server is acting as Primary Replica


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST2' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST1','MIA-CLUST3')));


-- Define Read Routing List when Server is acting as Primary Replica


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST3' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST1','MIA-CLUST2')));


GO

_______________________________End _________________________________________



Note: Servers indicated in the routing lists will be used in sequence based on availability, there is no form of load balancing or round robin.



Connectivity Test


Here is the process used by the Read Only Routing:

1. Clients connect to our Availability Group Listener
2. Client request is redirected (always!) to the Primary Replica
3. Client has specified in the connection string ApplicationIntent=ReadOnly
4.  Sever checks if the target database is member of an Availability Group
5. If it is true step 4, server checks if it is set a read_only_routing_list on the Primary Replica
6. If it is true step 5 server checks, in order, that the servers listed in Routing_List are synchronizing and accepting connections (allow_connections = read_only or all)
7. Server reads from the read_only_routing_url the first secondary replica ready to accept connections and passes it's URL to the client
8. The client reads the URL and redirects itself to the secondary readable instance

To test connectivity we'll now use SQL Server Reporting Services, in this example I'll make a simple report (the example is trivial!) on the Sales.dbo.orders table , including the name of the responding server,  using the following query:


Select OrderDate, OrderTotal, @@ServerName as Server from Orders


First Test: we access with ApplicationIntent = ReadWrite

And we receive answer from the Primary Replica

And now let's change the connection type with ApplicationIntent=ReadOnly

 And resubmitting our report we obtain

We have been routed to the first server in the routing list defined for Primary Replica, remember:


ALTER AVAILABILITY GROUP [MIA-SQL-AG]

MODIFY REPLICA ON

N'MIA-CLUST1' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST2','MIA-CLUST3')));




 Useful Commands

Connection string example:
Server=tcp:MIA-SQL-CLUST,1433;Database=Sales;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True


Listener Restart: 


ALTER AVAILABILITY GROUP [MIA-SQL-AG] RESTART LISTENER 'MIA-SQL-CLUST';


Related System Views


SELECT * FROM sys.dm_tcp_listener_states

SELECT * from sys.availability_replicas


SELECT * from sys.availability_read_only_routing_lists



Ending

I hope that this step by step guide will help you to take full advantages of this wonderful opportunity offered by SQL Server 2012.
Any corrections and comments are welcome
  
That's it, have fun!