SQL Server 2012 AlwaysOn vs. Database Mirroring

Probably the most anticipated new feature in the SQL Server 2012 (formerly code-named Denali) release is the new AlwaysOn Availability Groups high-availability feature. Earlier versions of SQL Server provided Windows Failover Clustering (WSFC), designed for server-level protection, and database mirroring, designed for database-level protection. AlwaysOn is essentially the next evolution of database mirroring. Let’s see how it stacks up against the tried-and-true SQL Server database mirroring high-availability feature.

Clustering Required vs. Witness Required

First, for the bad news: AlwaysOn requires a WSFC failover cluster, which means you have to run SQL Server Denali on Windows Server 2008 Enterprise Edition or later, adding additional complexity.

However, WSFC is easier to set up with Windows Server 2008. Database mirroring doesn’t require WSFC, but if you want to use it in High Safety mode for automatic failover, then you need to use a third SQL Server system as a witness to which database is available and which has failed.

Up to 4 Replicas vs. 1 Mirror

One of the main advantages that AlwaysOn has over database mirroring is that it permits up to four replicas for protected databases. Predictably, each replica is located on a separate SQL Server instance. With database mirroring, although it’s possible to mirror different databases to different mirror servers, each mirrored database is limited to a single mirror partner system.

Multiple Database Failover vs. Single Database Failover

Another limitation of database mirroring is that it fails over just one database at a time, but in many cases you might want to fail related databases simultaneously as a group. In addition, server-level objects such as logins aren’t part of the database. With mirroring you must manually create those objects on the mirror server. AlwaysOn can combine multiple databases as a unit that can be failed over together. This is called the Availability Group.

Mixed Synchronous and Asynchronous Connections vs. Synchronous or Asynchronous Connections

With database mirroring, you could do asynchronous (High Performance) or synchronous (High Safety) but not both. Essentially you had to choose between using database mirroring for disaster recovery (asynchronous) or for high availability(synchronous). AlwaysOn, however, allows up to two synchronous replicas and two asynchronous replicas to be simultaneously active.

Read-only Replica Databases vs. In-Recovery Mirrored Databases

Finally, with database mirroring, the mirror database is always in a state of recovery, which essentially means it can’t be used while mirroring is active. You can create a snapshot of the mirror database to use for reporting, but the snapshot is a point-in-time copy. With AlwaysOn, the replica databases provide read-only access to use for reporting and backup purposes, offloading some of the workload from the primary server.

Learn more: Be Invincible with AlwaysOn Availability Groups