Microsoft SQL Server 2012 – AlwaysOn

Microsoft SQL Server 2012 – Always On

As technology advances, so does the need (and want) for instantaneous information. The goal is always “How can we get information as quickly as possible?” The same is the standard for disaster recovery. How quickly can we recover in the event of failure? The answer in many cases is seconds. One of the newest options for High Availability is Microsoft SQL Server’s AlwaysOn technology. The name is self-explanatory–AlwaysOn is designed to have data and information always available, even in the event of disaster. Disaster Recovery time is minimized with SQL’s AlwaysOn ability to sync multiple databases across multiple database servers.

I had the recent experience of using SQL’s AlwaysOn technology to set up a redundant application environment. The purpose of this blog post is to summarize my experience with the technology. By no means is this a start-to-finish coverage of the technology.

Overall, the perception of AlwaysOn amongst users has been very positive (simply Google “SQL Always On” and you’ll see other posts stating its benefits). I, too, was impressed with its simple setup and configuration and how well it handles failover scenarios (verified during our pre-launch testing). With all technology, there were some downsides, but I felt that the positives of AlwaysOn far out-weigh its drawbacks.

Availability Groups

  • Replication in AlwaysOn is no longer thought of as individual databases to individual servers. Complex systems can be replicated as a group, not as individual databases, minimizing setup and configuration time for replication. The one downside is that the replicas are limited to four sets, so one group can only be replicated to 4 different locations.
  • Synchronous and Asynchronous Setup
    • Synchronous Replication – Transactions are not completed until committed to all databases in the Synchronous Availability Group
      • The first thought I had when hearing this process was that it may be performance impacting since SQL will need to commit the transaction to multiple databases on multiple servers.
      • We tested this process to ensure synchronous replication to multiple databases would not cause performances degradation.
      • We used SQL’s query performance statistics to ensure that the performance of data read/writes were as efficient with the new environment as they were in the old environment.
      • As a side note, there were other factors that affected query performance times (for the better – new environment, new OS), so our test wasn’t a true comparison of SQL performance with and without AlwaysOn replication. For the purpose of our test, we needed to ensure that the AlwaysOn replication was not a detriment to application performance.
    • Asynchronous – Transactions are queued and committed to this Availability Group at a later time. Transaction completion is not dependent on committing to this database group. This is similar to Database Log Shipping in older versions of SQL Server.
      • In previous versions of SQL Server, Log Shipping would require the database to be inaccessible during restoration of the transaction logs. With AlwaysOn, the database is always online and accessible.
      • This allowed us to use the Asynchronous process for other purposes, in this case Read-Only reporting. We took long-running reporting queries and calculations and removed that processing need from the transactional database and server altogether, providing an indirect performance benefit from AlwaysOn.

Automatic Synching

  • One of the most frustrating aspects of SQL Log Shipping is when the process gets out of sync and the transactions are not being properly applied to the replicated database. This would generally result in manually copying and restoring a database to the replication server and restarting the transfer process
  • This headache is alleviated with the Asynchronous Replication of AlwaysOn. AlwaysOn polices itself for the data replication, automatically re-synching the database if a network connectivity loss or other event disrupts the replication process

Licensing

  • Unfortunately, AlwaysOn is only available with the Enterprise Edition of the SQL 2012 License, so you’ll need justification to spend the extra money for the Enterprise license. This may not be the best DR solution for smaller applications.
  • However, licensing is setup to use Active vs. Passive server. This means that you don’t need an additional license for your replication servers if it is only being used in a failover scenario, which allows you to limit the number of licenses you will need.

Again, this was just an overview of my experience with Microsoft SQL Server’s Always On functionality. In the event that you are in the process of setting up a new environment using Microsoft SQL Server, I highly suggest considering AlwaysOn to handle the High Availability/Disaster Recovery requirements. See Microsoft’s overview of AlwaysOn here: http://msdn.microsoft.com/en-us/library/ff877884.aspx

Advertisements

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s