Before SQL
Server 2012, one of the options available for you to use to build your High
Availability (HA) solution was to utilize Database Mirroring. The Database
Mirroring technology is very good at what it was created for. However, it has
some limitations when it comes to your HA solution. The limitations include:
- A Single Secondary database
- Mirrored database is accessible
via db snapshot only until failover occurs
- Lack of support for MSDTC
(distributed transactions)
- Related databases are not able
to be grouped together
SQL Server
2012 AAG’s resolve most of these issues giving you more flexibility over your
environment and more granular control over your environment to meet your ever
growing complex HA requirements.
With
implementing SQL Server 2012 AAG’s, which is still utilizing the Database
Mirroring technology to transfer your data via TCP/IP either synchronously or
asynchronously to one or more replicas but giving you the added advantage of
being able to access these replicas. It still does not support transactional
consistency for those databases participating in a availability group.
Availability
Groups
As its name
suggests, an Availability Group is a grouping of related databases. When you
were setting up Database Mirroring Before SQL Server 2012, you could set up
multiple mirrors, but you were only able to set up to mirror a single database
at a time. If you have multiple databases that are reliant on each other for the
application to work, there is no simple way of ensuring that all of the
databases failed over together. Availability Groups now allow you to group
appropriate databases together. You can setup, up to 10 AAG’s on a per instance
level. Across these 10 Availability Groups you can have up to 100 replica
databases participating.
The
benefits given by an Availability Group are that it:
- Supports
Alternative Availability Modes
- Supports
Multiple forms of AAG Failover
- Supports
Configuring Secondary Replicas in Read-Only Mode
- Supports
Configuring Secondary Replicas to perform Backups
- Supports
Automatic Page Repair
- Supports
Encryption and Compression
- Supports
Management via
- TSQL
- PowerShell
- GUI
Wizards
- Dashboard
- Management
Studio
- Fast
application Failover through the use of AlwaysOn Availability Group
Listeners (AAGLs)
Availability
Replicas
Availability
replicas provide you the ability to setup:
- A primary replica which allows
you to undertake read and write capabilities against those databases that
have been configured in the AAG
- Up to four secondary replicas
which allow you to have read-only capabilities against those databases
that have been configured in the AAG. Also allows you to setup the ability
to perform backups on these secondaries.
Availability
Modes
As
mentioned above, when configuring your SQL Server 2012 AlwaysOn Availability
Groups, there are some considerations that need to be taken into account when
determining what type of availability mode you can use.
If you are
wanting to use AAGs for a reporting process, you could have your secondary
replica located in the same physical data centre and implement
synchronous-commit mode to give you a read only near time group of databases to
report against without impacting the performance of the primary databases with
reporting overheads. You probably would not consider this type of availability
mode where there are large distances between data centres.
If you have
the requirement for a reporting process, that does not require the data to be
near real time, you could consider implementing your secondary replica in a
separate data centre that may be more than 30-40 Kilometers away. If this is
the case, you would look at implementing asynchronous-commits for your AAG. By implementing
an asynchronous-commit method, you would reduce the latency of the transactions
on the primary site but it would open you up to the possibility of data loss.
As you can
set up several secondary replicas, you are able to setup different availability
modes in your environment. Each AAG is configured separately; for example: you
may have two synchronous implementations and two asynchronous implementations.
For example
you would have your primary databases in AAG1 residing in DC1. You then set up
a secondary replica that is also located in DC1 in a synchronous-commit mode,
thereby allowing you to run your reporting requirements without the reporting
overhead impacting on your primary database. This also provides for your HA
requirements, by having a secondary environment that is transactionally
consistent with the ability to failover to in the event of an issue with your
primary databases. You could then setup secondary replicas in DC2, DC3 &
DC4 in asynchronous-commit mode. These asynchronous secondary replicas allow
you to meet your DR requirements by having multiple copies in multiple
geographical dispersed locations, with the ability to failover to in the event
of an issue on the primary site.
Failing
Over
As with
Database Mirroring and Windows Server Failover Clustering, AlwaysOn
Availability Groups provide the ability to failover between the primary
and secondary replicas that you have setup. There are three forms of failover
which can be undertaken with AAG’s:
- Automatic - Supported by
Synchronous-Commit Mode - No Data Loss
- Manual - Supported by
Synchronous-Commit Mode - No Data Loss
- Forced - Supported by
Asynchronous-Commit - Possible Data Loss
The
Availability Mode that is in use will depend on whether you are implementing
High Availability or Disaster Recovery. This affects the failover setup that
you are going to implement in your SQL Server 2012 AlwaysOn environment.
Availability
Group Listener
We need to
set up and allow for the applications to maintain connectivity to the SQL
Server Databases after a failover. This is where the AlwaysOn Availability
Group Listeners (AAGL’s) come into use.
An
Availability Group Listener is a Virtual Server Name that applications connect
to. From the applications point of view it does not matter where the
Availability Database is active and available for use. The AAGL consists of:
- Virtual Network Name (VNN)
- Listener Port
- One or more Virtual IP
Addresses (VIPs)
For your
application to connect, you can either set up a connection string for your AAGL
or connect directly to your SQL Server Instance. However, a direct connection
does not give the failover support which this technology has been built for.
When a
failover occurs for an AAG, the connection from the client is terminated. To
gain access again, the client needs to reconnect to the AAGL. To achieve this,
the application must be designed and built to poll for the AAGL. Depending on
the connection that you are utilising:
- Primary database
- Secondary read replica
You will
need to configure your 'ApplicationIntent' in your AAGL connection
string appropriately.
