sqlserver (SQL Server Monitoring) Release Notes

Contents
uimpga-ga
sqlserver_RN
The SQL Server Monitoring (sqlserver) probe constantly monitors the internal performance and space allocation of SQL Server databases. The probe can also send this monitoring information to the UIM availability manager for the appropriate alert notification. The monitoring information is based on the checkpoints that you can select and schedule, as required.
 
Contents
 
 
 
2
 
 
Revision History
This section describes the history of revisions for this probe.
Support case(s) may not be viewable to all customers.
 
Version
 
 
Description
 
 
State
 
 
Date
 
5.42
 
What's New:
 
  • Added Robot Service authentication as an authentication type for local SQL Server instances.
  • Added the 
    Send Alarm
     option which can be enabled independent of the checkpoint.
  • (April 2019) The sqlserver MCS (version 5.42 onwards) has been updated to address the common vulnerabilities and exposures by updating the jackson-databind libraries. For more information and CVE numbers, see Addressing Jackson Vulnerabilities.
 
Fixed Defects:
 
  • Fixed an issue where the probe was crashing frequently due to 
    a memory access violation. 
    Support case number: 
     
     
    01192217
     
  • Fixed an issue where Status tab for log file sizes and free space percentages in the the sqlserver probe and the SQL DB server were displaying
     different values. 
    Support case number: 
     
     
    01158019
     
GA
January 2019
5.41
 
Fixed Defects:
 
  • The probe was unable to connect to the SQL database when TLS 1.0 was disabled. 
    Support case number: 01124963
     
  • The probe was unable to connect to the MSSQL database
    . Support case number:
     
    01137055
     
Beta
August 2018
5.40
 
Whats’s New:
 
  • Added support for SQL Server 2017.
  • Added a new field
     Connection 
    (IM GUI) to remove the requirement of entering credentials again while adding custom checkpoints. For more information, see the 
    (Optional) Create Custom Checkpoint
     section
     
    in
     
     sqlserver IM Configuration.
  • New MCS template (sqlserver_mcs_template 5.30) with:
    • Enhanced profiles that enable you to configure metrics, baselines, alarm thresholds, alarms - including Time Over Threshold alarms - and custom alarm and close alarm messages, all within a single MCS profile.
  • (August 2018) Added support for monitoring SQL Server 2017 on RHEL 7.x x86 64-bit.
 
Fixed Defect:
 
  • The probe crashed and did not start after maximum retries. 
    Support case number: 00920071
     
GA
June 2018
5.30
 
What's New:
 
  • Added support for TLS 1.2. The probe requires the SQL Server Native Client 2012 or ADO.net 4.6 framework. For more information about how to enable TLS 1.2 support and the compatible client and server versions, see TLS 1.2 support for Microsoft SQL Server.
  • Added support for AES-128 encryption that is required for FIPS compliance from the Admin Console and Infrastructure Management interface. For more information about enabling FIPS encryption, see Enable FIPS encryption.
GA
November 2017
5.20
 
What's New:
 
  • Added support for:
    • AES-128 encryption that is required for FIPS compliance, only through the IM interface.
    • Monitor Windows systems where FIPS encryption is enabled. Before you deploy the probe version 5.20 on your Infrastructure Manager (IM), you must enable the FIPS encryption on the system where the probe is configured.
       You cannot configure the probe from any Admin Console (AC) interface on a FIPS encryption enabled environment.
CR
March 2017
5.10
 
What's New:
 
  •  The probe supports monitoring within an IPv6 environment.
  • Added support for SQL Server 2016.
  • Enhanced the aag_listener_state checkpoint. For more information, see the 
    Probe Generates False Alarms for aag_listener_state Checkpoint
     section in sqlserver Troubleshooting.
  • Added support to map checkpoint alarms to the database instance name. For more information, see 
    Known Issues and Workarounds
    .
 
