SQL Server 2012 Always On Technology and Comparison


SQL Server 2012 Always On Technology
SQL Server 2012 now has Always On technology. This is where the database can keep a mirror copy/copies of the database that are constantly being refreshed.  In the event of a failure of the Primary, it will seamlessly fail over to another copy.  What really different is that the unlike previous versions of SQL Server is you can use the copies to do real work. For example you could backup your production database off a copy. You could use one of the copies for a reporting server. This is huge.

SQL Server 2012 Always On Versus SQL Server Mirroring
This has significant advantages over traditional SQL Server Mirroring. In traditional mirroring
  • You cannot manage groups of databases, so that when a single database failed they would all failover.
  • There is only one Mirror of the database allowed
  • The Mirror is not useable unless you are prepared to work with Snapshots.

SQL Server 2012 Always On Versus SQL Server Log Shipping.
Log Shipping can be very complex to set up. The biggest downfall is you cannot use the receiving database since it is in a recovery state as the transaction logs are being applied.

SQL Server Always On Versus SQL Server Clustering.
With SQL Server clustering, you are dealing with a shared storage system that adds cost and complexity to the equation. With a 2 node SQL Server Active/Active Cluster unlike an Oracle RAC database, only one node can access a database at a particular time.
So, when you compare SQL Server 2012 Always On technology to other current High Availability SQL Server options you can see why I think it rocks. This is a huge advancement for SQL Server.

SQL Server 2012 - Other Advancements.
Databases can now be grouped together. When a failure happens, the entire group of database is treated as one. When one fails over they all fail over. This is very powerful when you have an application that uses more than one database in an instance.
File Streams took a huge step forward. The database and the O/S can now communicate with each other, making it much easier and more transparent to keep both sides in sync. Additionally, the 2 Gig file limit is no longer a limitation.
You now have the ability to connect directly to a database and be given permission that only work within that database without going through the over all SQL Server.  If for any reason you need to a database, the permission move with it from one server to another. The Server accounts are no longer needed or required. This takes security to a whole new level.
There is a new Column Store Index. This is really huge from a data warehouse perspective. By using the column store index, you eliminate the need to do Page I/O Latching. In other words avoid the need to go out to the disk. You are only doing I/O latching in memory. This provides a huge performance enhancement 10X plus!
SQL Server 2012 now support server level security roles. No longer do we need to create the same role in multiple databases.

This release of SQL Server is the most compelling release to come out in 7 years. This is a real winner. The Always On capability and Column Store Index alone represent significant improvement over previous versions.