Using The AlwaysOn Feature of SQL Server 2012

This is the first in a four-part series on the new AlwaysOn feature in SQL Server 2012. In this article, AlwaysOn is introduced and contrasted with previous high-availability solutions in SQL Server. The second part of the series will commence with a detailed walkthrough on preparing the environment for AlwaysOn.

Prior to SQL Server 2012, SQL Server had several high availability and disaster recovery solutions for an enterprise’s mission critical databases such as failover
clustering, database mirroring, log shipping or combinations of these. Each solution typically has a major limitation, in the case of
failover clustering for example, its configuration is very tedious and
complex and you arguably have single shared storage or single point of failure. Database mirroring is relatively easy to configure in
comparison with failover clustering, but you can have only one database in a
single mirroring setup and you cannot read from the mirrored database. Log shipping
does not provide automatic failover (higher availability) though it be used
for disaster recovery with some expected data loss.

SQL Server 2012 (Code name SQL Server Denali) introduces a new feature called
AlwaysOn which combines the best of failover clustering and database mirroring and
overcomes major of the limitations imposed in failover clustering or a database
mirroring setup.

Please
note, in this article instance, server, replica and node refer to same thing
and will be used interchangeably.

Understanding The
AlwaysOn Feature

AlwaysOn is a new High Availability (HA) and Disaster Recovery (DR) solution in SQL
Server 2012 which improves high availability and protects data of your mission
critical applications. AlwaysOn lets you utilize your current hardware
and provides a flexible and simplified configuration, deployment and
management experience. AlwaysOn is common name for two high availability and
disaster recovery solutions:

AlwaysOn
Failover Cluster Instance (FCI)

This is an enhancement to the existing SQL
Server failover clustering (which is based on Windows Server Failover Cluster
(WSFC)) which provides higher availability of SQL Server instance after
failover. Some of the enhancements in AlwaysOn Failover Cluster Instance over the existing
SQL Server failover clustering are:

  • For
    improved site protection you can now set up multisite failover clustering
  • You
    can now define flexible failover policy to better control instance failover
  • You
    now have better and improved diagnostics capabilities out of the box


AlwaysOn
Availability Group (AG)

This is a completely new HA/DR feature and combines
best of failover clustering and database mirroring. It allows you to create a
group of databases which failover together as a unit from one replica/instance
of SQL Server to another replica/instance of SQL Server in the same
availability group. Each availability group that we create, allows you to
create one (and only) availability group listener which is nothing but a Virtual
Network Name (VNN) to be used by clients to connect to the availability group.

The AlwaysOn
availability group is based on Windows Server Failover Cluster (WSFC) and hence
you need to install the failover clustering feature on each server/replica and
create a failover cluster adding all these server/replicas before you can
start enabling/creating the availability group.

You can have numerous
availability groups on a single instance but a database from that instance can only
be part of one availability group thus you cannot also create a database
mirroring session for databases already participating in an availability group.

Availability Groups Compared To Traditional SQL Server Failover Clustering

In
a typical SQL Server failover cluster (at the instance level), you will have two
nodes/instances (Active-Passive or Active-Active) connected to shared storage
drives. Though SQL Server failover clustering has been good and is used in many
deployments for higher availability and disaster recovery, it has several
limitations and pain points, such as:

  • The process of setting up SQL Server failover clustering is
    tedious and complex – there are some 30-40 steps that you have to
    perform missing any of those steps can result in hours of additional work. This is why setting up SQL Server failover clustering is only recommended to be performed by highly
    experienced professionals.
  • Both
    the nodes are connected to a shared storage drive; though these drives might
    have their own failover mechanisms we still can have a
    single point of failure.
  • One
    of the nodes is idle all the time in case of Active-Passive cluster
    (recommended) and hence resources are underutilized. Though you have an
    Active-Active failover cluster this is not recommended as after failover
    one node will have double the load from both the cluster setup/applications.
  • The
    infrastructure and configuration of each node should be exactly same as other
    nodes and mimic each other.
  • You
    cannot distribute or load balance your read-write load from read only load on
    multiple nodes.

An
AlwaysOn availability group is superior to SQL Server failover clustering because the
configuration, deployment and management is relatively simple and all the
nodes/replicas will a copy of the databases and hence there is no shared
storage or a single point of failure. You can have readable secondary and hence
you can route your read-only load to a secondary replica and the read-write load to
primary replica and hence have better
utilization of your hardware resources.

How Availability Group
differs from database mirroring

Database
mirroring (at database level) can be set up in either synchronous mode or
asynchronous mode but not both in a single mirroring setup.

  1. Synchronous Commit mode (high-safety) : The transaction logs are hardened at
    both the principal server as well as at the mirror server before commit
    acknowledgement is returned to the client; it may introduce some latency but
    ensures no data loss after failover. In this mode you can also set automatic
    failover and for that you need another instance which will work as a witness and
    peforms the job of role switching.
  2. Asynchronous Commit mode (high performance) : The principal server hardens the
    transaction log at the principal server and returns the commit acknowledgement to
    client without waiting for transaction log hardening acknowledgement to be received
    from the mirror server. Transaction log hardening at the mirror server happens in a
    asynchronous manner.