Fixed Defects:
 
  •  
    The probe generated incorrect alerts for the probe backup checkpoints. 
    Support case number 499988
     
     
  • (On Admin Console GUI) After launching the probe, it automatically deactivated the 
    login_count
    buf_cachehit_ratio
    server_cpu
    check_dbalive
     checkpoints. 
    Support case number 363810
     
  • The probe generated an application eventlog error in the destination node whenever a shared resource migrated between the nodes of a two-node Windows cluster. 
    Support case number 326191
     
  • The probe displayed incorrect unit of 
    QoS_SQLSERVER_LOGFILE_SIZE
     in the Performance Report. 
    Support case number 246565
     
GA
September 2016
5.00
 
What's New:
 
  • Added support to monitor the Always On Availability Groups in SQL Server 2012, and 2014.
  • Added checkpoints to monitor the statistics of the wait checkpoints that are enabled in your profile.
 
Fixed Defects:
 
  • The probe successfully connected to the SQL Server database even when incorrect credentials were provided. 
    Support case number 270048
     
  • The probe returned a closed state for a command object when the query execution command is executed. 
    Support case number 276257
     
  • The probe now generates QoS value for log_size and used variables in the database_size checkpoint. 
    Support case number 381334
     
  • The probe was unable to connect to localhost or remote connections if the probe was installed on Windows Server 2008. 
    Support case number 412841
     
GA
July 2016
4.95
 
What's New:
 
  • Added support to unlock the database when the probe retrieves monitoring information. For more information, see sqlserver Troubleshooting.
 
Fixed Defects:
 
  • Updated the Release Notes for the required settings for a user without db_owner and sysadmin permissions. 
    Support case number 289881
     
  • The probe generated corrupt data for the QoS_SQLSERVER_USER_CPU metric, when monitoring multiple targets. 
    Support case number 292771
     
  • The probe displayed incorrect unit for the allocated variable in the alarm message for the 
    logfile_usage
     checkpoint. 
    Support case number 319212
     
GA
April 2016
4.94
 
Fixed Defects:
 
  • Probe was not generating QoS Messages at the specified poll interval. 
    Support case number 246108
     
  • Probe was not displaying the value in Bytes when lock_memory, connection_memory, optimizer_memory, sqlcache_memory, and total_memory checkpoints were executed. 
    Support case number 246026
     
GA
January 2016
4.93
 
Fixed Defects:
 
  • Probe was reporting a connection timeout in a time lesser than specified in the checkpoint interval. 
    Salesforce case
     
    00154897
     
  • Probe was displaying an error message while executing agent_job_failure checkpoint using Windows authentication. 
    Salesforce case 00155101
     
GA
October 2015
4.92
 
Fixed Defects:
 
  • Fixed a defect where the template checkpoint was overriding the static checkpoint. 
    Salesforce case
     
    00165905
     
  • Fixed a defect where the QoS was not always coming through Use FQDN As QoS Source. 
    Salesforce case
     
    00144129
     
  • Fixed a defect where the remote probe was reporting a connection timeout before the scheduled checkpoint interval. 
    Salesforce case 00154897
     
  • Added user setting information for SQL Server 2014. 
    Salesforce case
     
    00162883
     
July 2015
4.91
 
Fixed Defects:
 
  • Fixed a defect where the Use Excludes check box is disabled when a new custom checkpoint is created. 
    Salesforce case 00151079
     
  • Fixed a defect where the user_cpu checkpoint returns the CPU usage value greater than 100 percent. 
    Salesforce case 00147822
     
December 2014
4.90
 
What's New:
 
  • Added support for SQL Server 2014.
 
Fixed Defects:
 
  • Fixed a defect where the connection timeout value was always set to 2 seconds even after assigning a different value. 
    Salesforce case 00147043
     
  • Fixed a defect where the check_dbalive_2 default message is enclosed in quotes.
     Salesforce case 00151089
     
  • Fixed a defect where the probe did not start if the profile name length exceeded 60 characters. 
    Salesforce case 00148591
     
December 2014
4.84
 
Fixed Defects:
  • On creating a threshold for alarms from 
    Status
     tab, the object name was not appearing as expected.
  • During unauthorized query changes for the custom checkpoints, the alarm was generated and was repeating the message again and again. This defect has been fixed.
  • The alert message is generated once and the checkpoint is deactivated as expected.
