This topic presents a variety of SQL Server high-availability solutions used to help improve the availability of both servers and databases. High-availability solutions disguise the effects of hardware or software failure, further upholding the availability of applications. That way the alleged downtime for users is minimised.
SQL Server provides numerous options to produce high availability for a server or database. High-Availability options include the following:
A part of SQL Server Always On option, Always On Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality providing high availability locally through redundancy at the server-instance level—a failover cluster instance (FCI). An FCI is a lone occurrence of SQL Server installed across Windows Server Failover Clustering (WSFC) nodes and, quite possibly, across several subnets. An FCI shows up on the network as an occurrence of SQL Server running on a single computer, but the FCI delivers failover from one WSFC node to another, that’s if the current node becomes unavailable.
Always On Availability Groups is described as an enterprise-level high-availability and disaster recovery solution that was introduced in SQL Server 2012 (11.x) which permits you to maximise availability for more than one user database. Always On availability groups that need SQL Server instances reside alongside Windows Server Failover Clustering (WSFC) nodes.
Please Note! An FCI may leverage Always On availability groups to provide remote disaster recovery at the database level.
Database mirroring is used to enhance database availability by supporting rapid failover. In addition, Database mirroring can be used to uphold a single standby database, or “mirror database”, for a matching production database that is referred to as the “principal database”.
Please Note! This feature is currently in maintenance mode and could possibly be removed from a future updated version of Microsoft SQL Server. If focusing on new development work, avoid using this feature and arrange modification for the applications that are currently running this feature. We recommend using Always On availability groups instead.
What we recommend to provide data protection for your SQL Server environment:
If running on a version of SQL Server that does not support Always On availability groups, then we recommend using log shipping.