The Quality Assurance ADO Monitor monitors a SQL Server (or supported data source) from the end-user perspective by using synthetic transactions to:

• Test login ability
• Perform a query
• Retrieve data
• Analyze results for correctness

The ADO - QA Monitor Wizard is designed to help you configure an ADO - QA Monitor with the least amount of initial input. There are a number of benefits to this approach:

1. Configuration is fast and easy, allowing you to get the Monitor up and running quickly.
2. ipMonitor automatically creates the SQL Statement using the information you provide.
3. The ADO - QA Wizard allows you to test all the parameters you enter along the way to make sure that the Monitor will work as expected immediately upon being enabled to go live in a production environment.

Top of page

The ADO - QA Monitor requires that you have the correct OLE DB Provider installed to provide access to the Database Type you wish to use. The following Database Types are supported:

• Microsoft SQL Server
• Sybase SQL Server
• IBM DB2
• IBM Informix
• PostgreSQL
• Oracle
• MySQL
• Other

Note: The "Other" database type allows you to manually configure the ADO Monitor to test many other data sources, such as: SAP DB, FrontBase, FoxPro and LDAP.

Providers have different implementation requirements and guidelines. We recommend consulting the documentation provided by the appropriate third-party vendors before proceeding to install OLE DB Providers.

For basic instructions regarding configuring the supported data base types outlined above, refer to the provider configuration articles available on the ipMonitor Support Portal.

Top of page

ipMonitor includes a Universal Data Link file that will launch the Windows Data Link Properties dialog box in order to test connections between the ipMonitor host computer and OLE DB data sources.

For example, the following procedure illustrates how to verify that the SQL Server OLE DB provider is properly installed on the ipMonitor host machine, and that connectivity to the SQL Server can be established:

1. Double-click the ipm7adotest.udl file located in the \ipMonitor7\ root directory.
2. Select the SQL Server OLE DB Provider from the Provider tab. If the required Provider does not appear in the list, it will need to be installed before ipMonitor will be able to connect.



3. From the Connection tab, select the SQL Server instance and enter the necessary login and database information. Click the Test Connection button. If the test is successful, a Test Connection succeeded message box will appear. Click the OK button to save the settings.

   

In the example shown above, Use Windows NT Integrated security is selected to log on to a Microsoft SQL Server. In other words:

• The SQL Server is configured to use Windows NT authentication to allow access to its data
• The Account used to log in to the ipMonitor host machine and launch the ipm7adotest.udl file must have the necessary privileges to execute the remote procedure call and authenticate to the SQL server

Top of page

The following example illustrates the configuration process for creating an ADO - QA Monitor to monitor the Microsoft SQL Server Database type using the ADO - QA Wizard.

1. Log in to the ipMonitor Administration interface.
2. Click the Monitors menu option, then select Add a Monitor.
3. Select the ADO-QA (SQL Query) Monitor from the Windows NT/2000 based category and then click the Continue button.

4. Select a Database Type from the list provided.

Only correctly installed OLE DB Providers will be selectable for Monitor configuration. OLE DB Providers that have not been installed on the ipMonitor host machine will appear under the Unavailable Database Types heading. If necessary, contact your vendor to acquire the correct OLE DB Provider for your Database Type and install it before continuing.

Selecting the Other database type allows you to manually configure the ADO - QA Monitor to test many other data sources such as SAP DB, FrontBase, FoxPro and LDAP. For more information on configuring the ADO - QA Monitor without using the ADO - QA Wizard, refer to the section of this document titled Manually Configuring the ADO - QA Wizard.

Top of page

Server Address
Specify the Server Address of the SQL Server Database you want to monitor.

To Connect to a Named Instance of Microsoft SQL Server:
Data Source = InstanceName

Use TCP/IP versus Named Pipes
When this option is enabled, ipMonitor will use TCP/IP rather than Named Pipes.

To connect to a Microsoft SQL Server database via an IP Address:
Data Source = xxx.xxx.xxx.xxx,1433

Where:

• xxx.xxx.xxx.xxx is the IP address of the database server.
• 1433 is the default port number for SQL Server (IP Address and Port are separated by a comma).

Use Data Encryption
When this option is enabled, ipMonitor will encrypt the data transmitted between the ipMonitor host machine and the database being queried.

Specify a Microsoft SQL Server Instance
To connect to a Named Instance of Microsoft SQL Server, enable this option, then use the following format to enter the relevant Instance Name:

Data Source = InstanceName

Top of page

Select a Credential for Monitoring
Depending on the data source configured for the ADO Monitor, it is likely that a form of authentication to the data source will be required to connect and/or log in.

Typically, you will need to create a Credential and assign it to the ADO - QA Monitor in order to impersonate the account information required to access and query the data source.