These
all sound good solutions but like SQL Server failover clustering, it has also several limitations:

  1. You
    can have only one database in a single mirroring session/setup, though you
    can define multiple mirroring sessions/setups (one for each database) but it is not possible to have a group of databases failover together.
  2. Databases
    on mirror server are always in recovery mode and hence you cannot read from a
    mirrored database (though you can create a database snapshot and read from it
    but but would only reflect data till the particular point in time when it was
    created).
  3. You
    cannot load balance your read-write requests on one server and read-only on
    another server.
  4. You
    can have only one mirror server; you cannot have one for higher availability (synchronous
    commit mode) and one for disaster recovery (asynchronous commit mode) in one single
    mirroring session, although you can combine it with log shipping for disaster
    recovery.

An AlwaysOn
availability group is recommended over database mirroring as
this overcomes several limitations imposed in database mirroring, for example
with an AlwaysOn availability group:

  1. You
    can have multiple mirrored instance/nodes/replicas (up to four secondaries
    apart from one primary replica) with a combination of synchronous commit mode
    and asynchronous commit mode both at the same time. The replica set up in synchronous
    commit mode can be used for higher availability (or for automatic failover) and
    the replica set up in asynchronous commit mode can be used for disaster
    recovery.
  2. You
    can combine two or more database together and failover them as a unit, you
    don’t need to do it for each database separately as you were doing in case of
    database mirroring.
  3. You
    can offload the read-only load from the primary replica to the secondary by configuring
    the secondary as readable. In this way you can have better utilization of
    secondary replica’s hardware resources.
  4. You
    can also offload backup operations from the primary replica to the
    secondary replica and hence have less workload/IO on the primary replica and better
    utilization of the secondary replica’s hardware.

Preparing the Environments for an AlwaysOn Availability Group

An AlwaysOn availability group is based on a Windows Server Failover
Cluster (Windows Server 2008 and later versions) and hence before you can start
creating an AlwaysOn Availability Group you need to first setup/install failover
clustering on each node/replica and then create a failover cluster with
all the nodes/replicas joined to it. These are steps that you need to perform:

  1. Install
    SQL Server 2012 on each node/replica/server
  2. Install
    the failover clustering feature on each node/replica/server
  3. Create
    a failover cluster and let all nodes/replicas/servers join to the cluster

    Each
    AlwaysOn availability group can have one primary replica/node/server and up to
    four secondary replicas. Out of these four, two of them can be set to have
    synchronous commit mode and one of it can have setting for automatic failover.




Array

8 Responses to “Using The AlwaysOn Feature of SQL Server 2012”

  1. Let me get this straight…
    All the servers involved in the AlwaysOn have to be part of one big cluster? Why not use transactional replication which has been around for a while, and is much less expensive then this new idea?
    Am I missing something?

  2. Yes you are right, transaction replication is still available and will be available in subsequent releases. But actual intent of AlwaysOn is not same as of transaction replication.

    AlwaysOn is used for enterprise’s mission critical application for Higher Availability (Automatic Failover) or Disaster Recovery or both in single setup.

    Earlier methods have some limitations as discussed above and in the article below:

    http://support.microsoft.com/kb/822400

  3. Hi,
    limitation 1 is not true (it is not possible to have a group of databases failover together)
    We use a Witness server and in case of a failure of the Principal server automatic failover to the Mirror server will take place. So all the databases will automatic failover at the same time.

    • Yes databases do failover , but not as group. You will have to add that logic to the wintness server or a server that keeps polling at the primary server.

      ie. Assume there are 5 databases. 2 of them are for application A and 3 of them are accessed by Application B. If One database from application A fails to the mirror, part or the hole of Application A would respond as the load-balance’s will need two connections to handle the application. Where as Always on provides that option by providing group fail-overs).

  4. Not to mention… replication brings other problems. In my experience using replication with large, high transaction databases, it’s finicky and requires constant monitoring. I’m hoping AlwaysOn is a more robust and reliable solution.

  5. When using the alwayson group listener the listener can be accessed using its IP. The listener covers the high availability features but does not do an loadbalancing. Correct?

    With the accessing your alwayson db with knowing doing readonly requests (e.g. backup, reporting) you have to add a static ReadIntent=readonly parameter to the connection string to end up on the first (prio 1) secondary node marked for reading.

    The second node will not be used throught the listener since the first secondary node will die.
    OR
    You address them seperately which is no high availbility any more.

    What a feature always on is!?

  6. In my view, We cannot compare AlwayszOn availability with Transactional Replication.

    1. Transactional Replication can be configured for the tables only if they have primary key colums on it.

    2. Transactional Replication does not have automatic failover feature.

    3. Replication jobs such as log reader agent job, distribution agent jobs needs to be monitored to ensure both Publisher and subscriber are in sync.

  7. I configured ALWAYS ON in my project, Every month I’m getting huge no. of log space used. Can you suggest How can I clear the transactional log. I tried ShrikFile, Backup log etc… but no luck. Please suggest how can I shrink or truncate my old log file on ALWAYON DBS?

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |