High Availability Solutions

High Availability Solutions (SQL Server)

 

Updated: May 19, 2016

Applies To: SQL Server 2016

This topic introduces several SQL Server high-availability solutions that improve the availability of servers or databases. A high-availability solution masks the effects of a hardware or software failure and maintains the availability of applications so that the perceived downtime for users is minimized.

Note! Want to know which SQL Server editions support a given high availability solution? See the "High Availability (Always On)" section of Features Supported by the Editions of SQL Server 2016.

SQL Server provides several options for creating high availability for a server or database. The high-availability options include the following:

  • Always On Failover Cluster Instances
    As part of the SQL Server Always On offering, Always On Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level—a failover cluster instance (FCI). An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable.

For more information, see Always On Failover Cluster Instances (SQL Server).

  • Always On Availability Groups
    Always On Availability Groups is an enterprise-level high-availability and disaster recovery solution introduced in SQL Server 2012 to enable you to maximize availability for one or more user databases. Always On Availability Groups requires that the SQL Server instances reside on Windows Server Failover Clustering (WSFC) nodes. For more information, see Always On Availability Groups (SQL Server).

Note! An FCI can leverage Always On Availability Groups to provide remote disaster recovery at the database level. For more information, see Failover Clustering and Always On Availability Groups (SQL Server).

  • Database mirroring. Note! This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use Always On Availability Groups instead.
    Database mirroring is a solution to increase database availability by supporting almost instantaneous failover. Database mirroring can be used to maintain a single standby database, or mirror database, for a corresponding production database that is referred to as the principal database. For more information, see Database Mirroring (SQL Server).

  • Log shipping
    Like Always On Availability Groups and database mirroring, log shipping operates at the database level. You can use log shipping to maintain one or more warm standby databases (referred to as secondary databases) for a single production database that is referred to as the primary database. For more information about log shipping, see About Log Shipping (SQL Server).

Our recommendation for providing data protection for your SQL Server environment:

  • For data protection through a third-party shared disk solution (a SAN), we recommend that you use Always On Failover Cluster Instances.

  • For data protection through SQL Server, we recommend that you use Always On Availability Groups.

    We recommend using log shipping if you are running an edition of SQL Server that does not support Always On Availability Groups. For information about which editions of SQL Server support Always On Availability Groups, see the "High Availability (Always On)" section of Features Supported by the Editions of SQL Server 2016.

Windows Server Failover Clustering (WSFC) with SQL Server
Database Mirroring: Interoperability and Coexistence (SQL Server)
Deprecated Database Engine Features in SQL Server 2016

Community Additions

ADD
Show:
© 2016 Microsoft