If a Credential for Monitoring is not assigned, ipMonitor will use the privileges of the current Windows Account assigned to the ipMonitor Service.

To select a Credential:

• Click the Select... button to pop up the Credentials for Monitoring dialog
• Select an existing Credential from the ADO category
• To create a new Credential, click the New Credential button to start the Wizard

Top of page

The Database List contains Name and Size information for databases located on the server.

Note: Depending on the Database type being monitored, this step may not be displayed.

Top of page

The Tables List contains Name, Owner and Type information for tables located within the Database being queried.

Show only User Tables
When this option is selected, only User-created Tables will be displayed in the Tables List.

Show both User and System Tables
When this option is selected, User-created Tables and Systems Tables will be displayed in the Tables List.

Top of page

Automatically generate an SQL Statement from selected Columns
When this option is selected, a list of Columns located within the table specified in the previous step will be displayed. Information such as Data Type (including length of the column) and whether the column is allowed to contain "NULL" values is also made available. Simply place a checkmark beside the Column Name(s) of the column(s) you want to query further.

Manually supply an SQL Statement
When this option is selected, you can input a manual SQL query. Simply enter a SQL statement you've created within the textbox provided.

Preview
Clicking the Preview button allows you to view the data contained within the columns selected. This information is displayed within a pop-up window.

Stretch Table
Clicking the Stretch Table button will expand the preview table to fit all content selected. If you've chosen to view a large number of columns, some of the content may be truncated unless you expand the resulting preview table.

Top of page

SQL Statement
The SQL query created by the ADO - QA Wizard based on the Table and Column(s) you've selected is displayed for final verification. This SQL statement will be issued to the Database Server when the Monitor is enabled. If necessary, you can make changes to the query directly within the SQL Statement field.

Maximum Rows to Retrieve
This value represents the maximum number of rows that the query will be permitted to return to the ADO - QA Monitor for analysis. The default value is set to 300.

Verifying the number of Retrieved Rows

Examine the number of Retrieved Rows based on a numeric equation
If this option is selected, you must choose an operator and number that will be used to test the number of rows returned by the query. Supported numeric tests are as follows:

Pass if the number of rows is:

• < Less than "X"
• > Greater than "X"
• <= Less than or equal to "X"
• >= Greater than or equal to "X"
• == Equal to "X"
• != Not equal to "X"

Verifying the content of Retrieved Rows

Examine Row Content to perform textual or numeric analysis
This option can be used to perform textual or numeric analysis on the data contained in the Column you specified.

When the query result set returns more than 1 Row:

• Each Row in the result set is examined sequentially from first to last
• All configured tests are performed on the Column you specify
• If any condition you specify is met the test passes

Examine Column
When specifying the column to examine, note that column 1 is the first column. Counting is 1-based, not 0-based.

The data in the specified column can be either:

• A string
• A number

Column Will
Supported numeric tests are as follows:

Pass if the number of columns is:

• < Less than "X"
• > Greater than "X"
• <= Less than or equal to "X"
• >= Greater than or equal to "X"
• == Equal to "X"
• != Not equal to "X"

Several text comparison methods are available including Regular Expression and string matches.

Pass if the content of the column adheres to a:

• RegEx match - Enter a Regular Expression to match
• RegEx non-match - Enter a Regular Expression not to match
• Substring search - Search for a case sensitive substring
• == Exact match
• != Non-match

Preview
Clicking the Preview button allows you to view the data contained within the columns selected. For easy identification, rows highlighted in red indicate a "fail" scenario. Within the preview window, clicking the Stretch Table button will expand the table to fit all content selected. If you've chosen to view a large number of columns, some of the content may be truncated unless you expand the resulting preview table.

Top of page

Enter a concise, descriptive name for the new Monitor. The Monitor Name will be displayed in the Monitors List, Monitor Status, History Events and Logs pages. Names may not be greater than 64 characters. Since ipMonitor does not use the name field to identify the Monitor internally, Monitor Names can be changed at any time without data loss.

Create Monitor Enabled
Once the Monitor is created, it immediately starts querying the Database with the configuration options selected. This option is enabled by default.

Store Monitor Statistics for Recent Activity and Historical Reports
ipMonitor will immediately begin to record test results, which are then used to generate Recent Activity and Historical Reports. This option is disabled by default.

Create
Click the Create button to exit the wizard and access the new Monitor in edit mode. You can make any final modifications to the Monitor in this mode, including setting Timing and Notification parameters.

Once you're satisfied with the configuration settings, click the OK button at the bottom of the edit page. The new Monitor will be displayed within the Monitors List, and can be accessed for further configuration adjustments at any time.

Top of page