January 2014
4.81
Fixed: Garbled data coming in alarm message for the user_waits checkpoint.
March 2013
4.80
  • Added features in user_waits for blocking length and the include, exclude feature in exclude of user_waits.
  • Added checkpoint logfile_usage_with_avail_disk and category_name for jobs in checkpoint long_jobs.
  • Added feature in backup_status for ignoring databases for no of days, restoring databases and database snapshots.
  • Added feature to clear profile level alarms.
  • Added feature in long_jobs for active jobs, their name, job run time, and name of category.
  • Fixed: Errors regarding checkpoint logfile_usage and logfile_size.
  • Fixed: Multiple conf_sqlserver_4.40.exe are created while testing sqlserver probe.
  • Fixed: Changes getting applied on click cancel button also.
  • Fixed: Detail of one custom checkpoint is displayed on another custom checkpoint.
  • Fixed: Alarms that are related to float values.
  • Fixed: av_fragmentation checkpoint reporting NULL table values.
  • Fixed: Probe to show Fully Qualified Domain Name as the QoS source.
March 2013
4.72
Fixed a defect where the check_interval of the checkpoint was not working.
January 2013
4.71
Added checkpoint logfile_usage_with_avail_disk and category_name for jobs in checkpoint long_jobs.
January 2013
4.71
Updated the mirror state values in the hints section of mirror_state checkpoint.
December 2012
4.70
  • Added a feature in custom checkpoints which allows thresholds to be defined for multiple columns.
  • Added functionality to set up a schedule for each alarm in the custom and built-in checkpoints.
  • Added functionality to set up key specific alarms in custom checkpoints.
  • Added new checkpoint(fg_freeSpace_with_avail_disk) which monitors free space in filegroups after considering the available disk size.
  • Fixed check_dbalive checkpoint to send value "0" instead of "NULL" as QoS on connection failure.
  • Fixed an issue of incorrect calculation for fg_free_space checkpoint.
  • Fixed an issue that is related to negative values for the checkpoints fg_free_space and free_space.
  • Fixed an issue in custom checkpoints where NULL QoS with invalid keys were generated for any database- related errors.
  • Fixed an SOC issue of CM Authentication Failure.
December 2012
4.6
  • Fixed an issue where the "Windows Authentication" method lets you access the server with or without providing a valid user account
  • Fixed an issue where the "Windows Authentication" method on local system is connecting using system account rather than the domain account configure in the connection.
  • Added a feature "suppress all alarms" so that all the alarms are suppressed.
  • Added feature, a monitoring profile does not run concurrently and the delay alarm is raised whenever the profile run is delayed.
  • Fixed an issue where the profile shows template checkpoints even though the group is selected.
  • Fixed the issue with the logfile_usage checkpoint which was reporting incorrect values.
  • Fixed an issue where the probe was crashing if "detect domain automatically" is selected on a machine which is not on domain.
September 2012
4.41
Fixed an issue where the backup_status checkpoint was reporting wrong value.
June 2012
4.40
  • Added a new checkpoint suspect_pages for reporting if suspect pages are logged for databases.
  • Added a new checkpoint agent_job_failure for reporting failed agent jobs within a defined interval.
  • Added new checkpoints ls_primary_status, ls_secondary_status, ls_primary_time_since_last_backup, ls_secondary_time_since_last_copy, ls_secondary_time_since_last_restore and ls_secondary_last_restored_latency checkpoints for monitoring Log Shipping in SQLServer 2005 and above.
  • Added a callback that is able to specify a wildcard or a regex in profile_name value to fetch active profiles.
  • Fixed an issue where the active_connection_ratio checkpoint was not working.
  • Fixed an issue where checkpoint schedule was running for an extra minute.
