Microsoft SQL Server Failover

MS-SQL Server provides several solutions to share data, including:

With Microsoft technology, data replication is designed to share and broadcast data mainly for functional purposes. For example, a new server can be set up with replicated data to support a business intelligence application. Data replication is configured on a table-by-table or even field-by-field basis. This technology is not designed to support a failover for the system. In the case of failure, without extensive customization, it does not support the swapping of the primary and the secondary servers.

Always On availability groups are a high-availability and disaster-recovery solution that provides an alternative to database mirroring. An availability group is a set of databases that fail over together. Each availability group consists of a primary database and up to eight sets of secondary databases, which serve as failover targets for the group.

For more information about Always On availability groups, refer to the Microsoft SQL Server documentation at the following URL:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server

Mirroring is an easy way to increase database availability. Mirroring is optimized to manage one-way data replication (at the database level). It provides the ability to swap master and slave servers.

Note: Since Microsoft plans on removing database mirroring from future versions of SQL Server, it is recommended to use Always On availability groups instead. For more information, see Setting Up Always On Availability Groups and Configuring ActivID CMS to Use Always On Availability Groups.

A database mirroring session requires the following three partners:

  • Principal—the originating database and server.

  • Mirror—the receiving database and server.

  • Witness—the server that enables the Mirror server in high-safety mode to recognize whether or not to initiate an automatic failover.

A database can be mirrored from one partner server to another. In addition, a given partner server may have different roles for different databases. The three MS-SQL server mirroring operating modes are described in the following table.

MS-SQL Server Mirroring Operating Modes

Operating
Mode

Transaction
Safety

Transfer Mechanism

Quorum Required

Witness Server

Failover Type

High Availability

Full

Synchronous

Yes

Yes

Automatic or Manual

High Protection

Full

Synchronous

Yes

No

Manual Only

High Performance

Off

Asynchronous

No

N/A

Forced Only

This documentation describes the mirroring solution in High Availability operating mode with automatic failover. The mirroring solution is illustrated using the following server examples:

  • SERVER-A, as the Principal server.

  • SERVER-B, as the Mirror server.

  • SERVER-Witness, as the Witness server.

If the Principal database fails, then the High Availability mode supports maximum database availability with automatic failover to the Mirror database. You must set safety to Full and define a Witness server as part of the database mirroring session. The High Availability mode is used best where you have fast and very reliable communication paths between the database servers.

In the High Availability mode, database mirroring is self-monitoring. If the Principal database becomes unavailable, then the Witness server enables the Mirror server instance to change its role.

In this mode, manual and automatic failover are possible.

For more information about database mirroring, refer to the Microsoft SQL Server documentation at the following URL:

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server