The Test Parameters dialog box is used to specify the parameters the ADO - QA Monitor will use to open a connection to the data source.

The example discussed here relates to configuring the ADO - QA Monitor to test connectivity to a Microsoft SQL Server 2000 Database Server. Notes are provided for other OLE DB Providers as appropriate.

Database Type
Select a Database Type from the ADO - QA Monitor's list of supported OLE DB providers.

Credential for Monitoring
Depending on the data source configured for the ADO - QA Monitor, it is likely that a form of authentication will be required to connect and/or login.

Typically, a Credential will be created and assigned to the ADO - QA Monitor in order to impersonate the account information required to access and query the data source.

If a Credential for Monitoring is not assigned, ipMonitor will use the privileges of the current Windows Account assigned to the ipMonitor Service.

To select a Credential:

• Click the Select... button to pop up the Credentials for Monitoring dialog
• Select an existing Credential from the Windows category
• To create a new Credential, click the New Credential button to start the Wizard

Run this test from an external process
Enabling this option allows ipMonitor to continue monitoring the database even if a temporary OLE DB connection problem occurs.

In the example shown below, we are configuring the ADO QA Monitor to a test Microsoft SQL Server 2000 Database Server. The following authentication methods are possible:

SQL Authentication

Using SQL Authentication also known "Mixed Mode":

• Provider = sqloledb
• Data Source = ServerName
• Initial Catalog = DatabaseName

A Credential for Monitoring will need to be created and assigned to provide the User Name and Password required to authenticate. Only select the following option within the Usage Restrictions section of the Credential's settings:

• May be used with ADO (ActiveX Data Objects)

Integrated Windows Authentication

Using Integrated Windows Authentication also known as a "Trusted Connection":

• Provider = sqloledb
• Data Source = ServerName
• Initial Catalog = DatabaseName
• Integrated Security = SSPI

Use the AND... button to specify the additional Integrated Security = SSPI parameter.

A Credential for Monitoring is required if the ipMonitor Service account does not have sufficient rights to connect to the database server. Only select the following option within Usage Restrictions section of Credential's settings:

• May be used with Windows Impersonation for use with RPC

Note: For specific configuration details regarding configuration of a Credential to be used with the ADO - QA Monitor refer to Credentials for Monitoring :: ADO

Note: For more information about Credentials refer to Credentials Overview.

Additional Connection Types
The ADO - QA Monitor supports both Named Instance and TCP/IP connection options.

Named Instance

To connect to a Named Instance:

• Provider = sqloledb
• Data Source = ServerName\InstanceName
• Initial Catalog = DatabaseName

If the Named Instance uses SQL Authentication, a Credential for Monitoring will need to be created. If the Named Instance uses Integrated Windows Authentication, a Credential may be required as noted above.

Use the AND... button to specify the Integrated Security = SSPI parameter.

Note: In order to connect to a SQL Server 2000 Named Instance, the ipMonitor host machine must have MDAC 2.6 (or greater) installed.

TCP/IP Connection to SQL Server 2000

To connect to SQL Server 2000 via an IP Address:

• Provider = sqloledb
• Data Source = xxx.xxx.xxx.xxx,1433
• Initial Catalog = DatabaseName
• Network Library = DBMSSOCN

Parameters to Specify:

• xxx.xxx.xxx.xxx is IP address of the database server.
• 1433 is the default port number for SQL Server (IP Address and Port are separated by a comma).
• Use the AND... button to enter Network Library = DBMSSOCN indicating TCP/IP should be used instead of Named Pipes.
• Use the AND... button to enter Encrypt = yes indicating encryption will be used.

SQL Statement
Enter the query statement that will be issued to the Database Server or Data Source.

Note: Do not end the SQL statement with a ";" semicolon. ipMonitor automatically adds the semicolon for you.

Locking Method
Use the Locking Method drop-down selector to specify the locking mechanism that will be placed on the query statement issued to the Database Server or Data Source. By default, the Locking Method is set to "Optimistic".

The following options are available:

• Optimistic - Allows simultaneous editing of a record; locking it only when an update is attempted.
• Batch Optimistic - This option is required only when updating records in batches rather than individually.
• Pessimistic - Immediately locks a record when the retrieval process is initiated.
• Read Only - Prevents data from ever being modified.

Analysis of Test Results

The Analysis of Test Results section is used to validate query results. It controls the number of Rows that will be retrieved and the type of analysis the Monitor will perform on the result set.

Success can be determined strictly by:

• Retrieving up to a Maximum of "x" number of Rows

Further analysis is possible by:

• Examining Row Count
• Examining Row Content