June 2012
4.30
  • Fixed an issue where the probe was failing to pick up no. of samples(overridden) correctly for static checkpoints.
  • Fixed a logging issue where the probe was incorrectly logging sqlserver password in plain text.
  • Fixed an issue where sometimes the probe was failing to return any rows for custom checkpoint queries.
  • Fixed QoS V2 compatibility issue, earlier the probe was not able to send the QoS based on the V2 QoS specification
  • Fixed an issue in manually signed stored procedure feature that is related to permissions.
  • Fixed an issue where the probe was incorrectly converting metric units(KB/MB/GB) for some checkpoints
  • Fixed an issue where the probe was not able to return any rows for complex custom checkpoint queries.
  • Fixed an issue in logfile_size and logfile_usage checkpoints where the checkpoints were failing when any database is in the middle of recovery. The probe now skips the databases which are being recovered until the recovery is complete and database is online.
March 2012
4.22
  • Fixed Misspelling in description of lock_requests checkpoint
  • Fixed Size reporting for large databases.
March 2012
4.21
Fixed SOC Issues.
December 2011
4.20
  • SOC Support Added.
  • Added support for the signed store procedure for standard and custom checkpoints queries.
  • Probe can be run in standard and in sign mode.
  • Added new checkpoints, mirror_state, mirror_witness_server, and mirror_sqlinstance for monitoring Database Mirroring state, status of witness server and status of sqlserver instance hosting mirroring database.
  • Fixed an issue where sqlusr_cpu store procedure is not deleted after executing queries for SQL Server 2000.
  • Modified QoS_key value for user_cpu checkpoints for avoiding large amount of QoS.
  • Fixed an issue of the subsystem ID field where subsystem ID shows wrong value.
  • Fixed an issue where the long_jobs checkpoint does not send any alarms.
  • Fixed an issue where the logic_fragment checkpoint gives Lock request timeout error.
  • Fixed Handle leak issue.
  • Added support for configuring unit as minutes, hours and days in backup_status, transaction_backup_status and differential_backup_status checkpoints.
  • Added a new error alarm message that is sent on checkpoint query execution failure.
August 2011
4.11
  • Added support for internationalization.
  • Added support for reading alarm tokens from cfg.
April 2011
4.01
  • Fixed division by zero errors for Logfile_Usage and Logfile_Size checkpoints.
  • Added interval_value variable in custom checkpoints, so QoS can be sent on interval_value.
  • Updated QoS definition of logfile_size, that is,. removed QoS_max.
September 2010
4.00
  • Added a new checkpoint logfile_size for reporting database log file size in MB.
  • Initial implementation of the sqlserver probe based on V4 database framework.
  • Fixed defects in probe and GUI
  • Added support for extended NIS database information.
  • Implemented support for alarms.cfg.
  • Added a new checkpoint blocked_users for calculating blocked user connections
  • Added support for authentication on an untrusted domain
  • Fixed an issue in the backup_status checkpoint where it was incorrectly reporting logfile_usage QoS
  • Fixed a crash in the GUI configurator for editing message variables
  • Fixed the white-space issue in the GUI configurator
  • Added support for includes functionality in the probe. This feature works same as the exclude feature
  • Fixed an issue in the fg_free_space checkpoint where the probe reported incorrect values
  • Added support for reporting QoS values for long_queries checkpoint
  • Added a new checkpoint active_connection_ratio for reporting active connection ratio
  • Added two new checkpoints transaction_backup_status and differential_backup_status for reporting transaction and differential backup status
  • Fixed an issue in GUI configurator where the "Alarm Severity filter" field on the setup tab was editable, now the field is made non-editable
  • Added support for creating custom checkpoints for reporting per-second metric values
  • Added support for configuring separate SQL timeouts for checkpoints
  • Added 64-bit support
  • Updated cfx file for message variables in the server_startup checkpoint.
September 2010
3.14
Fixed security token leak by closing the security tokens when they are not required.
August 2010
3.13
For custom checkpoints, the query password was not always saved properly. Fixed the query password encryption in GUI.
Note
: If any custom checkpoints are deactivated by the probe, those checkpoints must be deleted from the GUI and then re-added in the probe.
September 2009
3.11
SQL Native Client 2008 support.
January 2009
3.07
  • Query for checkpoint free_connections corrected.
  • Threshold migration for check_dbalive corrected.
  • V2 QoS compatibility modus
