sqlserver AC Configuration

This article describes the configuration concepts and procedures to set up the SQL Server Monitoring (sqlserver) probe. To configure the probe, you must create a connection to the SQL Server database and a profile. The default checkpoints are automatically added in that profile. Profile checkpoints are predefined parameters that can be used to monitor the server performance or unwanted events. You can generate alarms and QoS using these checkpoints.
uimpga-ga
sqlserver_AC
This article describes the configuration concepts and procedures to set up the SQL Server Monitoring (sqlserver) probe. To configure the probe, you must create a connection to the SQL Server database and a profile. The default checkpoints are automatically added in that profile. Profile checkpoints are predefined parameters that can be used to monitor the server performance or unwanted events. You can generate alarms and QoS using these checkpoints.
 
This article is for probe versions 4.9 and later.
 
The following diagram outlines the process to configure the probe.
sqlserver_AC Configuration1
sqlserver_AC Configuration1
 
 
 
Contents
 
 
 
Verify Prerequisites
  • Verify that required hardware and software is available before you configure the probe.
  • Verify that the required drivers are available. You can install the drivers from http://www.microsoft.com/en-us/download/details.aspx?id=20065. The SQL Server driver name has been changed from SQLOLEDB to MSDASQL in version 4.30. 
Configure General Properties
You can configure the log, alarm, and QoS status details for the probe.
 
Follow these steps:
 
  1. Navigate to the 
    sqlserver
     node.
    The 
    Probe Information 
    section provides information about the probe name, probe version, start time of the probe, and the probe vendor. 
  2. Go to 
    General Configuration
     and set or modify the following details, as required:
    •  
      Suppress All Alarm
      : allows you to suppress all the alarms in the probe, such as checkpoint alarms, profile alarms, SQL timeout alarms, and startup clear alarms.
      Default: Not selected
    •  
      Clear Alarm on Restart
      : clears all alarms when the probe restarts.
      Default: Not selected
    •  
      Alarm severity filter
      : specifies a severity level filter on events to be considered as alarms. The probe checks many areas of the databases. Some events that are generated are key to the performance and availability of the database. For example, as a database administrator, you want to convey important events on to the operations center or help-desk, so that the event can trigger emails.
      Default: information
    •  
      Log Size
      : specifies the maximum size of the probe log file in kilobytes. When this size is reached, the existing contents are cleared.
      Default: 100
    •  
      Log Level:
       specifies the level of information that is written in the log file. You can select the following log levels:
      • 0 - Logs only severe information (default)
      • 1 - Logs error information
      • 2 - Logs warning information
      • 3 - Logs general information
      • 4 - Logs debugging information
      • 5 - Logs tracing/low-level debugging information
        : Log as little as possible during normal operation to minimize disk consumption, and increase the amount of detail when debugging.
    •  
      QoS V2 Compatibility: 
      allows you to
       
      insert QoS data of sqlserver version 2.x in the database.
      : This field is valid when you upgrading the probe from version 2.x to a higher version. For more information, refer to the 
      V2 QoS Compatibility Mode
       in sqlserver (SQL Server Monitoring) Release Notes.
Create a Connection
You can create a connection to the SQL Server database that you want to monitor.
 
Follow these steps:
 
  1. Click the 
    Options (icon)
     next to the 
    sqlserver 
    node and select 
    Create new connection
    .
  2. Complete the following details for the connection:
    •  
      Name
      : specifies a unique name for the connection.
    •  
      Description
      : specifies additional information about the connection.
    •  
      Authentication
      : specifies the authentication that the probe uses to connect to the database. The default
       
      authentication is SQL Server Authentication. You can select from the following options:
      •  
        SQL Server authentication
        : provide the username and password of the SQL Server database.
      •  
        Windows authentication
        : provide the username and password of the domain.
      •  
        Robot Service authentication
        : connects to the local SQL Server instance using the robot's login credentials, generally NT Authority\ System. Since the login is automatic, the username and password fields are disabled and the server name is set to 
        localhost
        .
    •  
      Encryption
      : enables you to encrypt the communication between the database and the server.
    •  
      Detect domain automatically
      : enables the automatic detection of the domain. This field is enabled only when the specified Authentication is 
      Windows authentication
      .
    •  
      User ID
      : specifies the authenticated user name. 
       Ensure that the specified 
      User ID
       has the required access privileges on the Microsoft SQL Server Database. For more information, see the 
      Access Prerequisites
       section in the sqlserver (SQL Server Monitoring) Release Notes.
    •  
      Password
      : defines the password of the specified 
      User ID
      .
    •  
      Server name
      : defines the server name of the database. If the server is not running on a standard port, then you specify the port with server name or IP address. For example, see the following screenshot:
      nonstandardport_sqlserver_AC.jpg  
    •  
      (From Version 5.0) Monitor Always On:
       enables the probe to monitor the Always On Availability Groups in the SQL Server database.
      Default: Not selected
       The Always On Availability Groups are supported from SQL Server 2012 or later.
    • (From Version 5.30) 
      TLS Enabled
      : enable the TLS 1.2 version for the probe.
  3. Click 
    Submit
    .
    The probe creates the 
    connection name
     node under the 
    Checkpoints
     node.
  4. To set up the additional details of the connection, navigate to the 
    connection name
     node.
  5. Complete the following information in 
    Connection Setup
     section:
    •  
      Retry attempts
      : specifies the number of attempts that the probe makes to connect when there is a failure. The value 0 means that only the initial connection is attempted.
      Default: 0
    •  
      Retry delay
      : specifies the time that the probe waits between two connection attempts.
      Default: 0
    •  
      Timeout
      : specifies how long the probe waits for an answer before it aborts the connection process.
      Default: 0
       Specify a higher 
      Timeout
       value, for example 10 seconds, if your robot has multiple networks hops to the monitored SQL Server.
    •  
      Timeout unit
      : specifies the unit for the timeout.
      Default: seconds
  6. Select 
    Actions > Test Connection
     in the 
    connection name
     node.
    If successful, it returns the instance name and the version number. If unsuccessful, the probe displays a failure message.
  7. Click 
    Save
    .
    The new connection is saved under the 
    Connections
     node in the navigation pane.
