Understanding Connection Failover and Client Load Balancing

Connection failover, client load balancing, and connection retry are three useful features that are available in the Pervasive.SQL data provider.

Using Connection Failover

Connection failover allows an application to connect to an alternate, or backup, database server if the primary database is unavailable, for example, because of a hardware failure or traffic overload. Connection failover ensures that the data on which your critical .NET applications depend is always available.

You can customize the Pervasive.SQL data provider for connection failover by configuring a list of alternate databases that are tried if the primary server is not accepting connections. Connection attempts continue until a connection is successfully established or until all the alternate databases have been tried a certain number of times.

For example, suppose you have an environment with multiple database servers, as shown in the following illustration. Database Server A is designated as the primary database server, Database Server B is the first alternate server, and Database Server C is the second alternate server.

Figure 2-1 Connection Failover

First, the application attempts to connect to the primary database, Database Server A (1). If connection failover is enabled and Database Server A fails to accept the connection, the application attempts to connect to Database Server B (2). If that connection attempt also fails, the application attempts to connect to Database Server C (3).

In this scenario, it is probable that at least one connection attempt would succeed, but if no connection attempt succeeds, the data provider can retry the primary server and each alternate database for a specified number of attempts. You can specify the number of attempts that are made through the connection retry feature. You can also specify the number of seconds of delay, if any, between attempts through the connection delay feature. For more information about connection retry, see Using Connection Retry .

The Pervasive.SQL data provider fails over to the next alternate server only if it cannot establish communication with the current alternate server. If the data provider successfully establishes communication with a database and the database rejects the connection request because, for example, the login information is invalid, then the data provider generates an exception and does not try to connect to the next database in the list. It is assumed that each alternate server is a mirror of the primary and that all authentication parameters and other related information are the same.

Connection failover provides protection for new connections only and does not preserve states for transactions or queries. For details on configuring connection failover for your data provider, see Configuring Connection Failover .

Client Load Balancing

Client load balancing works with connection failover to distribute new connections in your environment so that no one server is overwhelmed with connection requests. When both connection failover and client load balancing are enabled, the order in which primary and alternate databases are tried is random. For example, suppose that client load balancing is enabled as shown in the following illustration:

First, Database Server B is tried (1). Then, Database Server C may be tried (2), followed by a connection attempt to Database Server A (3). In contrast, if client load balancing was not enabled in this scenario, each database would be tried in sequential order, primary server first, then alternate servers based on their entry order in the alternate servers list.

For details on configuring client and load balancing for your data provider, see Configuring Connection Failover .

Using Connection Retry

Connection retry allows the Pervasive.SQL data provider to retry connections to database servers. Connection retry can be an important strategy for system recovery. For example, suppose you have a power failure scenario in which both the client and the server fail. When the power is restored and all computers are restarted, the client may be ready to attempt a connection before the server has completed its startup routines. If connection retry is enabled, the client application can continue to retry the connection until a connection is successfully accepted by the server.

Connection retry can be used in environments that only have one server or can be used as a complementary feature in connection failover scenarios with multiple servers.

Using connection string options, you can specify the number of times the data provider attempts to connect and the time in seconds between connection attempts. For details on configuring connection retry, see Configuring Connection Failover .

Configuring Connection Failover

Connection failover allows an application to connect to an alternate, or backup, database server if the primary database server is unavailable, for example, because of a hardware failure or traffic overload. Connection failover ensures that the data on which your critical .NET applications depend is always available.

See Understanding Connection Failover and Client Load Balancing for more information about connection failover.

To configure connection failover, you must specify a list of alternate database servers that are tried at connection time if the primary server is not accepting connections. To do this, use the Alternate Servers connection string option. Connection attempts continue until a connection is successfully established or until all the databases in the list have been tried once (the default).

Optionally, you can specify the following additional connection failover features:

The number of times the data provider attempts to connect to the primary and alternate servers after the initial connection attempt. By default, the data provider does not retry. To set this feature, use the Connection Retry Count connection string option.

The wait interval, in seconds, used between attempts to connect to the primary and alternate servers. The default interval is 3 seconds. To set this feature, use the Connection Retry Delay connection option.

If load balancing is enabled, the data provider uses a random pattern instead of a sequential pattern in its attempts to connect. The default value is not to use load balancing. To set this feature, use the Load Balancing connection string option.

You use a connection string to direct the data provider to use connection failover. See Connection String Options for the Pervasive.SQL Provider .

The following connection string configures the Pervasive.SQL data provider to use connection failover in conjunction with all of its optional features-load balancing, connection retry, and connection retry delay:

Conn = new 
PsqlConnection("ServerName=myServer.1583;UID=test
;PWD=secret;ServerDSN=SRVRDEMO;
Alternate Servers=(Host=AcctServer, 
Host=AcctServer2);Connection Retry Count=4;
Connection Retry Delay=5;Load 
Balancing=true;Connection Timeout=60;")

Specifically, this connection string configures the Pervasive.SQL data provider to use two alternate servers as connection failover servers, to attempt to connect four additional times if the initial attempt fails, to wait five seconds between attempts, and to try the primary and alternate servers in a random order. Each connection attempt lasts for 60 seconds, and uses the same random order that was established on the first retry.