December 2008
3.05
  • Problem with crashes in a multithreaded environment fixed.
  • Query database_size for SQL Server 2000 corrected.
  • Several other queries for SQL Server 2000 corrected.
  • Memory leak problem fixed.
October 2008
3.03
Problem with an empty password solved
September 2008
3.02
Windows authorization support corrected.
Queries logfile_usage and fg_free_space corrected
August 2008
3.00
  • Multithreading - every profile runs in a separate thread.
  • Scheduling - every checkpoint can have its own schedule.
  • Multiple thresholds - every checkpoint can have more than one threshold.
  • Message pool - messages can be customized and reused among checkpoints.
  • Connection retry - for every connection there can be multiple attempts defined, before the probe raises an alarm.
  • Resizable GUI windows - all windows with lists are resizable.
  • Multiple QoS per checkpoint
  • Exclude lists
  • # of samples for alarming
  • Auto-update (GUI)
  • Customer-created checkpoints
  • New checkpoints: long_queries, long_jobs, backup_status
  • Fixed problem with occasional "probe may be hanging" message
  • Fixed problem with database names containing "-" or other special character
  • Support for case-sensitive database added
  • "No response" parameter added into GUI
May 2008
2.14
  • Fixed problem with database names containing "-" or other special character
  • Support for case-sensitive database added
  • "No response" parameter added into GUI
June 2007
2.12
  • Added library to enable support for SQL Server 2005.
  • Integer overflow in free space calculation fixed.
  • Hard-coded "oracle.log" removed.
  • Long domain instance name possible.
November 2006
2.10
  • New checkpoint database_status
  • Database name added to "locked_users" message
April 2006
2.09
  • New checkpoint alloc_space
  • buf_cachehit_ratio corrected for SP3
  • Domain user-id support for Windows security
  • Changed severity for "Database report generation time..." alarm (default "critical" now)
November 2005
Access Prerequisites
For SQL Server versions 2005, 2008, and 2012, 2014, 2016 set VIEW SERVER STATE permission on master database. Also, map the user for the following databases:
  • master
  • model
  • msdb
  • ReportServer
  • ReportServerTempDB
  • tempdb
Usually, the default database roles are used to grant access to the users. However, you can edit and grant different roles and permissions to different users. User mapping is done to GRANT, REVOKE, and DENY permissions for the user in the SQL Server.
 
Follow these steps:
 
  1. Right-click on the table and go to 
    Properties
     
    >
     
    Permissions Tab
    .
  2. Select 
    Add
     and browse for the user to grant permission.
  3. Click 
    OK
    .
  4. Select the permission type under the 
    Grant
     column.
    User is mapped for the required permission. User mapping is required for the following tables:
    • master.sys.databases
    • master.dbo.sysperfinfo
    • msdb.dbo.sysjobsteps
    • msdb.dbo.sysjobs
    • msdb.dbo.syscategories
    • msdb.dbo.log_shipping_monitor_secondary
    • msdb.dbo.log_shipping_monitor_primary
    • msdb.dbo.sysjobhistory
    • .sys.database_files
    • .sys.partitions
    • .sys.allocation_units
    • .sys.internal_tables
    • .sys.filegroups
 By default, the listed database tables are present. If you add a user without the db_owner and sysadmin permissions, then, you must also add the database tables that are associated with that user. Adding associated database tables enable you to generate alarms for all the databases including the default ones.
For Windows authentication, perform the following:
  • ensure that the user has access to the system hosting the monitored SQL Server.
  • provide access rights to
     Niscache 
    folder of the file system on which the CA UIM robot is installed.
  • ensure that the user has local logon rights on the system where the sqlserver probe is installed.
 
(From version 5.00)
 Grant the following permission to a non-SA user to access AlwaysOn tables in the database:
