sql_response AC Configuration

This article describes the configuration concepts and procedures to set up the sql_response probe. Configure this probe to monitor the connection to the SQL server database, by executing custom SQL queries. A profile is used to define these queries and also to configure the alarms and QoS.
uimpga-ga
sql_response_AC
This article describes the configuration concepts and procedures to set up the sql_response probe. Configure this probe to monitor the connection to the SQL server database, by executing custom SQL queries. A profile is used to define these queries and also to configure the alarms and QoS.
This article is for probe versions 1.6 or later.
 
The following diagram outlines the process to configure this probe.
Configuring sql_response_AC
Configuring sql_response_AC
 
Contents
 
 
 
2
 
 
Verify Prerequisites
Verify that required hardware and software is available before you configure the probe.
Configure General Properties
You can configure the log properties of the probe to define where and how to maintain the log information. You can also, configure the global properties of the profile, for example, when to perform the monitoring and the severity of alarms.
 
Follow these steps:
 
  1. Navigate to the
     sql_response
     node.
  2. Update the following fields under 
    General Configuration
    :
    •  
      Log File
      : defines the file where the logging information will be saved.
      Default: sql_response.log
    •  
      Log Level: 
      sets the level of details written to the log file.
      Log as little as possible during normal operation to minimize disk consumption, and increase the amount of detail when debugging.
    •  
      Log Size (KB): 
      indicates the maximum size of the log file.
    •  
      Interval (Value)
      : defines how often the probe should run the defined profiles.
      Default: 5
      This value is overwritten if you define 
      Run Interval
      .
    •  
      Interval (Units):
       specifies the unit for the interval value.
      Default: min
    •  
      COM error
      : specifies the severity level for the alarms issued when communication errors occur.
      Default: major
Create a Connection Profile
You can create a connection to the SQL server database to enable the monitoring process.
 
Follow these steps:
 
  1. Click the 
    Options
     (icon) beside the sql_response node in the navigation pane.
  2. Select 
    Add New Connection
    .
    The 
    Add New Connection
     dialog appears. 
  3. Enter the following information:
    •  
      Connection Name:
       specifies the name of the connection.
    •  
      Connection Type (OLEDB) Parameters:
       defines the database provider name.
      Default: Microsoft OLE DB Provider for SQL Server
      To find information on the ODBC connection, navigate to connection name, Connection Setup Information, Actions, Show DSN Info.
    •  
      Win/Domain Authorization: 
      allows you to use Windows integrated security for MS SQL Server to connect to the database. If you do not select this option, SQL Server authorization is used.
      Default: Not selected
    •  
      Initial Catalog: 
      specify the initial database of the data source used for the connection, depending on the database:
      • DB2: empty
      • Informix: empty
      • Oracle: empty
      • SQL Server: 
        database name
         
      • Sybase: d
        atabase name
         
    •  
      Data Source: 
      specify the data source used for the connection, Depending on the database:
      • DB2: 
        database name
         
      • Informix:  
      • Oracle: 
        NET Service name 
        or keep it empty
      • SQL Server: 
        database server
         
      • Sybase: 
        data source name
         
    • (If Win/Domain Authorization check box is NOT selected)
       User ID: 
      enter the 
      User ID
       to connect to the database.
    • (If Win/Domain Authorization check box is NOT selected) 
      Password: 
      enter the password of the specified user ID.
    •  
      Timeout (Value): 
      defines the time for which the probe will attempt to connect to the database.
      Default: 30
    •  
      Timeout (Units)
      : defines the unit for measuring the value of timeout.
      Default: sec
    •  
      Subsystem: 
      defines the alarm subsystem_id.
      The subsystem_id is required if you configure custom alarms and messages. Alarms are classified by their subsystem ID, identifying which part of the system the alarm relates to.
    •  
      Parameters: 
      specifies additional parameters for the connection. This is for advanced use only. Leave the field blank unless you have detailed knowledge about these parameters.
    •  
      TLS
      : select this option to enable TLS v1.2 communication with the database. This option is available only when you select 
      SQL Server Native Client 11.0
      Microsoft OLE DB Provider for ODBC Drivers
      , or 
      Microsoft OLE DB Driver for SQL Server
       as the Provider. For Oracle, DB2, and Informix configure the respective database client.
  4. Click 
    Submit.
    A success message dialog appears.
  5. Click
     Reload.
    The connection details are saved.
  6. Navigate to the 
    <Connection Name>
     node.
  7. Complete the following connection information:
    •  
      Connection Error Alarm: 
      defines the severity of the alarm when the connection fails.
    •  
      Connection Error Alarm Message: 
      specifies the alarm message that is used when the connection fails.
    •  
      Connection Established Alarm: 
      defines the severity of the clearing alarm when the connection is successful
       
    •  
      Connection Established Alarm Message: 
      specifies the message that is used when the connection is successful.
  8. Click on 
    Actions
     and select 
    Test Connection.
     
    If the test fails, check the log file for error messages.
    The profile for the connection is configured.
    If you do not want to use this connection, click 
    Options 
    icon on the connection and click on 
    Delete Connection
    .
