Knowledge Base Home

Call Support


Search the Knowledge Base

Skip to end of metadata
Go to start of metadata

Environment

Netmail Archive 5.x

Synopsis

By default, Netmail Archive ships with PostgreSQL as the database that provides consolidated logging and job reporting for all archive nodes.  In some cases, customers may wish to use other databases such as MS SQL for improved performance, disaster recovery or enhanced management capabilities. In larger environments with multiple Archive nodes, a dedicated database server is recommended. This article will explain how to configure Netmail Archive to work with MS SQL server instead of PostgreSQL.

Solution

Creating the Netmail Database

If Netmail Archive is not yet installed or if an existing Netmail Archive installation will be reconfigured to use MS SQL Server instead of PostgreSQL, the Netmail database (and its tables) should first be created on the MS SQL Server as follows:

Locate the Netmail SQL folder which contains the required files:

  • If Netmail Archive is not yet installed, obtain the Netmail Archive installation package. Open the folder containing the Netmail Archive installation files and navigate to Netmail Archive\program files\Messaging Architects\SQL

  • For an existing installation of Netmail Archive, navigate to <drive letter>\Program Files (x86)\Messaging Architects\SQL.

 

The SQL folder contains an application called gwopenDbCreator and various scripts for creating various databases. This application is generally used for database creation with PostgreSQL. However, since ODBC drivers must be installed before running gwopenDbCreator, use the SQL Script Processor instead (in the MS SQL Administration console) to create an SQL database (call it Netmail).  Once the database exists, the Netmail database tables may be created.

 

Creating the Netmail Database Tables

Once the Netmail database exists, go the SQL directory in the Netmail installation files and create the tables by opening the gwopen.mssql.sql script for editing.

Modify the script so that all <XML> tags are removed and save the changes. Run the script and it should create five Netmail tables: 

  • accountsummary
  • error
  • jobhistory
  • trace
  • warning

 

Create a Dedicated Database User and Assign Rights

It is recommended that a dedicated database user be created and assigned to the netmail database. Since ODBC strings are stored in plain text, it is not advisable to use an existing service or administrative account.

Once this dedicated user exists, assign the user DBO rights to the recently created netmail database through the Rights and Permissions Management settings on the MS SQL server.

 

Create an ODBC Data Source Connector to the MS SQL server

1. Install the Microsoft Native Client software which (for Window Server 2008 R2)  is part of the Microsoft® SQL Server® 2008 R2 SP1 Feature Pack, available at: http://www.microsoft.com/en-us/download/details.aspx?id=26728 (download the appropriate software for your particular OS version and database).

 

2. Create an ODBC Data Source on the server that will become the Netmail Archive Master node:

 

  • Open the ODBC Connection settings in Administrative Tools, and click Set up data sources (ODBC):

 

  • On the User DSN tab of the configuration window, click the Add button to view available drivers for the new ODBC data source; select the SQL Server driver from the Create New Data Source popup, and take note of the driver version as it will be required later in the configuration. After the driver version is noted, Click the Finish button:

 

  • Assign a data source name (DSN) as well as a description to the connector, and from the pull down list in the Server field, select the IP Address or FQDN of the MS SQL server that the data source will connect to:

 

  • Click the Finish button to create the data source.

 

Install Netmail Archive

During the Netmail Archive installation process, when the Connection Page is reached, manually modify the ODBC Connection String as follows (an example is provided below):    

Driver={SQL Driver Version as obtained in Step 2 above};Server=SQL Server IP Address or Name/Cluster  Name;Port=PortNumber;Database=databasename;Uid=Dedicated DB User;Pwd=password;

   

Example:  

Driver={6.01.7601.17514};Server=10.20.30.40/CLSTR1;Port=1462;Database=netmail;Uid=Netmailadmin;pwd=test123;  

Click NEXT, and if an error is generated, check the syntax, disable validation, complete the installation of Netmail Archive. After Netmail Archive is installed, use the next section of this article (reconfiguring an existing installation) to troubleshoot.

 

Reconfiguring an Existing Netmail Archive Installation to use MS SQL Server

If Netmail Archive is already installed, configuring the system to use MS SQL server is somewhat simpler. This section is also used to troubleshoot failed ODBC connections.

 

Log in to the Netmail WebAdministration console, and navigate to Archive > Clusters > clustername > Configuration tab. 

In the Default Log Settings section, edit the Logging Server Connection String as explained in Step 2 above (and shown in the screenshot below). 

Click the Test button and a message indicating Success should be returned (as long as the string is correct).  If not, check the syntax and settings.

 

Notes


Help us improve!
Is this article helpful?
Is it well written?
Is the content complete?