USE master;
GRANT VIEW DEFINITION ON AVAILABILITY GROUP::<group name> TO <username>;
GO
Supported Versions
The sqlserver probe supports the following SQL Server versions:
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012 (32 and 64 bit) including AlwaysOn Availability Groups support
  • (From version 4.90) SQL Server 2014 (32 and 64-bit) including AlwaysOn Availability Groups support
  • (From version 5.10) SQL Server 2016 (32 and 64-bit) including AlwaysOn Availability Groups support
  • (From version 5.31) SQL Server 2017 (32 and 64-bit) including AlwaysOn Availability Groups support
From April 2016 onward, Microsoft has discontinued the support for SQL Server 2005 version. Therefore, all enhancements that are done for version 5.0 or later are not supported for SQL Server 2005.
Probe Specific Hardware Requirements
The sqlserver probe must be installed on systems with the following minimum resources:
  • Memory: 2-4 GB of RAM. The OOB configuration requires 256 MB of RAM.
  • CPU: 3-GHz dual-core processor, 32-bit, or 64-bit
Probe Specific Software Requirements
The sqlserver probe requires the following software environment:
  • CA Unified Infrastructure Management 8.1 or later
  • Robot 7.62 or later (recommended)
  • (to enable FIPS encryption) Bus (Robot) version 7.80
  • Java JRE 6 or later (required for Admin Console)
  •  
    (From version 5.10)
     IPv6
  • MDAC 2.5 or higher, and ADO provider for database connection
  •  
    (From version 5.00)
     ci_defn_pack 1.27 or later (required to view data on Unified Management Portal (UMP))
  •  
    (From version 5.10)
     wasp_language_pack 8.50 or later (required to view alarm messages on UMP)
  • Probe Provisioning Manager (ppm) 3.47 or later is required for the probe Administration Console.
  • Ensure that the compatible client and server versions are available before you enable TLS 1.2.
  • Ensure that the system where database server is installed is updated with required patches according to SQL Server version (2008r2, 2012, 2014). For more information, see TLS 1.2 support for Microsoft SQL Server.
  • For both local and remote monitoring, Microsoft SQL Server 2012 Native Client must be installed on the system where probe is deployed. For more information, see Microsoft® SQL Server® 2012 Native Client - QFE.
(Version 5.20) Probe Specific Changes After Upgrade
The probe supports AES-128 bit encryption and decryption. To enable this feature in your Infrastructure Manager (IM), set the 
NIM_PROBE_CRYPTO_MODE
 environment variable to one of the following values in the  probe:
  •  
    TWOFISH
     (default)
  •  
    AES_128_COMPAT
     
  • If you do not set any value of the environment variable, then the probe uses 
    TWOFISH
     encryption and decryption, by default.
  • Restart the 
    Nimsoft Robot Watcher
     service after you set the environment variable.
  • If you provide incorrect values for the environment variable, the probe fails to start. CA does not recommend you to change this value after you set it once.
Upgrade Considerations
The probe has the following considerations:
  •  
    (On version 5.00)
     When you upgrade the probe from earlier versions, the probe might not connect to the database. This loss of connectivity can happen because the 
    User ID
     specified while creating a connection does not have the required privileges in the database.
Installation Considerations
The probe has the following considerations:
  • Install the sqlserver probe on any robot to monitor a remote or local (recommended) instance of Microsoft SQL Server database.
     Ensure that the probe and the monitored database are in the same domain for a successful connection.
  • Deploy the following probes on the same robot as the sqlserver probe to view monitoring information of your database server on the associated Unified Dashboard.
    • cdm: allows you to view the performance data of your database server. When monitoring remote databases, the cdm probe retrieves performance data of the system where sqlserver is deployed, not the database server.
    • processes: allows you to view the performance data of the sqlserver process. Create a profile in the 
      processes
       probe to monitor the sqlserver process, 
      sqlservr.exe
      . For more information about creating a profile, see processes (Process Monitoring).
       The processes probe cannot retrieve the process performance information of remote database servers.
  • If you have configured the probe using Infrastructure Management (IM) and want to switch to Admin Console (AC), then delete all instances of the probe and deploy it again. Also, create separate connections and profiles from AC. You must configure the probe either from AC or IM.
  • To copy AC configuration to another robot, copy the .cfg file from probe directory and use it on the robot where the probe resides.