Retrieve a Maximum of "x" Rows
The Retrieve Maximum Rows parameter is used to control the maximum number of rows that the query will be permitted to return to the ADO - QA Monitor for analysis. This helps to:

• Control impact on the SQL server or data source being monitored
• Reduce network bandwidth consumed
• Reduce the amount of processing ipMonitor is required to do

Examine the Row Count
The Examine Row Count option can be used to validate results based on the numeric equation you configure.

Number of Rows Retrieved Must Be
Select an operator and enter a number that will be used to test the number of rows returned by the query. Supported numeric tests:

• < Less than
• > Greater than
• <= Less than or equal to
• >= Greater than or equal to
• == Equal to
• != Not equal to

Examine the Row Content
The Examine Row Content option can be used to perform textual or numeric analysis on the data contained in the Column you specify.

When the query result set returns more than 1 Row:

• Each Row in the result set is examined sequentially from first to last
• All configured tests are performed on the Column you specify
• If any condition you specify is met the test passes

Examine Column Number
When specifying the Column Number to examine, note that column 1 is the first column. Counting is 1-based, not 0-based.

As
The data in the specified column will be treated either as:

• Text
• Numeric

Column Will
This equation determines success if the test passes.

Supported numeric tests:

• < Less than
• > Greater than
• <= Less than or equal to
• >= Greater than or equal to
• == Equal to
• != Not equal to

Several text comparison methods are available including Regular Expression and string matches:

• RegEx match - Enter a Regular Expression to match
• RegEx non-match - Enter a Regular Expression not to match
• Substring search - Search for a case sensitive substring
• == Exact match
• != Non-match

Note: ipMonitor includes a RegEx Wizard to help create Regular Expressions.

The AND button and the OR button can be used to increase the flexibility of the test. For example, you could test that:

• The value is greater than 30000
• AND less than 40000
• OR not equal 0

Preview
Click the Preview button to test the Analysis of Test Results settings against the Test Parameters configured. If you make changes to your configuration, simply click the Preview button again to retest the configured parameters.

Top of page

When the Monitor is in an Up state, test results are reported as shown in the example below:

rtt - Round-Trip Time. This value indicates the time it took the test packet to reach the monitored resource and return a response to ipMonitor. Round-trip time is measured in milliseconds (ms).

rows dl - The total number of rows returned to the ADO - QA Monitor for analysis.

When the Monitor is in a Warn, Down, or Lost state, the Last Result field indicates the problem encountered. Different Monitor types generate specific Error Codes in accordance with the technical capabilities of the Monitor. Refer to the Error Codes section of this document for details.

Top of page

Note1: The ADO - QA Monitor requires that you have MDAC 2.6 or greater installed on the ipMonitor host computer. MDAC 2.5 and earlier do not support Named Instances, which are used when more than one instance of SQL Server is running on a machine.

Note2: For information on settings common to all Monitor types, such as Identification, Timing, Notification Control and Recovery Parameters, refer to General Monitor Settings.

Note3: The ADO Monitor Wizard allows you to configure ADO Monitors quickly and easily. However, if you'd prefer greater control over the process, you can Clone an existing ADO Monitor and make any required configuration changes manually.

Top of page

The following error codes are generated by the ADO - QA Monitor. Error codes are displayed in Real-time and Historical Reports, and can be added to Alerts using Tokens.

Message	Details
Configuration information for this item is missing some required fields	A required element(s) for Monitor connectivity or testing was not supplied to ipMonitor.
The remote device rejected the request due to a client error	The server was unable to process the request because of a client-side error.
The remote device timed out before sending a response	The resource being monitored did not respond within the maximum allowable time.
Login credentials are incorrect	The Credentials supplied to ipMonitor were invalid.
Unable to connect to the remote device	The specified address to the connection-based service is unavailable.
Access rights are insufficient	ipMonitor was unable to process the request because of insufficient permissions.
There were errors with either issuing the query string or its execution	The server failed to process the query due to an error with the query string or an error was encountered when executing the query.
An error occurred while retrieving a database row	The result set could not be read.
The specified column does not exist, is not available, or is the wrong data type	A column was referenced in the Analysis of Test Results section that does not exist, was not included in the result set or the column data type does not match the Monitor configuration.
A row was retrieved with a column that did not match the specified pattern	A row in the result set contained data that resulted in a FAIL condition.
The remote device sent less data than expected	The result set returned by the server contained fewer rows / columns than was defined in the Monitor configuration.
The remote device sent more data than expected	The server sent more data than expected for this protocol. This is usually due to an error in the software being monitored.
The device returned a different quantity of data than was expected	The server returned a result set size that was different than was defined in the Monitor configuration.
The device returned the exact quantity of data expected	The server returned a result set that matches the criteria defined in the Monitor configuration.

Top of page