Add a Query
You can add a query to the connection profile, which when executed monitors the database connection.
 
Follow these steps:
 
  1. Click 
    Options (icon)
     for the 
    <Connection Name>
     node.
  2. Click the 
    Add New Query 
    option.
  3. Enter the query name in 
    Add New Query
     dialog and click 
    Submit
    .
    A success message dialog appears.
  4. Click 
    Reload.
     
  5. Navigate to the
     <Query Name>
     node.
  6. Update the 
    General 
    information of the query:
    •  
      Active: 
      allows you to use this query for monitoring, on creation.
    •  
      Description: 
      defines the query description.
    •  
      Connection
      : specifies the connection
    •  
      Alarm source: 
      specifies an alternative to the default alarm source, which is the robotname where the probe is deployed.
    •  
      Run Interval (Value):
       specifies how often the SQL query should run. This value overrides the default value which is set on the 
      Setup
       tab.
      Reduce this interval to generate alarms frequently. A shorter interval can also increase the system load.
      Default: 5
    • Run Interval (Units):
       specifies the run interval unit.
      Default: min
    • Query Timeout (Value):
       select the time interval after which an alarm is generated when the SQL query fails to validate.
      Default: 5
      Reduce this interval to generate alarms frequently. A shorter interval can also increase the system load.
      The query timeout must not be set too short. If the query timeout exceeds and the provider does not support the Command Timeout function, the probe considers the database as unavailable and terminates the process. It can be set to a multiple of the monitoring interval. This function is supported with MDAC 2.6 or higher.
    •  
      Query Timeout (Units):
       defines the unit for measuring the 
      Query Timeout
       value.
      Default: min
    •  
      Timeout error:
       defines the severity level of the alarm that is generated when the SQL query fails to validate.
      Default: major
    •  
      Scheduling: 
      defines how to schedule the profile execution on specific date and time, based on the rules specified in 
      Schedules
      . Use this option, if you do NOT want to use the 
      Run interval
       time and want to refine the time interval of running the profile. For example, run your profile to monitor the SQL server only on Wednesday mornings at 8:00 A.M. The following options are available for the scheduling.
      •  
        rules: 
        profile is scheduled to run according to the rules described in 
        Schedules
        . (Default)
      •  
        exceptions: 
        profile is NOT scheduled to run according to the rules described in 
        Schedules
        .
        The rules and exceptions cannot be mixed in one profile.
  7. Under 
    SQL Query
    , define the cursor type as follows:
    •  
      Cursor: 
      allows you to select server or client to manage the cursor.
      •  
        Server: 
        allows you to create the cursor in the database server. This setting delivers realistic data about the database performance, but reduces performance when the net-traffic increases.
      •  
        Client: 
        allows you to transfer the record set to the client machine by ADO and manage the cursor there. This option provides better performance, but the results are buffered (depending on the ADO provider) and can hide actual database performance.
    •  
      Simple Query: 
      defines one-liner query to be executed.
      CA recommends that you follow these rules when you create a query:
      • To avoid excessive alarms, try to limit the number of rows that is returned by the query. If possible, use "SELECT a, b FROM table1 WHERE somedate < DATEADD(n,-10,GETDATE())".
      • Use queries that return one row (if possible). For example, SELECT count(*) as rows FROM table1.
      • Each row returned by a query results in one alarm and one or more QoS messages.
  8. Click on 
    Actions 
    and select 
    Test SQL Query
     to test if the query is valid.
    You can now configure the alarms and QoS for the probe.
    If you do not want to use this query, click 
    Options 
    icon on the query and click on 
    Delete Query
    .
Configure Alarms and QoS
To successfully monitor the database connection, configure alarms and QoS based on the following components:
  • Total response time
  • Total number of rows returned by the defined SQL query
  • Row value returned by the defined SQL query
You can configure high threshold, low threshold, and clear alarm messages. The low threshold can be used to generate a warning alarm and the high threshold can be used to generate an error alarm. For example, generate a high threshold alarm when the query response time exceeds 22 milliseconds.
 
