Connecting to the database

• 1: Using TLS: installing certificates on Windows
• 2: Opening and closing the database connection (ADO.NET)
• 3: ADO.NET connection properties
• 4: Load balancing in ADO.NET
• 5: ADO.NET connection failover
• 6: Configuring log properties (ADO.Net)

1 - Using TLS: installing certificates on Windows

You can optionally secure communication between your ADO.NET application and Vertica using TLS. The Vertica ADO.NET driver uses the default Windows key store when looking for TLS certificates. This is the same key store that Internet Explorer uses.

Before you can use TLS on the client side, you must implement TLS on the server. See TLS protocol, perform those steps, then return to this topic to install the TLS certificate on Windows.

To use TLS for ADO.NET connections to Vertica:

• Import the server and client certificates into the Windows Key Store.

• If required by your certificates, import the public certificate of your Certifying Authority.

Import the server and client certificates into the windows key store:

1. Copy the server.crt file you generated when you enabled TLS on the server to your Windows Machine.

2. Double-click the certificate.

3. Let Windows determine the key type, and click Install.

Import the public certificate of your CA:

You must establish a chain of trust for the certificates. You may need to import the public certificate for your Certifying Authority (CA) (especially if it is a self-signed certificate).

1. Using the same certificate as above, double-click the certificate.

2. Select Place all certificates in the following store.

3. Click Browse, select Trusted Root Certification Authorities and click Next.

4. Click Install.

Enable SSL in your ADO.NET applications

In your connection string, be sure to enable SSL by setting the SSL property in VerticaConnectionStringBuilder to true, for example:

//configure connection properties    VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
    builder.Host = "192.168.17.10";
    builder.Database = "VMart";
    builder.User = "dbadmin";
    builder.SSL = true;
    //open the connection
    VerticaConnection _conn = new VerticaConnection(builder.ToString());
    _conn.Open();

2 - Opening and closing the database connection (ADO.NET)

Before you can access data in Vertica through ADO.NET, you must create a connection to the database using the VerticaConnection class which is an implementation of System.Data.DbConnection. The VerticaConnection class takes a single argument that contains the connection properties as a string. You can manually create a string of property keywords to use as the argument, or you can use the VerticaConnectionStringBuilder class to build a connection string for you.

To download the ADO.NET driver, go to the Client Drivers Downloads page.

This topic details the following:

• Manually building a connection string and connecting to Vertica

• Using VerticaConnectionStringBuilder to create the connection string and connecting to Vertica

• Closing the connection

To manually create a connection string:

See ADO.NET connection properties for a list of available properties to use in your connection string. At a minimum, you need to specify the Host, Database, and User.

1. For each property, provide a value and append the properties and values one after the other, separated by a semicolon. Assign this string to a variable. For example:

   String connectString = "DATABASE=VMart;HOST=v_vmart_node0001;USER=dbadmin";
   

2. Build a Vertica connection object that specifies your connection string.

   VerticaConnection _conn = new VerticaConnection(connectString)
   

3. Open the connection.

   _conn.Open();
   

4. Create a command object and associate it with a connection. All VerticaCommand objects must be associated with a connection.

   VerticaCommand command = _conn.CreateCommand();
   

To use the VerticaConnectionStringBuilder class to create a connection string and open a connection:

1. Create a new object of the VerticaConnectionStringBuilder class.

   VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
   

2. Update your VerticaConnectionStringBuilder object with property values. See ADO.NET connection properties for a list of available properties to use in your connection string. At a minimum, you need to specify the Host, Database, and User.

   builder.Host = "v_vmart_node0001";
   builder.Database = "VMart";
   builder.User = "dbadmin";
   

3. Build a Vertica connection object that specifies your connection VerticaConnectionStringBuilder object as a string.

   VerticaConnection _conn = new VerticaConnection(builder.ToString());
   

4. Open the connection.

   _conn.Open();
   

5. Create a command object and associate it with a connection. All VerticaCommand objects must be associated with a connection.

   VerticaCommand command = _conn.CreateCommand;
   

To close the connection:

When you're finished with the database, close the connection. Failure to close the connection can deteriorate the performance and scalability of your application. It can also prevent other clients from obtaining locks.

 _conn.Close();

Example usage:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();
        //Perform some operations
            _conn.Close();
        }
    }
}

3 - ADO.NET connection properties

To download the ADO.NET driver, go to the Client Drivers Downloads page.

You use connection properties to configure the connection between your ADO.NET client application and your Vertica database. The properties provide the basic information about the connections, such as the server name and port number, needed to connect to your database.

You can set a connection property in two ways:

• Include the property name and value as part of the connection string you pass to a VerticaConnection.

