High Availability - AlwaysOn Availability Groups

Overview:
AlwaysOn Availability Groups can be thought of as a hybrid of the best of clustering, and the best of mirroring. It must be configured on a Windows Server Failover Cluster, but rather than installing SQL Server as a clustered installation with shared storage for the databases, a separate installation of SQL Server is installed on each node of the Windows Cluster, using the local disks of the cluster node (i.e. the OS is clustered, SQL Server isn't). Like clustering, the Availability Groups have a shared IP / name that everyone can point to. Unlike clustering, databases are mirrored across all servers in the cluster. There can be one primary, and up to four replicas. Updates can be asynchronous or synchronous. Requires Full recovery mode for databases.

Secondaries / Read Only Replicas:
Secondaries can be used as read only databases for backups / reporting / DBCC checks etc, although these functions lend themselves more to secondary replicas in an asynchronous configuration, as synchronous secondaries are more useful for failover.

Connecting to Secondaries:
You can connect directly to a secondary replica, or indirectly by using the "ApplicationIntent" (either ReadWrite or ReadOnly) within the connection string for SQL Server connections, with Read Only routing configured on the listener service for the main shared instance name.

Failover Types:
From MSDN:

• Synchronous-commit replicas support two settings—automatic or manual. The "automatic" setting supports both automatic failover and manual failover. To prevent data loss, automatic failover and planned failover require that the failover target be a synchronous-commit secondary replica with a healthy synchronization state (this indicates that every secondary database on the failover target is synchronized with its corresponding primary database). Whenever a secondary replica does not meet both of these conditions, it supports only forced failover. Note that forced failover is also supported a replicas whose role is in the RESOLVING state.
• Asynchronous-commit replicas support only the manual failover mode. Moreover, because they are never synchronized, they support only forced failover.

Pros:
Good for high availability, and reporting via usable secondary copies of the data.

Cons:
You cannot add system databases to an Availability Group. To that end, logins, SQL Jobs etc are not available on secondary replicas automatically - these must be manually set up / kept in sync.
Expensive, as you must license each node individually.
Doubles your data (or more) - as you have multiple copies of the databases many times.
Distributed transactions are not guaranteed in the event of failover.
All servers in the availability group must be in the same domain

Resources:
Great post here around HA options in SQL Server: http://dba.stackexchange.com/questions/53815/clustering-vs-transactional-replication-vs-availability-groups

High Availability - Misc Resources

Some great resources about High Availability in SQL Server:

Overview of High Availability options:
http://www.toadworld.com/platforms/sql-server/w/wiki/9739.alternatives-to-replication.aspx

Pros and Cons of High Availability Options
http://serverfault.com/questions/99228/high-availability-on-sql-server-cons-and-pros-of-solutions

MSDN Overview of High Availability options in SQL Server 2014:
https://msdn.microsoft.com/en-us/library/ms190202.aspx

High Availability - AlwaysOn Failover Clustering

Overview

Failover Clustering (or AlwaysOn Failover Clustering) is a means for providing redundancy at a server level. Should one server fail, another server will take it's place automatically. Clustering requires two (or more) servers, and a shared disk. Standard Edition will only support a 2 node failover cluster.

Pros

Automatic failover from one server to another
Appears as one SQL Server on the network
Redundancy at a server level
Robust
AlwaysOn supports failover cluster nodes in different geographical locations (via virtual LAN)

Cons

Expensive, more involved (tricky) to set up and configure than other High Availability methods, can be sluggish to failover. Not all SQL features are able to be clustered (i.e. Reporting Services). Data is only in ONE place (i.e. on a shared disk) - use RAID / Disk Mirroring to mitigate this risk

Clustering resources

Really great post here (by Kendra Little on Brent Ozar blog): http://www.brentozar.com/archive/2012/02/introduction-sql-server-clusters/
MSDN: https://technet.microsoft.com/en-us/library/ms189134(v=sql.110).aspx

High Availability - Log Shipping

Overview

Log shipping is a means for keeping a warm standby copy of a database on a secondary server. It works by continually taking, copying, and restoring, transaction log backups from the primary server to the secondary server on a schedule. Log Shipping is available in Standard and Enterprise Edition

Pros

Very performant
Can read from the secondary database (between log restores), as long as Log Shipping is configured to keep this secondary database in "Standby" (not possible if Log Shipping is configured to keep secondary database in "Recovering" state)
Can ship logs to multiple secondary servers
Cheap
Easy to set up

Cons

Manual failover is required in the event of a failure on the primary server (to failover manually: apply uncommitted transaction logs, WITH RECOVERY)
Additional log backups not part of the scheduled log shipping backups will break the chain and stop log shipping from functioning
Data cannot be manipulated between source and target system, and it's not possible to only transfer a portion of the data
Per database level - if you want to log ship 5 databases, you have to repeat the log shipping configuration for each database (i.e. set up log shipping 5 times)
There is a lag between the log backup being taken at the primary database, and the logs being applied to the secondary database
Logs are only applied on the schedule you set - if you ship logs every 15 minutes, the secondary database can get up to 15 mins out of sync with the primary server. This can however be useful, if an erroneous data change is made at the primary, you can halt log shipping, and check the data on the secondary

Log Shipping resources

Brent Ozar blog: http://www.brentozar.com/archive/2013/03/log-shipping-faq/
Setting up Log Shipping: https://msdn.microsoft.com/en-us/library/ms190640.aspx

High Availability - Database Mirroring

Overview

Mirroring is a means to provide a secondary copy of your data. The secondary copy is automatically updated when data on the primary server is changed.

Different types of mirroring

Synchronous (high safety / high availability - database SAFETY option set to FULL) – data is always kept exactly the same on the primary and secondary server. Available in Standard & Enterprise edition. Automatic failover available with a witness server

Asynchronous (high performance - database SAFETY option set to OFF) – data is eventually up to date on secondary server

Mirroring can be configured with or without a witness server. Available in Enterprise Edition
Mirroring is per database, and only works for databases using Full recovery model

Pros

Almost instant, automatic failover when using a witness server in synchronous (high safety / high availability) mode

Cons

Mirroring is deprecated in future version of SQL Server. Microsoft advise not to use this for any new SQL Server implementations, and look to remove this function from systems that currently use it (source: https://msdn.microsoft.com/en-us/library/ms190202.aspx)Can cause some performance overhead in Synchronous configurationCan only mirror to one secondary serverMaster database cannot be mirrored - logins have to be added manually to secondary server, then mapped to the database users
Cannot access mirrored data directly (i.e. for reporting) - although you can set up a snapshot of the mirrored database and access this for reporting
Cannot mirror a database containing FILESTREAM filegroups
You can't detach a database that is being mirrored
Can only have ONE mirror copy of the database

Notes

Removing mirroring leaves the secondary copy of the database in a RECOVERING (unusable) state. The database must be recovered to be usable.

Mirroring resources

Brent Ozar blog: http://www.brentozar.com/archive/2013/07/database-mirroring-faq/
Setting up mirroring: https://technet.microsoft.com/en-us/library/ms190941(v=sql.110).aspx
MSDN on Mirroring: https://msdn.microsoft.com/en-us/library/ms189852.aspx