With the release of SQL Server 2012, Launch date of March 7 2012, release date April 1 2012, One of the more well known features being included in the release is SQL Server AlwaysOn. For those of you who have had a look at SQL Server Code Named ‘Denali’ or as it is now known ‘SQL Server 2012’ you will be aware of this feature. If not have a read of my previous blog post – SQL Server 2012 AlwaysOn .
The driving force behind these new features in SQL Server 2012 was to provide a more granular approach to providing a robust Highly Available solution. To achieve this you have to have built your SQL Server 2012 AlwaysOn Cluster. The next step is to create your SQL Server 2012 AlwaysOn Availability Groups (AG).
Prior to SQL Server 2012, one of the options available for you to use to build your High Availability (HA) solution was to utilise 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 AG’s resolve most 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 AG’s, which is still utilising the Database Mirroring technology to transfer your data via TCP/IP either synchronously or asynchronously to one or more replica’s but giving you the added advantage of being able to access these replica’s. It still does not support transactional consistency for those databases participating in an availability group.
Availability Groups
Like its name indicates an Availability Group is a grouping of related databases. Prior to SQL Server 2012, when you were setting up Database Mirroring, you could setup multiple mirrors, but you were only able to setup 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 allow you to group appropriate databases together. You can setup, up to 10 AG’s on a per instance level. Across these 10 Availability Groups you can have up to 100 replica databases participating.
The benefits available by using SQL Server 2012 AlwaysOn Availability Groups:
-
Supports Encryption and Compression
-
Supports Management via
- TSQL
- Powershell
- GUI Wizards
- Dashboard
- Management Studio
-
Fast application Failover through the use of Availability Group Listeners (AGL’s)
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 AG
-
Up to four secondary replica’s which allow you to have read-only capabilities against those databases that have been configured in the AG. 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 AG’s 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 for 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 look at implementing your secondary replica in a separate data centre that may be more than 30-40 km’s away. In this scenario you would look at implementing asynchronous-commits for your AG. Implementing an asynchronous-commit method does reduce the latency of the transactions on the primary site but does open you up to the possibility of data loss.
As you can setup multiple secondary replicas, you are able to setup different availability modes in your environment. Each AG is configured separately. For example: you may have two synchronous implementations and two asynchronous implementations.
In this example you would have your primary databases in AG1 residing in DC1. You then setup a secondary replica also located in DC1 in a synchronous-commit mode, giving you the ability to run your reporting requirements without the reporting overhead impact 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. Your setup secondary replica’s 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
Just like in Database Mirroring and Windows Server Failover Clustering, AlwaysOn Availability Groups provide the ability to failover between the primary and secondary replica’s that you have setup. There are three forms of failover which can be undertaken with AG’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
-
Depending on the Availability Mode that is in use, will depend on whether you are implementing High Availability or Disaster Recovery. This impacts the failover setup that you are going to implement in your SQL Server 2012 AlwaysOn environment.
Availability Group Listener
In order to take advantage of the SQL Server 2012 AlwaysOn Highly Available and/or Disaster Recovery environments that we have stepped through above and in conjunction with my previous post – ‘SQL Server 2012 AlwaysOn‘, we need to setup and allow for the applications to maintain connectivity to the SQL Server Databases after a failover. This is where the AlwaysOn Availability Group Listeners (AGL’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 AGL consists of:
-
Virtual Network Name (VNN)
-
Listener Port
-
1 or more Virtual IP Addresses (VIPs)
For your application to connect you, setup a connection string for your AGL. This is not a mandatory requirement for connections and your application’s can connect directly to your SQL Server Instance (this does not give the failover support which this technology has been built for).
When a failover occurs for an AG, the connection from the client is terminated. To gain access again, the client needs to reconnect to the AGL. To achieve this, the application must be designed and built to poll for the AGL. Depending on the connection that you are utilising:
-
Primary database
-
Secondary read replica
You will need to configure your ‘ApplicationIntent‘ in your AGL connection string appropriately.
Michael Otey has a nice post on Availability Groups if you would like a further read.
Thank you to Peter Ward & Greg Low for the technical review
Enjoy configuring your new SQL Server 2012 environment.
Leave a Reply