• Set the properties in a VerticaConnectionStringBuilder object, and then pass the object as a string to a VerticaConnection.

4 - Load balancing in ADO.NET

Native connection load balancing

Native connection load balancing helps spread the overhead caused by client connections on the hosts in the Vertica database. Both the server and the client must enable native connection load balancing. If enabled by both, then when the client initially connects to a host in the database, the host picks a host to handle the client connection from a list of the currently up hosts in the database, and informs the client which host it has chosen.

If the initially-contacted host does not choose itself to handle the connection, the client disconnects, then opens a second connection to the host selected by the first host. The connection process to this second host proceeds as usual—if SSL is enabled, then SSL negotiations begin, otherwise the client begins the authentication process. See About native connection load balancing for details.

To enable native load balancing on your client, set the ConnectionLoadBalance connection parameter to true either in the connection string or using the ConnectionStringBuilder(). The following example demonstrates connecting to the database several times with native connection load balancing enabled, and fetching the name of the node handling the connection from the V_MONITOR.CURRENT_SESSION system table.

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 = "v_vmart_node0001.example.com";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            // Enable native client load balancing in the client,
            // must also be enabled on the server!
            builder.ConnectionLoadBalance = true;

            // Connect 3 times to verify a new node is connected
            // for each connection.
            for (int i = 1; i <= 4; i++) {
                try {
                    VerticaConnection _conn = new VerticaConnection(builder.ToString());
                    _conn.Open();
                    if (i == 1) {
                        // On the first connection, check the server policy for load balance
                        VerticaCommand sqlcom = _conn.CreateCommand();
                        sqlcom.CommandText = "SELECT LOAD_BALANCE_POLICY FROM V_CATALOG.DATABASES";
                        var returnValue = sqlcom.ExecuteScalar();
                        Console.WriteLine("Status of load balancy policy
             on server: " + returnValue.ToString() + "\n");
                    }
                    VerticaCommand command = _conn.CreateCommand();
                    command.CommandText = "SELECT node_name FROM V_MONITOR.CURRENT_SESSION";
                    VerticaDataReader dr = command.ExecuteReader();
                    while (dr.Read()) {
                        Console.Write("Connect attempt #" + i + "... ");
                        Console.WriteLine("Connected to node " + dr[0]);
                    }
                    dr.Close();
                    _conn.Close();
                    Console.WriteLine("Disconnecting.\n");
                }
                catch(Exception e) {
                    Console.WriteLine(e.Message);
                }
            }
        }
    }
}

Running the above example produces the following output:

Status of load balancing policy on server: roundrobin

Connect attempt #1... Connected to node v_vmart_node0001
Disconnecting.

Connect attempt #2... Connected to node v_vmart_node0002
Disconnecting.

Connect attempt #3... Connected to node v_vmart_node0003
Disconnecting.

Connect attempt #4... Connected to node v_vmart_node0001
Disconnecting.

Hostname-based load balancing

You can also balance workloads by resolving a single hostname to multiple IP addresses. The ADO.NET client driver load balances by automatically resolving the hostname to one of the specified IP addresses at random.

For example, suppose the hostname verticahost.example.com has the following entries in C:\Windows\System32\drivers\etc\hosts:

192.0.2.0 verticahost.example.com
192.0.2.1 verticahost.example.com
192.0.2.2 verticahost.example.com

Specifying the hostname verticahost.example.com randomly resolves to one of the listed IP addresses.

5 - ADO.NET connection failover

If a client application attempts to connect to a host in the Vertica 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 Vertica Analytic 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.

The DNS method of failover centralizes the configuration client failover. As you add new nodes to your Vertica Analytic 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 Vertica Analytic 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 Vertica Analytic 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 Vertica Analytic 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 Vertica Analytic Database cluster. You then have all client applications use this host name to connect to Vertica Analytic 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 Vertica 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 resort to trying the backup hosts to establish a connection to Vertica.

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);
            }
        }
    }
}

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 a Vertica 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.

6 - Configuring log properties (ADO.Net)

Log properties for ADO.Net are configured differently than they are other client drivers. On the other client drivers, log properties can be configured as one of the connection properties. The ADO.Net driver user the VerticaLogProperties class to configure the properties.

VerticaLogProperties

VerticaLogProperties is a static class that allows you to set and get the log settings for the ADO.net driver. You can control the log level, log path, and log namespace using this class.

The log is created when the first connection is opened. Once the connection is opened, you cannot change the log path. It must be set prior to opening the connection. You can change the log level and log namespace at any time.

Setting log properties

Setting the log properties is done using the three methods in the VerticaLogProperties class. The three methods are:

• SetLogPath(String path, bool persist)

• SetLogNamespace(String lognamespace, bool persist)

