Configuring FreeTDS

Learn about how to use FreeTDS and the external database configurations known to work with Oracle Communications Unified Assurance.

FreeTDS allows components and rules files to connect to databases other than MySQL. You can use it with the Event Database Aggregator, Event Generic Database Connector, Metric Database Collector, or other applications that connect to a database to retrieve data.

Although you can install the FreeTDS application with Unified Assurance, it is not a Unified Assurance component. Oracle does not create or maintain FreeTDS.

Installing the vendorFreeTDS-app Package

Installing the vendorFreeTDS-app package is optional. You can use FreeTDS to connect to an external database without installing any additional packages. To use FreeTDS, you must create a freetds.conf file that contains database connection information in the $A1BASEDIR/etc directory on every server that will use FreeTDS.

The optional vendorFreeTDS-app package includes a sample freetds.conf file, containing reference information and an example configuration, and a T-SQL implementation that you can use to test your configuration.

To install the optional vendorFreeTDS-app package, run the following command:

$A1BASEDIR/bin/Package install vendorFreeTDS-app

Note:

If you install the vendorFreeTDS-app package, the next time you update or upgrade Unified Assurance, the update process will update the FreeTDS package and overwrite any changes you made in the freetds.conf with the default sample file. To preserve your customizations, back up your customized freetds.conf file before updating and restore it after updating.

If you do not install the package, and instead create freetds.conf manually, the update process does not overwrite freetds.conf, because there is no FreeTDS package to update.

Adding FreeTDS Configurations

  1. If you did not install the vendorFreeTDS-app package, create a file called freetds.conf in the $A1BASEDIR/etc directory.

  2. Add configurations in freetds.conf. Known FreeTDS Configurations provides some known settings, but you may need specific settings for different databases. Each [<data_source_name>] heading must be unique within freetds.conf. Do not create duplicates.

  3. Reproduce freetds.conf on all servers that will use FreeTDS.

  4. Optionally, if you installed the vendorFreeTDS-app package, back up freetds.conf to a safe location. Keeping a backup lets you preserve your customizations the next time you update or upgrade Unified Assurance.

  5. In the UI, update any relevant Unified Assurance components with the new configuration settings:

    1. From the main navigation menu, select Configuration, then Broker Control, and then Services.

    2. Select the relevant component, such as Event Database Aggregator, Event Generic Database Connector, or Metric Database Collector.

    3. Under Configuration, add or set the following configurations, replacing the placeholders as appropriate for your database:

      • DSN: dbi:Sybase:server=<data_source_name>;database=<data_source_name>

      • Password: <database_password>

      • Username: <database_username>

    4. Remove the following configurations:

      • DatabaseID

      • SourceSchemaName

    Note:

    Instead of updating the default service, you can clone it to create a custom service. In this case, you may want to use a custom log file, and create custom rules files and SelectSQL files. Update the service configurations to point to these custom files instead of the generic ones.

  6. Update any relevant rules files with the new configuration settings:

    1. Under Configuration in the component editor, click Edit Rules.

      The relevant base.rules file opens in a new tab.

    2. Find and select any rules files that need to use the new configurations.

    3. Edit the file to add the user name, password, and DBI->connect method, replacing the placeholders as appropriate. For example:

      my $UserName  = '<database_username>'; 
my $Password  = '<database_password>'; 
my $DBH       = DBI->connect('dbi:Sybase:server=<data_source_name>', $UserName, $Password, {PrintError=>1}) or die "unable to connect to server " . $DBI::errstr; 
my $Query     = '<SQL_query>'; 
my $STH       = $DBH->prepare($Query) or die "prepare failed for query $Query\n"; 
$STH->execute() or die "unable to execute query $Query :: error $DBI::errstr"; 
while (my $row = $STH->fetchrow_hashref()) { 
    <processing_code> 
}

    4. Click Submit, enter a commit message, and then click OK.

Testing FreeTDS

If you installed the vendorFreeTDS-app package, you can use T-SQL to test your connection.

  1. Enter the following command:

    $A1BASEDIR/vendor/freetds/bin/tsql -S <data_source_name> -U <database_username> -P <database_password>

    FreeTDS connects to the database, prints locale and character set information, and displays a command prompt.

  2. Enter the following test query at the prompt:

    SELECT Name from sys.databases;
GO

    The query returns the names of any configured databases.

  3. Disconnect by entering exit.

Known FreeTDS Configurations

These are the configuration settings for known databases. You must find and test the connection settings for any other databases.

Microsoft SQL Server 2000

To connect to a Microsoft SQL 2000 server, add the following to freetds.conf, replacing <database_server> with your database server host:

[MyServer2k]
    host        = <database_server>
    port        = 1433
    tds version = 8.0

Microsoft SQL 2005

To connect to a Microsoft SQL 2005 server, add the following to freetds.conf, replacing the placeholders as appropriate:

[<data_source_name>]
    host        = <database_server>
    instance    = <instance_name>
    port        = <port>
    tds version = 7.0

Depending on your database configuration, you may not need the instance setting.

For example, to connect to a SolarWinds database, which runs on a Microsoft SQL 2005 Server, use the following values:

Microsoft SQL Server 6.x Configuration

To connect to a Microsoft SQL 6.x server, add the following to freetds.conf, replacing <database_server> with your database server host:

[MyServer65]
    host        = <database_server>
    port        = 1433
    tds version = 4.2

Microsoft SQL Server 7.x

To connect to a Microsoft SQL 7.x server, add the following to freetds.conf, replacing <database_server> with your database server host:

[MyServer70]
    host        = <database_server>
    port        = 1433
    tds version = 7.0

Nimbus

To connect to a Nimbus server, add the following to freetds.conf, replacing <database_server> with your database server host:

[NimBUS]
    host        = <database_server>
    port        = 1433
    tds version = 8.0