Configuring SQL Mirroring

SQL Mirroring is a ScaleArc-based failover solution for all three SQL server database mirroring replication modes; that is, Synchronous, Asynchronous, and Synchronous with a witness.

Consider the benefits and limitations of SQL Mirroring in ScaleArc before you configure this setting.

Follow these steps to configure SQL Mirroring:

Prerequisites

These are the prerequisites 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.

   Note: ScaleArc does not display a separate screen for setting up SQL Mirroring. Follow the instructions here to set up the database server as a Standalone.
2. The databases that are subject to failover should be configured in the Users and DBs section within a cluster.

3. At least one of the databases between primary and secondary server must be configured as Mirroring and the same should be configured within the Users and DBs section.  For instance, the first database configured in Users and DBs section within ScaleArc should be configured as Mirroring. 

   Tip: See this KB article for details on how ScaleArc works with databases that have SQL Mirroring.

Steps to Configure SQL Mirroring

1. On the ScaleArc dashboard, click the CLUSTERS tab then click on the Auto Failover button in the Status column.
   
    
   
   
2. Locate the Failover Type panel and click SQL Mirroring.
   
    
    

3. Complete the fields as outlined in the table below:
   
    
    

   Field	Description	Default/User input
   Switch Delay Time (seconds)	Specifies the time (in seconds) to wait before the standby database is promoted to Active Read-Write after demoting the current Active Read-Write server.	Default is 5 seconds.
   Wait for Sync	When turned ON, this option makes sure the newly promoted active Read/Write server. May cause increased failover time.	Default is ON.
   Retry Attempts	Specifies the number of attempts to check if the replication is in sync between the original Read/Write server and to be promoted active stand by server.	Default is 3.
   Retry Interval	Specifies the retry interval (in seconds) between the consecutive retry attempts.	Default is 1 second.
   Force Failover	Performs failover in the case of unsuccessful wait for sync or SQL I/O errors or replication errors. This option can result in data loss and increase recovery times.	Default is OFF.

Switch roles

Once you have configured and implemented SQL Mirroring, you can conduct maintenance or upgrades by initiating switchover to change the database roles between primary and secondary in the backend.

1. Click the Switchover button. 
2. Enter the grace period (Connection Bleedoff Time) to complete the existing connections.
   
   
   
   
3. Click Done to start the Switchover.

Benefits of SQL Mirroring

• Maximize resource utilization and save cost through:
  • Enabling automatic failover without additional witness server
  • Avoiding the cost of witness server set up and maintenance
• Improve application performance/uptime and avoid application errors by:
  • Enabling automatic failover without additional witness server
  • Routing traffic around offline servers, including across multiple data centers for maximum availability
  • Enabling application-transparent automatic server failover
  • Handling switchover in asynchronous operation mode
  • Using surge queue and connection migration 
  • Zero downtime system migration and upgrade
• Improve administrative efficiency and avoid manual setup/deployment by:
  • Providing automatic failover without an additional witness server
  • Effortlessly managing the server hosting the databases through ScaleArc

Limitations of SQL Mirroring

• If any database configurations in Users & DBs are in suspended mode, then ScaleArc aborts the switchover process.
• If ScaleArc is unable to reach a primary server, it does not perform the failover even when the primary server is healthy.