SQL Server Mirroring vs SQL Server AlwaysOn Technology

Microsoft has made great improvements in the area of High Availability since Database Mirroring was introduced with the release of SQL Server 2005. Many of the limitations of this feature have been addressed with the release of AlwaysOn Availability Groups as part of SQL Server 2012, and subsequently enhanced in SQL Server 2014. Outlined below are the benefits and limitations of these two technologies:

Database Mirroring:

• Database Mirroring sessions allows for only one database at a time
• Mirroring is one-to-one meaning that you cannot have multi-site failovers
• Only one database can be failed over at a time. Multiple mirroring sessions are possible for additional databases, but the failover coordination process is handled manually
• The mirror target database is non-readable making it unsuitable for reporting or backup purposes.

SQL Server 2012 & 2014 provides AlwaysOn Availability Groups which seeks to address these limitations. SQL Server AlwaysOn Availability Groups key features include:

• Multi-Database failovers as well as introducing Availability Groups (AVG) to group associated databases for failover
• Up to four readable secondary replicas (SQL2012). This has been increased in SQL2014 to 8 readable replicas. These readable replicas allows for remote backups, DBCC statements and reporting
• Integrated with Windows Server Failover Clustering (WSFC)
• Synchronous and Asynchronous mirroring on non-shared storage
• Database mirroring over TCP/IP with built-in Compression & Encryption
• Only available with SQL Server Enterprise edition

This diagram shows an implementation of availability group that contains themaximum possible number of availability replicas on SQL Server 2012. In SQL Server 2014 you could add a further four secondary replicas. Initially the primary node AVG can be created and HA (High Availability) or DR (Disaster Recovery) nodes could be added when required.

Database Mirroring is still available in SQL2012 and SQL2014, but will be deprecated in future SQL Server releases.

Detailed SQL Server AlwaysOn information can be found on theMicrosoft site, or by contacting andersenIT.