Database failover options and procedures using mirroring

Database mirroring

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. 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. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

More information about SQL Server Database Mirroring can be found here: http://technet.microsoft.com/en-us/library/ms189852.aspx

Role switching

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. The following three forms of role switching exist.

• 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. For more information, see Database Mirroring Witness.
• Manual failover
  This requires high-safety mode. The partners must be connected to each other, and the database must already be synchronized.
• Forced service (with possible data loss)
  Under high-performance mode and high-safety mode without automatic failover, forcing service is possible if the principal server has failed and the mirror server is available.

Database failover support in Verba

Verba is compatible with each mirroring mode and will act similarly regardless of the mirroring setup.

In case of a disaster, either the DB administrator switches the roles, or it happens automatically. Until the roles switched, Verba web interface will be unaccessible, but recording will work since when recorder engines cannot connect to the database, they are collecting every information that should had been inserted, and will insert all of the collected data once the connection is up.

Obviously, Verba services have to learn that they need to connect to the new principal DB server. There are three different options to achieve it:

SQL Server built-in "Failover Partner" feature

The mirror database can be added to the so called "Connection String". This string is used by the SQL Server client libraries, and if it contains the Failover Partner information, then after the original principal server goes down, the library will automatically reconnect to the new principal server.

Using this method, the mirror databases are configured in advance, so no additional configuration  is required when the database roles are switched, and no service restart is needed.

After a role switch, each Verba component's each database connection will be invalid, and the next database query will fail. Again, that will not cause any loss in regards of the recorded data, because when a SQL query fails, the recorder services put the data to their cache, and will try to synchronize later. The web interface periodically tests the database connections, and if a connection is invalid, it tries to reconnect to the database. As a result, the interface will be usable in a few seconds after the roles switched.

Failover Partner can be configured for each Verba server at the database configuration. You also need to install the appropriate native SQL client:

Changing the database server host name's DNS configuration

If the customer thinks that the probability of a Role Switching is very low, then this option might makes sense.

When the original principal server goes down, and the database administrator decides to switch the roles, after the role switching, the DNS server(s) configuration has to be changed such the database host name points to the new principal server's IP address. Verba servers will connect to the new database as soon as the operating system recognizes the DNS change.

The DNS information is cached, and until it is expired, the servers will resolve the database server host name to the old IP address. In order to have the DNS change recognized by the servers as soon as possible, you might consider performing a DNS flush on the Verba servers.

The applications' existing connections will not work, so the system will not be stable until every connection is closed and reopened. This fact does not lead to any data loss in the recorded media, because the recorder services maintain their own cache and will insert the cached data as soon as the database connection gets stable.

The web interface periodically tests the database connections, and if a connection is invalid, it tries to reconnect to the database. As a result, the interface will be usable in a few seconds after the roles switched.

Configure Verba to use the new database server

After a Role Switching, instead of changing the DNS configuration, the Verba configuration can be changed as well. The change has to be applied on each Verba server, so depending on the number of Verba servers this approach might take a longer time that the previous ones.

After the database connection configuration changed, almost each of the Verba services have to be restarted as the Verba web application will warn the user.

More information about the database configuration can be found here: http://kb.verba.com/display/docs/Database+connection+settings