When reviewing your SQL Server HADR strategy for Azure IaaS here are main points of the different options: Windows Server Failover Cluster (WSFC), SQL Server AlwaysOn Availability Groups, Clusters, Availability Listeners, and Database Mirroring. WSFC provides infrastructure support and is required for both “AlwaysOn Availability groups” and “AlwaysOn Failover cluster”.
WSFC Clusters (sometimes called AlwaysOn Failover Clusters)
- WSFC Failover Cluster is used for HA
- WSFC provides redundancy at server level.
- Uses one shared (mirrored) storage server as Single Point of Failure between to active (primary) and passive (secondary) nodes. Thus, no copy of Tx Log. Failover takes time to roll to next node.
- Primary is running (active) no SQL Server running in secondary. When primary fails secondary becomes active.
- Mirroring is an option on the cluster and does not do auto failover. You have to manually use the secondary at failover. It will automatically start the secondary node if the active node fails (no heartbeat response in time and routes to secondary).
- Use the cluster name in the database connection string. If failover then cluster will map it to secondary failover server
SQL Server AlwaysOn Availability Groups (SSAOGs)
- Can use more than two nodes
- Is a logical association across more than one SQL Server cluster nodes.
- Since TX log is always being updated the secondary servers are always up and running as active/active.
- No use for shared (mirrored) storage
- AAA Group constantly checks for failover – if primary replica node is suddenly not responding AAL VIP points to a secondary and makes that the primary.
- Multiple secondary servers alternatively used for read-only queries, reporting, and backup.
- AlwaysOn Availability Group is used for HA and DR scenarios.
- Faster recovery and zero data loss using synchronous mode
SQL Server AlwaysOn Availability Group Listener (SSAOAGL)
- Use the AAAG Listener (VIP) in the database connection string.
- AAAGL abstracts out the actual database of the primary and secondary servers.
- Requires Windows Server 2012 to set up AAAGL in Azure
SQL Server Database Mirroring
- MS doesn’t recommend database mirroring, for any new development it shouldn’t be used. Also this feature will not be available in future versions of SQL Server(http://technet.microsoft.com/en-us/library/ms189852.aspx)
- Automatic failover in database mirroring is possible by specifying Failoverpartner attribute in connection string, I was not aware of this connection string parameter. So automatic failover is possible in database mirroring with some connection string parameters (http://technet.microsoft.com/en-us/library/ms175484.aspx).
- This link has some good details on HA and DR scenarios on SQL on Azure (IaaS) http://msdn.microsoft.com/en-us/library/jj870962.aspx