Microsoft SQL Server Failover
MS-SQL Server provides several solutions to share data, including:
Data Replication
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
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:
Database Mirroring
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.
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.
|
Operating |
Transaction |
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.
High Availability Mode
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:
