Hi Availability!

A few weeks ago I decided to transition my Microsoft SQL Server 2008 Certifications over to the latest version, Server 2012 (I know glutton for punishment right?).

So I hit the books, and one of the things that jumped out throughout the studying was the real focus on business continuity.  What happens, when the worst happens?

In SQL Server 2008 (and previous to that), we had database clustering.  This meant that we could put together say two servers, and a SAN (Storage Area Network), and one of the servers would be active, whilst the other was providing a backup.

This was great because it meant that when someone unplugged the current active server to plug in the kettle and make a brew (I’ve seen that happen…), the other server would take over and the business could continue to run whilst you dunk your chocolate hobnobs… and you would never know how much of a Mr Bean moment you’ve just escaped.

As good as it sounds, that configuration can be quite costly as you need a SAN for each of the servers to connect, and allow them to fail over and bring the company’s main database back online.  And one of the servers is pretty much redundant, sitting there waiting for when it’s needed. 

However, SQL Server 2012 introduced a new piece of technology called AlwaysOn availability groups, which meant we could say “Hi” to mission critical high availability (Where would my blogs be without a pun or two! 🙂 ).

So what is it? SQL Server 2012 allows us to have a primary replica, and between one and four secondary replica sets of the database. So, for example, when a user saves a customer record in Dynamics CRM, the record is saved to a number of places.

For a mission critical system, this would be done synchronously meaning that the user’s record would be stored at each replica, before the user had confirmation of a successful save.

With everything running in this mode of operation, should an outage of the primary replica partner server happen, SQL Server could automatically fail over to one of the other secondary replicas, without the need for SAN. 

This gives an overall more affordable availability solution for SMEs and means that it’s far less likely for your users to be without their data, sitting twiddling their thumbs whilst steam gathers around the person trying to get the server back online.

One of the other benefits of AlwaysOn availability groups over traditional Failover clustering, as I found out during my studying, is that we have the option for secondary readable copies of the database, which can be used for copy-only backups and read-only operations. This means that we can start to leverage some of that “redundant” server hardware for additional none critical processing.

So what’s the future of all of this?

Due for release in early 2014 is the aptly named SQL Server 2014. One of the new features that really sprung out during my research for my transition exams, was that the 2014 version is extending the availability groups to include the Windows Azure infrastructure.

This means that as well as utilising a number of On Premise servers for high availability, we can also benefit from disaster recovery provided by having a secondary replica of our critical data hosted in the Cloud, via Windows Azure.

So after a gruelling week of studying, exams, chocolate hobnobs and brews, I finally passed my SQL Server 2012 certifications, Woohoo!

And the thought that I leave you with from this blog is “how available is your business?” and “how can TSG help you improve this?”