Update from the Field – SQL PASS SUMMIT 2012
This week the Professional Association for SQL Server (PASS) has its annual conference in Seattle. It is always a good show to get updates on new developments for SQL Server and to get a feeling about the adoption rate of various features. At this show LSI is demonstrating a benchmark that is running on a SQL Server 2012 AlwaysOn Availability Group (a good article discussing this feature is available here). In the demo we are showing a database in an AlwaysOn Availability Group Cluster (in synchronous mode) running on local storage. This is a type of database clustering solution that uses log shipping between databases to create a High Availability (HA) solution. Both sides of the cluster having a complete copy of the database rather than a single copy on shared fault tolerant storage. LSI also has a second setup with a database running on SAN storage – the traditional storage for a clustered architecture. The AlwaysOn Availability Group Cluster is running at almost three times the transaction rate of the SAN system and at a fraction of the response time.
There is an important different between the two configurations. The local storage setup is not using disks alone, but rather Nytro MegaRAID controllers. These are high performance RAID controllers with flash integrated as a large cache to accelerate the disks. Mellanox was also kind enough to let us borrow a couple of Infiniband HCAs for a high performance connection between the clustered databases. With both high performance local storage and high performance networking a better performing HA SQL server solution can be created very cost effectively compared to alternative SAN based architectures. The full setup is illustrated below:
Prior to the AlwaysOn Availability Groups, SQL Server had another technology called Database Mirroring that used log shipping to create a HA database using local storage. It worked, but the applications had to be aware of the backup server and the management of the HA solution was very different than the management of a traditional SQL HA solution using Microsoft failover clustering. This different management was targeted at database administrators more than server or storage administrations. Database mirroring was a solution that worked technically, but because of its different management, it introduced serious risk of human error. The administrators tasked with availability and database administration had to use the same tool rather than separate ones and the application team had to make sure that the connections to the database were setup properly for everything to work.
An enterprise application that requires HA typically supports very important applications where the cost of downtime dwarfs all other considerations and management risks are taken very seriously. One of the huge advancements with Availability Groups is the management is done through the Microsoft cluster manager and applications no longer need to be aware of whether the database is clustered or not. This change has made a huge difference to the perception of the solution and it is great to talk with customers about how they are using Availability Groups & how they plan to. With applications getting better and better at providing robust solutions that can leverage local storage; the future is very bright for high performance local storage solutions.