ADO.NET connection failover

If a client application attempts to connect to a host in the OpenText™ Analytics Database cluster that is down, the connection attempt fails when using the default connection configuration. This failure usually returns an error to the user. The user must either wait until the host recovers and retry the connection or manually edit the connection settings to choose another host.

Due to the database's distributed architecture, you usually do not care which database host handles a client application's connection. You can use the client driver's connection failover feature to prevent the user from getting connection errors when the host specified in the connection settings is unreachable. The JDBC driver gives you several ways to let the client driver automatically attempt to connect to a different host if the one specified in the connection parameters is unreachable:

Configure your DNS server to return multiple IP addresses for a host name. When you use this host name in the connection settings, the client attempts to connect to the first IP address from the DNS lookup. If the host at that IP address is unreachable, the client tries to connect to the second IP, and so on until it either manages to connect to a host or it runs out of IP addresses.
Supply a list of backup hosts for the client driver to try if the primary host you specify in the connection parameters is unreachable.
(JDBC only) Use driver-specific connection properties to manage timeouts before attempting to connect to the next node.

For all methods, the process of failover is transparent to the client application (other than specifying the list of backup hosts, if you choose to use the list method of failover). If the primary host is unreachable, the client driver automatically tries to connect to other hosts.

Failover only applies to the initial establishment of the client connection. If the connection breaks, the driver does not automatically try to reconnect to another host in the database.

Choosing a failover method

You usually choose to use one of the two failover methods. However, they do work together. If your DNS server returns multiple IP addresses and you supply a list of backup hosts, the client first tries all of the IPs returned by the DNS server, then the hosts in the backup list.

Note

If a host name in the backup host list resolves to multiple IP addresses, the client does not try all of them. It just tries the first IP address in the list.

The DNS method of failover centralizes the configuration client failover. As you add new nodes to your OpenText™ Analytics Database cluster, you can choose to add them to the failover list by editing the DNS server settings. All client systems that use the DNS server to connect to the database automatically use connection failover without having to change any settings. However, this method does require administrative access to the DNS server that all clients use to connect to the database cluster. This may not be possible in your organization.

Using the backup server list is easier than editing the DNS server settings. However, it decentralizes the failover feature. You may need to update the application settings on each client system if you make changes to your database cluster.

Using DNS failover

To use DNS failover, you need to change your DNS server's settings to map a single host name to multiple IP addresses of hosts in your database cluster. You then have all client applications use this host name to connect to the database.

You can choose to have your DNS server return as many IP addresses for the host name as you want. In smaller clusters, you may choose to have it return the IP addresses of all of the hosts in your cluster. However, for larger clusters, you should consider choosing a subset of the hosts to return. Otherwise there can be a long delay as the client driver tries unsuccessfully to connect to each host in a database that is down.

Using the backup host list

To enable backup list-based connection failover, your client application has to specify at least one IP address or host name of a host in the BackupServerNode parameter. The host name or IP can optionally be followed by a colon and a port number. If not supplied, the driver defaults to the standard database port number (5433). To list multiple hosts, separate them by a comma.

The following example demonstrates setting the BackupServerNode connection parameter to specify additional hosts for the connection attempt. The connection string intentionally has a non-existent node, so that the initial connection fails. The client driver has to backup the hosts to establish a connection to the database.

using System;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder =
        new VerticaConnectionStringBuilder();
            builder.Host = "not.a.real.host:5433";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            builder.BackupServerNode =
        "another.broken.node:5433,v_vmart_node0002.example.com:5433";
            try
            {
                VerticaConnection _conn =
            new VerticaConnection(builder.ToString());
                _conn.Open();
                VerticaCommand sqlcom = _conn.CreateCommand();
                sqlcom.CommandText = "SELECT node_name FROM current_session";
                var returnValue = sqlcom.ExecuteScalar();
                Console.WriteLine("Connected to node: " +
            returnValue.ToString() + "\n");
                _conn.Close();
                Console.WriteLine("Disconnecting.\n");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }
    }
}

The ADO.NET connection pool reuses database connections for efficiency. When the Close() function is called on the connection in the application code, it does not terminate the physical connection to the database. Instead, the connection is returned to the pool for future use. All pooled connections must be closed or released when the application finishes execution using ClearPool.

Pooled Connections (Pooling=true)

The connection returns to the pool for reuse and is not physically closed. It is available for future use by other parts of the application. No new connection is created; the connection is reused, improving performance by avoiding the need to establish new connections.

Non-Pooled Connections (Pooling=false)

The connection is physically closed terminating the connection to the database. Whenever the Open() function is called, a new connection must be established. This impacts performance as the application needs to create and close the connections repeatedly.

The key difference is that the Close() function:

returns the connection to the pool for pooled connections.

closes the connection to the database for non-pooled connections.

Example

using System;
using Vertica.Data.VerticaClient; // Vertica ADO.NET driver
public class VerticaConnectionPoolExample

{
    private static string connectionString = "Host=your-vertica-server;Database=your-database;
    User ID=your-user-id;Password=your-password;Pooling=true;Max Pool Size=100;Min Pool Size=5;";

        public static void Main(string[] args)
        {
            // Open and close some pooled connections
            for (int i = 0; i < 5; i++)
            {
              using (var connection = new VerticaConnection(connectionString))
              {
                connection.Open();
                Console.WriteLine($"Connection {i + 1} opened.");
                // Simulate some database operations
              } 
              Console.WriteLine($"Connection {i + 1} closed and returned to pool.");
            }
            // Now we explicitly clear the connection pool
            // VerticaConnection.ClearPool() can be used to clear the pool of connections to the server
            // This is usually only necessary when you want to force all connections to be released

            VerticaConnection.ClearPool(new VerticaConnection(connectionString)); // Clear the connection pool

            Console.WriteLine("Vertica connection pool cleared.");

            // Exit the application
            Console.WriteLine("Application is exiting.");
        }
}

Notes

When native connection load balancing is enabled, the additional servers specified in the BackupServerNode connection parameter are only used for the initial connection to an OpenText™ Analytics Database host. If host redirects the client to another host in the database cluster to handle its connection request, the second connection does not use the backup node list. This is rarely an issue, since native connection load balancing is aware of which nodes are currently up in the database. See Load balancing in ADO.NET.
Connections to a host taken from the BackupServerNode list are not pooled for ADO.NET connections.