• SetLogLevel(VerticaLogLevel loglevel, bool persist)

Each of the methods requires a boolean persist argument. When set to true, the persist argument causes the setting to be written to the client's Windows Registry, where it is used for all subsequent connections. If set to false, then the log property only applies to the current session.

SetLogPath

The SetLogPath method takes as its arguments a string containing the path to the log file and the persist argument. If the path string contains only a directory path, then the log file is created with the name vdp-driver-MM-dd_HH.mm.ss.log (where MM-dd_HH.mm.ss is the date and time the log was created). If the path ends in a filename, such as log.txt or log.log, then the log is created with that filename.

If SetLogPath is called with an empty string for the path argument, then the client executable's current directory is used as the log path.

If SetLogPath is not called and no registry entry exists for the log path, and you have called any of the other VerticaLogProperties methods, then the client executable's current directory is used as the log path.

When the persist argument is set to true, the path specified is copied to the registry verbatim. If no filename was specified, then the filename is not saved to the registry.

Example Usage:

            //set the log path
            string path = "C:\\log";
            VerticaLogProperties.SetLogPath(path, false);

SetLogNamespace

The SetLogNamespace method takes as its arguments a string containing the namespace to log and the persist argument. The namespace string to log can be one of the following:

• Vertica

• Vertica.Data.VerticaClient

• Vertica.Data.Internal.IO

• Vertica.Data.Internal.DataEngine

• Vertica.Data.Internal.Core

Namespaces can be truncated to include multiple child namespaces. For example, you can specify "Vertica.Data.Internal" to log for all of the Vertica.Data.Internal namespaces.

If a log namespace is not set, and no value is stored in the registry, then the "Vertica" namespace is used for logging.

Example Usage:

            //set namespace to log
            string lognamespace = "Vertica.Data.VerticaClient";
            VerticaLogProperties.SetLogNamespace(lognamespace, false);

SetLogLevel

The SetLogLevel method takes as its arguments a VerticaLogLevel type and the persist argument. The VerticaLogLevel argument can be one of:

• VerticaLogLevel.None

• VerticaLogLevel.Fatal

• VerticaLogLevel.Error

• VerticaLogLevel.Warning

• VerticaLogLevel.Info

• VerticaLogLevel.Debug

• VerticaLogLevel.Trace

If a log level is not set, and no value is stored in the registry, then VerticaLogLevel.None is used.

Example Usage:

            //set log level
            VerticaLogLevel level = VerticaLogLevel.Debug;
            VerticaLogProperties.SetLogLevel(level, false);

Getting log properties

You can get the log property values using the getters included in the VerticaLogProperties class. The properties are:

• LogPath

• LogNamespace

• LogLevel

Example Usage:

            //get current log settings
            string logpath = VerticaLogProperties.LogPath;
            VerticaLogLevel loglevel = VerticaLogProperties.LogLevel;
            string logns = VerticaLogProperties.LogNamespace;
            Console.WriteLine("Current Log Settings:");
            Console.WriteLine("Log Path: " + logpath);
            Console.WriteLine("Log Level: " + loglevel);
            Console.WriteLine("Log Namespace: " + logns);

Setting and getting log properties example

This complete example shows how to set and get log properties:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {

//configure connection properties

            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";

//get current log settings

            string logpath = VerticaLogProperties.LogPath;
            VerticaLogLevel loglevel = VerticaLogProperties.LogLevel;
            string logns = VerticaLogProperties.LogNamespace;
            Console.WriteLine("\nOld Log Settings:");
            Console.WriteLine("Log Path: " + logpath);
            Console.WriteLine("Log Level: " + loglevel);
            Console.WriteLine("Log Namespace: " + logns);

//set the log path

            string path = "C:\\log";
            VerticaLogProperties.SetLogPath(path, false);

//set log level

            VerticaLogLevel level = VerticaLogLevel.Debug;
            VerticaLogProperties.SetLogLevel(level, false);

//set namespace to log

            string lognamespace = "Vertica";
            VerticaLogProperties.SetLogNamespace(lognamespace, false);

//open the connection

            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();

//get new log settings

            logpath = VerticaLogProperties.LogPath;
            loglevel = VerticaLogProperties.LogLevel;
            logns = VerticaLogProperties.LogNamespace;
            Console.WriteLine("\nNew Log Settings:");
            Console.WriteLine("Log Path: " + logpath);
            Console.WriteLine("Log Level: " + loglevel);
            Console.WriteLine("Log Namespace: " + logns);

//close the connection

            _conn.Close();        }
    }
}

The example produces the following output:

Old Log Settings:
Log Path:
Log Level: None
Log Namespace:
New Log Settings:
Log Path: C:\log
Log Level: Debug
Log Namespace: Vertica