Follow these steps:
 
  1. Navigate to the
     <Query Name>
     node.
  2. Under 
    No Record Returned
    , specify the alarm message (variables may be used) to be issued if no record was returned from the query, as follows:
    • Publish Alarms
      : allows you to generate the alarm messages.
      Default: Selected
    • Suppression key: 
      defines the parameter to be checked from the results of the defined query. It can clear the alarm if the query returns data the next time or issue an alarm if the query does not return any data (select 
      other
      ).
      Default: value
    • Severity
      : specifies the severity of the alarm message to be issued.
      Default: major
    • Send QoS as: 
      defines the type of QoS to be sent if the query returns no data.
      Default: null
    • Message: 
      specifies the message to be used when no data is returned from the query.
  3. Update the following information under 
    Query Response Time, Row count, 
    and 
    Value,
     as required.
    •  
      Publish Data: 
      allows you to generate QoS. For 
      Value, 
      you can select existing numeric QoS data from 
      Value QoS Alarm List
      . To do so, select an entry from this list and select the 
      Publish Data 
      check box.
    •  
      Publish Alarms
      : allows you to generate the alarm messages. For 
      Value, 
      you can select existing numeric alarms from 
      Value QoS Alarm List
       and non numeric alarms from  
      Value Alarm List
      . To do so, select an entry from this list and select the 
      Publish Alarms 
      check box.
      For more information about how to add alarm or QoS to monitor the row values returned by the SQL query, see Create QoS or Alarms.
    • (For 
      Query Response time 
      section) 
      Network: 
      specifies whether the network delay time is included or excluded from the response time calculation. The network delay is calculated by sending one or more pings.
      •  
        inclusive: 
        indicates that the network delay is included in the response time. (Default)
      •  
        exclusive
        : indicates that the network delay is NOT included in the response time.
    • (For 
      Query Response time
       section) 
      Pings
      : defines the number of pings to be used to find the approximate network delay.
      Default: 0
    • (For 
      Query Response time 
      section)
       Checked Value
      : specifies the type of threshold level for the connection response time, as follows:
      •  
        total: 
        includes all SQL phases from connect to close connection.
      •  
        connect
        : includes connection and database instance creation and open the connection for database communication.
      •  
        prepare
        : includes create database instance and open the recordset for processing. (Default)
      •  
        recordset
        : includes prepare and all fetches.
      •  
        fetch
        : includes the time per one fetch (average of all records).
    • (For
       Row count 
      section)
       Operator: 
      defines the operator to be used to check the number of rows returned against the threshold values. For example if the Operator is "<=" and the threshold value is 10 and the number of rows returned is 9, an alarm is sent for that threshold. If the next query returns 11 rows, a clear alarm (if configured) is sent.
      Default: >
    • (For 
      Value 
      section)
       Row key
      : defines one or more columns that make the unique identifier for every row, This option is used if the query returns more than one row to distinguish alarm and QoS messages issued by the profile for every row.
    • (For 
      Value 
      section)
       NULLs: 
      defines how to handle NULL values returned from the query, as follows:
      •  
        as_zero
        : If the query returns a NULL value, it is replaced by 0. (Default)
      •  
        ignore
        : If the query returns a NULL value, no checking occurs.
      •  
        alarm
        : If the query returns a NULL value, the probe issues an alarm (without further checking).
    •  
      High Threshold: 
      specifies the maximum 
      Value 
      (in milliseconds) that will be compared with the value returned from the SQL query. For example, if the response time exceeds this threshold value, an alarm is generated. The 
      Severity 
      of the alarm and 
      Message 
      is sent when this threshold value is breached.
    •  
      Low Threshold: 
      specifies the minimum 
      Value
       (in milliseconds) that will be compared with the value returned from the SQL query. For example, if the response time exceeds this threshold value, an alarm is generated. The 
      Severity
       of the alarm and 
      Message
       is sent when this threshold value is breached.
      In the 
      Query
       
      Response time
       and 
      Row count
       sections, you must specify a low threshold 
      Value
       when the following features are enabled:
      •  
        Alarm
         and 
        QoS
         in the Response time and Row count tabs
      • The 
        No Record Returned
         section in the <Query Name> node
      Specify 
      as the low threshold 
      Value
       in these sections if the Suppression key is set as 
      count
       in the No Record Returned section in the <Query Name> node. If you do not specify any value, the probe clears any alarm generated for no records.
    •  
      Clear Severity: 
      defines the 
      Severity
       of the alarm to clear or maintain history of the alarms. The 
      M
       
      essage
       is sent when the threshold value is not breached.