: To delete a connection, click the 
Options (icon)
 on the 
 
Connection-Connection Name
 
 node and select 
Delete Connection
.
Create a Profile
You can create a profile to monitor the required SQL Server database using the existing connections in the probe. 
 We recommend that you limit the number of profiles to less than 10 for an optimized GUI performance.
 
Follow these steps:
 
  1. Click the 
    Options (icon)
     next to the 
    connection name
     
     
    node.
  2. Click the 
    Create new profile
     option.
  3. Complete the following field information:
    •  
      Name
      : specifies the name of the profile.
    •  
      Connection
      : specifies the connection that is used by this profile. The connections must exist in the probe before creating a profile.
  4. Click 
    Submit
    .
  5. Navigate to the 
    profile name
     node and complete the following field details:
    •  
      Active
      : activates the profile on creation.
      Default: Not selected
    •  
      Description
      : specifies additional information about the profile.
    •  
      Heartbeat
      : specifies the interval, after which schedules of all profile checkpoints are tested and executed.
      Default: 5 
      : This number is a common denominator to all check interval values. The higher the value, the lower is the profile overhead.
    •  
      Heartbeat Unit
      : specifies the unit for the heartbeat.
      Default: sec
    •  
      Connection
      : specifies the connection that is used in the profile.
    •  
      Check Interval
      : specifies the interval, after which the profiles are scanned.
      Default: 15
      : Reduce this interval to generate alarms frequently. A shorter interval can also increase the system load.
    •  
      Check Interval Unit
      : specifies the unit for the selected interval.
      Default: min
    •  
      Clear message
      : specifies the message for the timeout clear alarm.
    •  
      SQL Timeout
      : specifies the SQL query timeout. Every checkpoint query runs asynchronously. If the query reaches the SQL timeout, the checkpoint processing is terminated. The probe processes the next checkpoint and generates an alarm.
      Default: 5
    •  
      Sql timeout unit
      : specifies the unit for the SQL Timeout.
      Default: min
    •  
      Message
      : specifies the message for the profile timeout alarm
    •  
      Profile timeout: 
      defines the maximum processing time for all checkpoints in the profile. If this timeout is reached, the interval processing is terminated. The probe waits for next heartbeat to evaluate any checkpoint schedules, and generates an alarm.
      Default: 15
    •  
      Profile Timeout unit
      : specifies the unit of profile timeout.
      Default: min
    •  
      Delay Threshold
      : specifies the timeout threshold for the delay alarm of the profile.
    •  
      Delay Threshold unit
      : specifies the unit for the delay threshold.
    •  
      Message
      : specifies the message that is used for threshold alarm.
    •  
      Delay Severity
      : specifies the severity of the alarm when the threshold delays the profile.
      Default: Major
    •  
      Timeout Severity
      : specifies the severity for timeout messages.
      Default: Major
    •  
      Alarm Source
      : overrides the source name of the alarm on the Unified Service Management.  If you do not specify a value, robot IP address is used.
    •  
      QoS Source
      : overrides the source name of the QoS on the Unified Service Management.
      (From Version 5.0)
       Type ${ in the field and select the 
      instance
       variable to use the 
      $instance
       variable to display as the alarm or QoS source. 
    •  
      Use FQDN As QoS Source
      : allows the QoS source to be fully qualified domain name (FQDN) instead of a simple host name.
      Default: Not selected
       CA does not recommend you to change the source fields after the initial configuration. If you change the QoS source later, multiple graphs are displayed on the USM Metrics view (one for every QoS source value). Also, we recommend you to keep the source identical for both alarm and QoS.
  6. Click 
    Save 
    to configure the profile.
    : To delete a profile, click the 
    Options (icon)
     on the 
     
    Profile Name
     
     node and select Delete profile.
Add Checkpoints to Profile
Profile checkpoints are predefined parameters that can be used to monitor the server performance or unwanted events. The global and default checkpoint settings are used, unless you modify the settings locally for your profile.
 Define a higher time value of the 
Check Interval
 and 
Profile Timeout
 fields depending on your database size, if your profile has a large number of activated checkpoints.
The 
Add profile checkpoint
 option allows you to add a static checkpoint to a specific profile and monitor the database.
:
  • Checkpoints "logic_fragment" and "scan_density" are not supported on SQL Server 2005. Use "av_fragmentation" instead.
  • The checkpoint "database_status" has been replaced by "database_state". Database status values from SQL Server 2000 (and lower) are mapped to database_state as defined in SQL Server 2005.
 
(From Version 5.0)
 The probe includes checkpoints to monitor different parameters of the Always On Availability Group in the SQL Server database. For example, you can monitor the AlwaysOn WSFC nodes state with the aag_cluster_members_state checkpoint. All these checkpoints remain disabled in a profile until the 
Monitor Always On
 feature is enabled for the connection that is specified for the profile. The probe also includes new checkpoints to monitor statistics of the wait checkpoints at every interval enabled in your profile. These checkpoints ensure that the database performance is optimal. For more information about the checkpoints, see sqlserver Metrics.
 The 
Monitor Always On
 feature is not available for the connections or profiles existing on probe versions 4.9 or earlier.
 
Follow these steps:
 
  1. Click the 
    Options
     
    (icon) 
    next to your <
    profile name>
     node and select 
    Add Profile Checkpoints
    .
  2. Select the checkpoints in the 
    Available
     list and move them to the 
    Selected
     list.  
  3. Click 
    Submit
    .
    The checkpoints are added to your profile.
Create a Custom Checkpoint
You can configure the probe to add a checkpoint to monitor the database events. 
 
Follow these steps:
 
  1. Click the 
    Options (icon)
     next to the 
    Checkpoints 
    node.
  2. Select 
    Create New Checkpoint
     and complete the following details:
    •  
      Name
      : specifies the name of the checkpoint.
    •  
      Connection Name
      : specifies the name of the connection.
    •  
      Query File
      : defines the query file name where you want the query to be stored.
    •  
      Query
      : defines the query for creating the checkpoint.
  3. Click 
    Submit
    .
    The new checkpoint is saved under the 
    Checkpoints
     node.  
  4. Navigate to the 
    Checkpoints
     node and complete the information as given in Configure Predefined Checkpoints.
 If a custom checkpoint is added to an existing monitoring profile, the Unified Management Portal (UMP) creates a Dynamic node in the Metric section. Further, it does not display the user-defined description and unit.
Configure Predefined Checkpoints
The probe allows you to customize the predefined or default checkpoints.
 
Follow these steps:
 
  1. Navigate to the 
    Checkpoints
     node.
  2. Complete the following details under the 
    General Config 
    section:
    •  
      Checkpoint name
      : specifies the name of the checkpoint. 
    •  
      Active
      : activates the checkpoint on creation.
      Default: Not selected
    •  
      Description
      : specifies additional information about the checkpoint.
    •  
      Check Interval
      : specifies the interval when the checkpoints are scanned.
    •  
      Check interval unit
      : specifies the unit of the selected Checkpoint Interval.
    •  
      Samples
      : specifies the number of samples to calculate an average value, which is compared with the specified alarm threshold. The probe generates an alarm when the average value of any sample breaches the threshold. The probe behavior for the following 
      Samples
       value is described as follows:
      • 0: Sampling is done based on the number of samples that are defined in the template. If no number is specified in the template, no sampling is done.
      • 1: No sampling is done.
      • 3: The probe performs the following:
        • uses the first sample value in the first interval 
        • uses the average of samples 1 and 2 in the second interval
        • uses the average of samples 1, 2, and 3 in the third interval
      Default: 1
    •  
      Clear Severity
      : specifies the severity of the clear alarm.
      Default: clear
    •  
      SQL Timeout
      : specifies the SQL query timeout. If the query reaches the SQL timeout, the checkpoint processing is terminated. The probe processes the next checkpoint and generates an alarm..
    •  
      SQL timeout unit
      : specifies the unit of the SQL timeout.
    •  
      Clear Message
      : specifies the message for the clear alarm.
    •  
      Send Alarm
      : specifies to send alarm information. This option is enabled by default. If this option is cleared, then only QOS information is shared.
    •  
      Use Exclude/Use Include
      : enables you to define list of exclude or include patterns to be monitored for a checkpoint. You can select from the following options:
      Default: Not selected
      •  
        Use Exclude: 
        adds the objects that you want to exclude in the monitoring list of some of the checkpoints. Go to the 
        Exclude
         section and click
         New
        . Add the exclude pattern and click 
        Save
        . The exclude pattern is added, which allows you to define objects that you do not want to monitor on the checkpoint.
      •  
        Use Include
        : adds the objects that you want to include in the monitoring list of some of the checkpoints. Go to the 
        Include
         section and click
         New
        .
         
        Add the include pattern and click 
        Save
        . The include pattern is added, which allows you to define objects that you want to monitor on the checkpoint.
        :
        • We recommend you to define objects that you want to exclude or include from monitoring, in large databases. If you do not limit the monitored objects, the probe might not be able to execute all the checkpoints within the profile timeout value. For example: tablespace_free, free_space checkpoints.
        • You can select this option for a custom checkpoint from version 4.9. In previous versions, this option is disabled after you create a custom checkpoint.
  3. To pick the rows as variables in an alarm message, click 
    New
     in the 
    Row Identifier
     section and specify the name of the row variable.
    If the query returns more than one row, the probe needs a unique identification for each row. The row identifier is used as part of the suppression key and QoS definition.
    : This section appears only for custom checkpoints.
  4. To pick the columns as variables in an alarm message, click 
    New
     in 
    Message Variable
     section and  complete the following field information:
    •  
      Name
      : defines the name of the variable.
    •  
      Data Type
      : specifies the data type of the variable of the new alarm message.
    •  
      Column Use
      : specifies the column use of the variable.
    : This section appears only for custom checkpoints.
  5. To configure the database settings, navigate to 
    Advance Database Settings
     section and  complete the following field information:
    •  
      Ignore Database Snapshots
      : allows you to ignore the database snapshots from monitoring that are read-only copy of some database.
      Default: Not selected
    •  
      Ignore Database Restoring State
      : allows you to ignore the databases from monitoring that are in restoring state.
      Default: Not selected
    • I
      gnore Database Created in Last (Days)
      : allows you to ignore the databases that are created in the mentioned days.
      Default: 1
      This section appears only for the backup_status checkpoint.
  6. Click 
    Save
    .
    The checkpoint is available for monitoring.
 To delete a checkpoint, click the 
Options (icon)
 on the 
 
Profile Name
 
 node and select 
Delete checkpoint
.
Configure QoS and Thresholds
You can add and delete the QoS definitions and thresholds in the probe.
 
Follow these steps:
 
  1. Navigate to 
    sqlserver > Checkpoints > 
    checkpoint name
     > Monitors 
    node.
  2. To define QoS, click 
    New
     in the 
    Quality of Service
     section.
  3. Set or modify the following details, as required:
    •  
      Name
      : specifies the name of the QoS
    •  
      Description
      : specifies additional information about the QoS. 
    •  
      Unit
      : defines the unit of the QoS.
    •  
      Abbreviation
      : defines the abbreviation for the QoS.
    •  
      Metric
      : specifies the metric of the QoS.
    •  
      Max value
      : specifies the maximum value for the QoS.
    •  
      Object
      : defines the object for the QoS.
     If a custom QoS is added to an existing monitoring profile, UMP creates a Custom node in the Metric section. Further, it does not display the user-defined description and unit.
  4. To define the thresholds, click 
    New
     in the 
    Threshold
     section.
  5. Set or modify the following details as required:
    •  
      Threshold object name
      : defines the name of the threshold object.
    •  
      Condition
      : specifies the condition of threshold breach.
    •  
      Threshold value
      : defines threshold values of the checkpoint.
    •  
      Severity
      : specifies the severity level of threshold breach.
    •  
      Message
      : defines the type of message to be issued when the probe exceeds threshold values.
    •  
      Clear Message
      : specifies the message name that is used for clear alarm message.
    •  
      Message text
      : defines the message text to be generated when the probe exceeds threshold values.
  6. Click 
    Save
    .
View Message Configurations
You can view the properties of alarm messages that are configured in the probe.
 
Follow these steps:
 
  1. Navigate to the 
    Message Configuration
     section of the 
    sqlserver
     node.
  2. Select an alarm message from the table to view the following properties:
    •  
      Name
      : identifies the name of the message.
    •  
      Text
      : identifies the message text.
    •  
      i18n_Token
      : identifies the predefined alarms that are fetched from the database.