Environmental Data Logging Guidelines

    Introduction
    Logging to MS Access
    Logging to MS SQL or any other OLE-DB/ADO compliant database
    Migrating to MS SQL
    Troubleshooting

Introduction

Environmental Data (temperatures, humidity, wetness) can be logged in three different ways:

To an MS Access database (default);
To an MS SQL database (or any other ADO compliant database);
To an ASCII text file.

When logging is enabled, environmental data is written to an MS Access database called environmental.mdb by default. This database contains three tables:

Temperatures - for logging temperatures;
Humidity - for logging humidity;
Wetness - for logging wetness information.

So, basically you do not need to configure anything for basic logging. When logging is enabled, Network Monitor will write its environmental data to the environmental.mdb database.

This document provides detailed information about logging, shows how to setup logging for other databases and provides tips and tricks to troubleshoot logging.

Logging to MS Access

ActiveXperts Network Monitor is shipped with an environmental logging database called Environmental.mdb. This Access 2000 compatible database file is located in the <install-dir>\server\logs\ directory. When you switch on logging for a Sensatronics device, all logging will be stored in this database. MDAC 2.5 or higher is required on the server where ActiveXperts Network Monitor is running.

You can refer to an Access database in two ways:

Using an ADO Connection String (recommended);
Using a direct database file.

A connection string is a string version of the initialization properties needed to connect to an ADO compliant database and enables you to easily store connection information. It's recommended to use a connection string. When using a direct database file, ActiveXperts Network Monitor will translate the settings to a connection string internally.
Example connection string for MS Access logging:

DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\Program Files\ActiveXperts\Network Monitor\Server\Logs\Environmental.mdb

Logging to MS SQL or any other OLE-DB/ADO compliant database

By default, environmental data are logged to an MS Access database. However, if you already have an MS SQL database in your network, you may want to benefit of the power and flexibility of MS SQL and store temperatures, humidity and wetness information in an MS SQL database.
To make use of MS SQL, you need to do two things:

Migrate the current Environmental.mdb Access database to MS SQL;
Change the Connection String in the Logging Environmental Data dialog of the ActiveXperts Network Monitor Manager.

Migrating the MS Access database is to MS SQL is very simple, thanks to MS SQL's Import Wizard. The next section explains how to migrate to MS SQL.

After migration to MS SQL, you must provide a valid connection string, containing the configuration settings of the new MS SQL database. An MS SQL connection string has the following syntax:

DRIVER={SQL Server}; Server=<server hostname>; Database=<database name>; UID=<loginname>; PWD=<password>

Where UID and PWD are optional: if login is not required, you can remove UID and PWD from the connection string.

Example connection strings:

DRIVER={SQL Server}; Server=pluto; Database=environmental; UID=sa; PWD=mypassword
DRIVER={SQL Server}; Server=mssql01.mydomain.dom; Database=environmental; UID=sa
DRIVER={SQL Server}; Server=192.168.1.104; Database=environmental

Migrating to MS SQL

Migrating the Logging database from MS Access to MS SQL is made easy thanks to MS SQL's Import Wizard. By providing the Environmental.mdb file to the Import Wizard of MS SQL, you will have MS SQL logging running within minutes.

The following steps explain how to move from MS Access logging to MS SQL logging:


	Step 1 - Choose the Import and Export Data program from the Start-menu		Step 2 - The Data Transformation Services Import/Export Wizard application is launched. Click Next to continue.


	Step 3 - Set the Data Source field to MS Access. Now, the File name field will be enabled. Enter the a valid location where the Environmental.mdb is located. The file is located on the server where ActiveXperts Network Monitor is installed. If you want, you can copy the Environmental.mdb file first to the SQL server. The file is needed for its data structure. Click Next to continue.		Step 4 - Choose a desination database. Click on the Database selection box at the bottom of the dialog and choose New. The Create Database dialog will popup. Provide a name for the new database. In the above dialog, we called this new database Environmental. Click Next to continue.


	Step 5 - Choose a destination. Select Microsoft OLE DB Provider for SQL Server as the destination (this is already selected by default), and Select the new Environmental database in the Database selection box. Click Next to continue.		Step 6 - Copy Tables. Start copying tables by clicking on the Next button.


	Step 7 - Select source tables and views. Leave all fields default and click on Next.		Step 8 - Save, schedule and replicate package. Leave all fields default and click on Next.


	Step 9 - Complete the Wizard. Click on Finish to start the Import.		Step 10 - The Import is finished now. The only thing that is left, is to change the design of the tables in the Environmental database: the primary key of the Temperatures, Humidity and Wetness tables must be set to autonumbering. You can use the Enterprise Manager to make that change. Select the Enterprise Manager from the Start Menu to launch it.


	Step 11 - Select the Environmental database, then select the Temperatures table and go to design mode of the table by selecting the 'Design View' menu from the context popup menu (right-click on the table). Now, select the numID field and set the Identity to Yes. Close all dialogs now (save the changes you made), Repeat these steps for the Humidity and Wetness tables.		Step 12 - In the Network Monitor Manager application, change the connection strings for all checks that must log to the SQL database. To configure SQL logging, open the Temperature/Humidity/Wetness check, click on the Logging Settings button and change the Connection string field. Be sure that you include the name of the SQL server in the connection string. For example, if your SQL server is called SQL01 and the logonname of the database is sa with password sa1, the following string would be a valid connection string: DRIVER={SQL Server}; Server=SQL01; Database=environmental; UID=sa; PWD=sa1.

Troubleshooting

Logging to an ADO compliant database (including MS Access and MS SQL) is very powerful. However, when logging is not working, it's usually quite difficult to troubleshoot.
ActiveXperts provides a trace log when logging to an MS Access, MS SQL or any other ADO compliant database. Using the trace log, you can see what's happening under the hood of the engine; it will make it easier to troubleshoot a logging problem. The trace log provides you with errors returned by the database components of the operating system, and explanations of the errors.
Especially when migrating from one database to another - for instance from MS Access to MS SQL - the trace log may be a very welcome tool!

By default, tracing is disabled. You can enable tracing in the following way:

Open the registry editor on the server where the ActiveXperts Network Monitor service is running, either by starting REGEDIT.EXE or REGEDT32.EXE;
Open the HK_LOCAL_MACHINE\Software\ActiveXperts\Network Monitor\Trace key;
Double-click the Logfile_AdoEnvironmental value;
Enter a filename in the 'Value Data' field. The filename can be a non-existing file.

After setting the registry value, your registry should look like this:

You must restart the ActiveXperts Network Monitor service after you've changed the Logfile_AdoEnvironmental registry setting to apply the new setting to the Network Monitor Engine.
It's recommended to disable tracing after troubleshooting, because the trace log will slow down the performance of the Network Monitor Engine. Just enter an empty string in the Logfile_AdoEnvironmental registry value to disable tracing.