In the I.T. industry, the phrase "high availability" (HA) is another name for "always up". In other words, your servers and applications are never down.
Microsoft high availability solutions hide the effects of hardware and/or software failures and maintain the availability of applications so that the perceived downtime for users is minimized, sometimes to the point of being imperceptible.
“High Availability” is not an absolute term. “Higher Availability” is more descriptive. Depending upon which and how each Microsoft HA option is implemented, there are degrees of recoverability and availability.
Windows Server and SQL Server provide five high-level options for creating a high availability application environment:
In general, clustered database servers offer the fastest and most reliably way to recover from a server failure. It has been my experience that in the event of a server failure, a Windows Server Cluster and SQL Server will usually return to full production status in under one minute, and most often in under 30 seconds.
The implementation is complicated and requires an elaborate and correct setup to be made reliable. It is also expensive but it is a proven technology that works.
Database mirroring comes in two implementations, synchronous and asynchronous. Both have virtues but each has shortcomings.
Database mirroring is primarily a software solution to increase database availability by supporting almost instantaneous failover in the event of a server failure. Each database mirroring configuration involves a principal server that contains the principal databases, and a mirror server that contains the mirrored databases. The mirror server continuously brings the mirrored databases up to date with the principal databases.
Database mirroring runs with either synchronous operation in high-safety mode, or asynchronous operation in high-performance mode. In high-performance mode, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance but with a risk of data loss on failover. In high-safety mode, a committed transaction is committed on both partners, but at the risk of increased transaction latency.
Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. It will not always guarantee against data loss, depending upon how it is implemented and executed.
Like database mirroring, log shipping operates at the database level. Log shipping can be used to maintain one or more warm standby databases for a corresponding production database that is referred to as the primary database. Standby databases are also referred to as secondary databases. Database log shipping latency can be configured to meet your business requirements.
A log shipping configuration includes a single primary server that contains the primary database, one or more secondary servers that each have a secondary database, and a monitor server. Each secondary server updates its secondary database at set intervals from log backups taken of the primary database. Log shipping involves a user-modifiable delay between when the primary server creates a log backup of a primary database and when the secondary server restores the log backup to a corresponding secondary database. Before a failover can occur, a secondary database must be brought fully up-to-date by manually applying any unrestored log backups.
Database Replication uses a publish-distribute-subscribe model. This lets a primary server, referred to as the Publisher, distribute data to one or more secondary servers, or Subscribers. Replication can enable almost real-time availability and scalability across these servers. It supports filtering to provide a subset of data at Subscribers. Subscribers are online and available for reporting or other functions. SQL Server offers three types of replication: snapshot, transactional, and merge.
Only committed transactions are sent to the distribution database. This means that any transactions that are in process but not committed to disk on the Publisher will be lost if users must rely upon a Subscriber due to a failure at the Publisher.
AOHA is Microsoft’s replacement for Mirroring beginning with SQL Server 2012.
AOHA is much more than Mirroring with many options. There are many large and long white papers describing the many options available with, and the complexities of, AOHA. They are not trivial.
At its highest level of abstraction, AOHA technology can most succinctly be thought of as giving application databases the ability to failover as part of Windows Server Failover Clusters (WSFC). When a WSFC fails over (switches) to another node of the cluster, AOHA groups of databases will remain up and available for production as if they were part of the WSFC (even though they are defined in distinct and separate Availability Groups). This is a great over-simplification but it is difficult to succinctly describe this complicated technology.