Failover Support for SQL Server Mirroring

How does ScaleArc Support Failover for SQL Server Mirroring?

Failover Support for SQL Server Mirroring

Introduction to SQL Server Mirroring:

There are different types of techniques for replication technologies in SQL Server - P2P, Peer, Transactional and Mirroring. Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server database engine.  Starting database mirroring on a database initiates a relationship known as a database mirroring session between these server instances.

One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. 

The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At a time, one partner performs principal role, other partner performs mirror role. 

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record.

Both synchronous as well as asynchronous operations are supported in SQL Mirroring.  In case of ASynchronous operation, transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance while in case of Synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.

There are two modes of SQL Mirroring operation:

• High-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency. High-safety mode can be with or without auto failover. High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness does not serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server. 

• High-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. In high-performance mode, as soon as the principal server sends a log record to the mirror server, the principal server sends a confirmation to the client. It does not wait for an acknowledgement from the mirror server. This means that transactions commit without waiting for the mirror server to write the log to disk. Such asynchronous operation enables the principal server to run with minimum transaction latency, at the potential risk of some data loss.

Within the context of a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server. In role switching, the mirror server acts as the failover partner for the principal server. When a role switch occurs, the mirror server takes over the principal role and brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role, and its database becomes the new mirror database. Potentially, the roles can switch back and forth repeatedly.

There are two forms of role switching in ScaleArc:        

• Automatic failover:  This requires high-safety mode and the presence of the mirror server and a witness. The database must already be synchronized, and the witness must be connected to the mirror server. The role of the witness is to verify whether a given partner server is up and functioning. If the mirror server loses its connection to the principal server but the witness is still connected to the principal server, the mirror server does not initiate a failover.  

•  Manual failover:This requires high-safety or high performance mode. The partners must be connected to each other and the database must already be synchronized. This type of failover is a forced service (with possible data loss) if the principal server has failed and the mirror server is available.

Challenges:

Following are some of the key challenges faced by the SQL Mirroring users:

1. Lack of automatic failure without an additional witness server in SQL Mirroring
2. Potential data loss in asynchronous operation mode. 
3. Significantly low performance 
4. SQL Mirroring works only at the database level and not at server level.
5. Excessive manual administration of Principal, Mirror & Witness Server

ScaleArc for SQL Mirroring 

ScaleArc supports SQL Mirroring by performing role changes during the occurrence of failover.  ScaleArc supports application-transparent automated database failover, shielding apps from the downtime and preventing application errors. Operating at the SQL protocol layer, ScaleArc instantly and transparently routes database traffic around downed servers - even across multiple data centers for maximum availability - ensuring business continuity for SQL environments. ScaleArc now provides both an internal implementation as well as External API Support.

ScaleArc supports both Auto-Failover and Manual Failover.

Auto-Failover - When a master server fails, Auto-Failover aims to simplify and automate the failover process to a slave, or another master in the same cluster.

Switchover - This option can be used to trigger a manual failover of active read-write server to the standby server.  Here the user can specify a manual failover timeout. The active connections in intermediate state that do not complete by the specified timeout limit will not be switched to a Standby server. Active connections which are not in intermediate state will be switched to a Standby server immediately.

External Implementation (API support): The external API allows users to call out to an external script which can be customized to meet users’ specific needs for failure support to SQL Mirroring. 

 Pre-requisites:

Following are the pre-requisites for configuring SQL Mirroring in ScaleArc:

1. One of the ScaleArc server should be configured as Read + Write and the other server should be configured as Standby, No traffic.
2. The databases to be failed over need to be configured in the Users & DB’s section within a cluster.
3. At least one of the databases between primary and secondary server must be configured as Mirroring and same should be configured within Users & DB’s section

4. First database configured in Users & DB’s section within ScaleArc must be configured as Mirroring.

Configuration on Scalearc Box:

Step 1:  We have the following database servers as indicated from the SQL Server Management Studio configured in clusters CLS-Mirror and CLS-Mirror2.

Step 2: These database servers are configured within a ScaleArc cluster as shown below.

Cluster 1 - CLS-Mirror has two database servers, 10.0.101.91 is the primary server and

10.0.101.92 is the secondary server.

 Cluster 2 - CLS-Mirror 2 has two database servers, 10.0.101.92 is the primary server and 10.0.101.91 is the secondary server.

Step 3: 

All the databases which need to be failed over need to be configured in the Users & DB’s section within a Cluster.

The principal databases on 10.9.101.92 (Primary Server), PROTEST1234, exact1, exact2 are added to the cluster CLS-MIRROR2 and configured as mirroring. 

Use Cases

Implementation of SQL Mirroring in ScaleArc in Active-Active Mode:

In an active-active mode, database servers have databases configured in Principal as well as in Mirror state.

E.g., consider the following 2 database servers

In database server 10.0.101.91, PROTEST123 is configured as principal and PROTEST1234, exact1 and exact2 are configured as Mirror.

In database server 10.0.101.92, PROTEST1234, exact1 and exact2 are configured as Principal and PROTEST123 is configured as Mirror.

The section below will describe the implementation of SQL Mirroring in ScaleArc (Active-Active use case).

Switchover:

After setting up the ScaleArc machine with database servers and databases as described in the Configuration on Scalearc Box section, we perform the following steps to initiate a Switchover.

Step 1:

To implement SQL mirroring in ScaleArc, navigate to the Auto-failover tab in cluster settings of Cluster CLS-MIRROR 2. Make sure that Auto-failover is set to ON and click on SQL Mirroring failover type.

Step 2: Click Switchover to trigger manual failover of Primary Server and Standby Server & enter Connection Bleedoff Time. 

Step 3: Once Switchover is triggered, ScaleArc performs role changes and 10.0.0.92 becomes secondary and 10.0.0.91 becomes principal as shown below: 

Step 4: On ScaleArc machine the following changes are visible.

Key ScaleArc Benefits

Following are the benefits of SQL Mirroring in ScaleArc;

• Maximize resource utilization and save cost through

−       Enabling automatic failure without an additional witness server. 

−       Avoiding the cost of witness server setup and maintenance.

• Improve application performance/uptime by

−       Enabling automatic failure without an additional witness server 

−       Routing traffic around downed servers - even across multiple data centers for maximum availability.

−       Enabling application transparent automatic server failover

−       Handling auto failover in asynchronous operation mode.

−       Zero-downtime system migration and upgrade

• Avoid Application Errors

−       Routing traffic around downed servers - even across multiple data centers for maximum availability.

−       Enabling application transparent automatic server failover

−       Handling auto failover in asynchronous operation mode.

−       Zero-downtime system migration and upgrade

• Improve Administrative Efficiency & Avoid Manual Setup/Deployment by

−       Providing automatic failure without an additional witness server 

−       Handling auto failover in asynchronous operation mode.

−       Zero-downtime system migration and upgrade

−       Effortlessly manage servers hosting the databases through ScaleArc

Limitations of SQL Mirroring in ScaleArc

The following section describes the limitations of SQL Mirroring in ScaleArc:

• As a note of caution, if any database configured in Users & DB’s are in suspended mode then ScaleArc aborts the Switchover process.
• In a situation where the primary server is healthy but ScaleArc is not able to reach the Primary Server, then ScaleArc will not perform failover.