Create QoS or Alarms
This section is used to create QoS data or alarms when row values that are returned from the SQL queries are monitored. QoS are generated only for numeric queries. Alarms can be generated for both numeric and non numeric queries.
The lists are only visible when the query test has run successfully and you have saved the query.
Create Numeric QoS Alarms
This section is used to create numeric QoS data or alarms or both when row values that are returned from the SQL queries are monitored.
 
Follow these steps:
 
  1. Go to 
    Value QoS Alarm List
     
  2. Click on 
    New 
    and update the following information:
    • Publish Data: 
      allows you to generate QoS.
    • Publish Alarms
      : allows you to generate the alarm messages.
    • QoS Name: 
      defines the name that is used for the QoS database.
    • QoS Description
      : defines the description for the QoS database.
    • Unit
      : defines the long name for QoS data unit of measurement.
    • Abbreviation
      : Defines the short name for QoS data unit of measurement.
    • Metric
      : specifies the column name to be used as QoS value. The drop-down list is ffilled with values after the SQL query is executed.
    • Max value
      : defines maximum value the metric can have (if available).
    • Object
      : defines the name to describe the value in the QoS database.
    • High Value: 
      specifies the maximum 
      Value 
      (in milliseconds) that is compared with the value returned from the SQL query. For example, if the response time exceeds this threshold value, an alarm is generated. The 
      High Severity 
      of the alarm and 
      High Message 
      is sent when this threshold value is breached.
    •  
      Low Value: 
      specifies the minimum 
      Value
       (in milliseconds) that is compared with the value returned from the SQL query. For example, if the response time exceeds this threshold value, an alarm is generated. The 
      Low Severity
       of the alarm and 
      Low Message
       is sent when this threshold value is breached.
    • Clear Severity: 
      defines the severity of the alarm to clear or maintain history of the alarms. The 
      Clear M essage
       is sent when the threshold value is not breached.
The QoS alarms are created.
Create Non Numeric Alarms
This section is used to create non numeric alarms when row values that are returned from the SQL queries are monitored.
 
Follow these steps:
 
  1. Go to 
    Value Alarm List
     
  2. Click on 
    New 
    and update the following information:
    •  
      Publish Alarms
      : allows you to generate the alarm messages.
    •  
      Name:
       defines the name of the alarm.
    •  
      Comparison: 
      defines the type of comparison that checks the value returned from the SQL query. For example, to check the numeric value of the column, select Numeric. Consider the following points:
      • If character is selected, "=" and "!=" operators are valid.
      • If regular expression is selected, "in" operator is valid.
      • See the 
        Regular Expression Construct Rules
         section in the Logmon Hints and Examples article for various rules and constructs for creating regex and pattern matching.
        Regexp Constructs: All the patterns that are not used with ‘/’ are internally enclosed within ^ and $. For example, 
        CA
         is converted to 
        ^CA$
         and further regular expression processing is done considering rules for both 
        ^
         and 
        $
        . However, using * (e.g. 
        *C.A*
        ) provides another way to use regular expression.
        Default: numeric
    •  
      Operator: 
      defines the operator to use to check the number of rows returned against the threshold values. For example if the Operator is "<=" and the threshold value is 10 and the number of rows returned is 9, an alarm is sent for that threshold. If the next query returns 11 rows, a clear alarm (if configured) is sent.
    •  
      Column
      : specifies the column name. To populate this list, you must run your SQL query.
    •  
      High Value: 
      specifies the maximum 
      Value 
      (in milliseconds) that is compared with the value returned from the SQL query. For example, if the response time exceeds this threshold value, an alarm is generated. The 
      High Severity 
      of the alarm and 
      High Message 
      is sent when this threshold value is breached.
    •  
      Low Value: 
      specifies the minimum 
      Value
       (in milliseconds) that is compared with the value returned from the SQL query. For example, if the response time exceeds this threshold value, an alarm is generated. The 
      Low Severity
       of the alarm and 
      Low Message
       is sent when this threshold value is breached.
    •  
      Clear Severity: 
      defines the severity of the alarm to clear or maintain history of the alarms. The 
      Clear M essage
       is sent when the threshold value is not breached.
The alarm is created.
  • To edit an existing alarm or QoS data, select it from the list and modify the parameters.
  • If you do not want to use the alarm or QoS data, select it from the list and click
     Delete.
     
Alarm Thresholds
The alarm threshold options that are available can vary depending on the probe versions installed at the hub level. The alarm threshold settings to allow the probe to:
  • Send alarms when threshold criteria is met
  • Indicate to baseline_engine to compute baselines
See Configuring Alarm Thresholds for details.