Known Issues and Workarounds
This section contains the known issues and workarounds for the probe.
  • The probe configuration for both the Infrastructure Manager (IM) GUI and Admin Console (AC) GUI is separate. For example, any profile that is created in the IM GUI is not available on the AC GUI and must be recreated.
  • The signed mode feature does not work while creating a profile. Do not use it.
The Admin Console version has the following more limitations:
  • If a checkpoint has multiple QoS and you activate or deactivate one QoS, the other QoS of the checkpoint are also activated or deactivated. 
  • If a checkpoint has multiple alarms and you activate or deactivate one alarm, the other alarms of the checkpoint are also activated or deactivated.
  • If you edit a message variable in custom checkpoints, it adds a new message variable in the
     Message Variable
     table. To edit a message variable in custom checkpoint, delete and create a message variable.
  • Dynamic population of 
    Alarm Message
     field is not supported in AC GUI.
  • When you define a threshold for a checkpoint, first select a message and then save it. The message text field is updated with the corresponding message text after the probe reloads the page.
Known SQL Server 2000 Problem
Sometimes, SQL Server internal tables are not updated with the current size values. This can cause negative size values in the following checkpoints:
  • database_size
  • free_size
  • fg_free_size
  • table_size
Use command DBCC UPDATEUSAGE to correct the values in SQL catalog. Running this command takes a long time on large databases.
Known SQL Server 2005 Problem with av_fragmentation
SQL Server 2005 changed SQL syntax for some commands with its SP2 maintenance. If you want to run the checkpoint av_fragmentation, you must apply the SP2 maintenance pack otherwise the checkpoint returns the error 0x80040e14.
V2 QoS Compatibility Mode
In the V3, the has_max flag has been added to following checkpoints:
  • alloc_space
  • fg_free_space
  • free_connections
  • free_space
  • locks_used
  • logic_fragment
  • scan_density
  • server_cpu
  • server_io
  • user_cpu
  • workspace_memory
If you have created the QoS definition for any of these checkpoints under V2 sqlserver probe, you must enable the checkbox "QoS V2 compatibility" in the 
General
 section of the probe. This ensures that all data is inserted correctly into the QoS database. If you want to use the V3 format (with the has_max flag), delete the V2-generated QoS definitions for these checkpoints (all data for these checkpoints are deleted).
(From Version 5.10) Map Alarms to Database Instance Name 
You can configure the probe to change the Configuration Item (CI) name to map individual alerts of the profile checkpoints to come under one Database Instance Name.
 
Follow these steps:
 
  1. Deactivate the sqlserver probe.
  2. Delete the 
    alarms.cfg
     file from the 
    CA UIM Installation Directory
     > probes > Database > sqlserver folder.
  3. Upgrade the probe to version 5.10 or later.
  4. Navigate to your robot and open the probe 
    Raw Configure
     interface.
     Do not activate your probe now.
  5. Navigate to the 
    setup
     section, and set the 
    usm_display_db_level
     key to 1. When you enable this key, the CI name is appended with “
    ##<Database Instance Name>
    .” If no database name is mentioned in the CI for a profile checkpoint, the probe adds the master database name in the defined format.
    Default: 0
  6. Clear the 
    niscache
     folder of all the robots in your environment that have the sqlserver probe. The folder is located in the 
    CA UIM Installation Directory
    .
  7. Acknowledge all alarms from the probe.
  8. Run the following queries on your UIM database to delete the QoS data from the probe:
    delete from s_qos_data where probe = 'sqlserver'
    delete from s_qos_definition where qos_group = 'QoS_SQLSERVER'
    delete from nas_alarms where prid = 'sqlserver'
    delete from cm_configuration_item_metric where ci_metric_type like '4.2%'
    delete from cm_configuration_item where ci_type like '4.2%'
  9. Restart the 
    data_engine
    nas
    nis_server
    discovery_server
    , and 
    discovery_agent
     probes.
  10. Activate the 
    sqlserver
     probe.