sqlserver (SQL Server Monitoring) Release Notes
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.
This section describes the history of revisions for this probe.
Support case(s) may not be viewable to all customers.
Fixed: Garbled data coming in alarm message for the user_waits checkpoint.
Fixed a defect where the check_interval of the checkpoint was not working.
Added checkpoint logfile_usage_with_avail_disk and category_name for jobs in checkpoint long_jobs.
Updated the mirror state values in the hints section of mirror_state checkpoint.
Fixed an issue where the backup_status checkpoint was reporting wrong value.
Fixed SOC Issues.
Fixed security token leak by closing the security tokens when they are not required.
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.
SQL Native Client 2008 support.
Problem with an empty password solved
Windows authorization support corrected.
Queries logfile_usage and fg_free_space corrected
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:
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:
- Right-click on the table and go toProperties>Permissions Tab.
- SelectAddand browse for the user to grant permission.
- Select the permission type under theGrantcolumn.User is mapped for the required permission. User mapping is required for the following tables:
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 toNiscachefolder 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
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.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_MODEenvironment variable to one of the following values in the
- If you do not set any value of the environment variable, then the probe usesTWOFISHencryption and decryption, by default.
- Restart theNimsoft Robot Watcherservice 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.
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 theUser IDspecified while creating a connection does not have the required privileges in the database.
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 theprocessesprobe 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 theMessage Variabletable. To edit a message variable in custom checkpoint, delete and create a message variable.
- Dynamic population ofAlarm Messagefield 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:
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:
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
Generalsection 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:
- Deactivate the sqlserver probe.
- Delete thealarms.cfgfile from theCA UIM Installation Directory> probes > Database > sqlserver folder.
- Upgrade the probe to version 5.10 or later.
- Navigate to your robot and open the probeRaw Configureinterface.Do not activate your probe now.
- Navigate to thesetupsection, and set theusm_display_db_levelkey 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
- Clear theniscachefolder of all the robots in your environment that have the sqlserver probe. The folder is located in theCA UIM Installation Directory.
- Acknowledge all alarms from the probe.
- 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%'
- Restart thedata_engine,nas,nis_server,discovery_server, anddiscovery_agentprobes.
- Activate thesqlserverprobe.