Step by step guide on deploying SQL Server MCS profile and troubleshooting missing SQL Servers in sqlserver dashboard issue

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Step by step guide on deploying SQL Server MCS profile and troubleshooting missing SQL Servers in sqlserver dashboard issue

Environment

Release : 20.3

Component : UIM - MON_CONFIG_SERVICE_TEMPLATES

- Primary UIM Hub Server and OC 20.3.3 on robot 9.33

- Robots with MSSQL server instance running respectively (sqlserver probe and sqlserver_mcs_template probe running on release 5.45) discovered under Primary UIM Hub Server

Resolution

Deploying SQL Server MCS profile:

1. Install robot on the end Servers with MSSQL installed, in this use case example, we have 5 robots with MSSQL installed, the robot names are sqlserver1, sqlserver2, sqlserver3, sqlserver4, sqlserver5 (for information on how to install robot, click here)
2. Verify sqlserver probe and sqlserver_mcs_templates probe release 5.45 are in the Archive:
   • 
3. Deploy sqlserver probe on the robots sqlserver1, sqlserver2, sqlserver3, sqlserver4, sqlserver5 (for information on how to deploy probe, click here)
4. Creating new Group:
   1. Create Group from Operator Console for the above newly installed robots following the document, in this use case example, we have created a Dynamic Group and named it SQL Server (for information on creating Group, please click here)
      • 
   2. The below is the logic used in the current use-case to discover the servers:
      • 
5. Creating new Profile:
   • 
6. Select SQL Server (Enhanced):
   • 
7. Fill in the connection details, check monitoring settings, enable checkpoints, metrics according to the requirement. When done, click on Monitor to create the Profile, in this use case, we name the Profile as “local database monitor (SQL Server)”
   • 
8. Once the Profile has been successfully created, click on MCS Deployment Overview -> View By Groups -> Profiles:
   • 
9. Verify the profile deployed, remember, in this use case example, in the group SQL Server created earlier, we have 5 robots in the sqlserver1, sqlserver2, sqlserver3, sqlserver4, sqlserver5 but check the below screenshot, the profile has not been applied on 2 Devices:
   • 
10. If the issue is left unchecked, the two robots would never be monitored by sqlserver probe, when sqlserver dashboard is launched, but naturally the two robots on which the profile has not been applied would be missing and not seen in the monitored devices list as below:
    • 

 

Troubleshooting:

Identifying the issue:

1. We have seen in the group SQL Server created earlier, we have 5 robots in the sqlserver1, sqlserver2, sqlserver3, sqlserver4, sqlserver5 but check the below screenshot, the profile has not been applied on 2 Devices:
   • 
2. To identify which robots do not have profile applied, one way is to check in the Group, in this case which is SQL Server, check the column “Monitored by”:
   • 
3. From step 2, we see the robots sqlserver4, sqlserver5 do not have the profile “local database monitor (SQL Server)” applied as we observe they are not monitored by sqlserver probe

Fixing the issue:

Note:

1. Access to the UIM Backend Database is required
2. The devices would be deleted from the database, and rediscovered, as a result, any other monitoring needs to be reapplied
3. The UIM Primary Hub Server may have to be restarted
4. It would make complete sense to only troubleshoot the robots sqlserver4 and sqlserver5 on which the issue has been identified but the below troubleshooting steps includes all the robots, i.e. sqlserver1, sqlserver2, sqlserver3, sqlserver4, sqlserver5

To solve the issue, perform the following steps:

1. Login to UIM backend Database
2. For our use case example, run the following query to find out the cs_id of the devices, modify the query according to your environment:

select cs_id, name from CM_COMPUTER_SYSTEM where name = 'sqlserver1' or name = 'sqlserver2' or name = 'sqlserver3' or name = 'sqlserver4' or name = 'sqlserver5'

3. The above query’s output:
   • 

 

4. Run the following query to delete the existing profiles associated with robots sqlserver1, sqlserver2, sqlserver3, sqlserver4, sqlserver5 using their respective cs_id:

update ssrv2profile set status='delete', retries=0 where cs_id = 60 or cs_id = 61 or cs_id = 62 or cs_id = 63 or cs_id = 64

The above query should run successfully without errors:

5. Run the following query to delete the robots sqlserver1, sqlserver2, sqlserver3, sqlserver4, sqlserver5 using the cs_id:

delete from CM_COMPUTER_SYSTEM where  cs_id = 60 or cs_id = 61 or cs_id = 62 or cs_id = 63 or cs_id = 64

 The query should run successfully without errors:

 

6. Login to Infrastructure Manager -> Primary UIM Hub Server:
   1. Deactivate the mon_config_service (wait till Port and PID are not used and the probe is completely deactivated):
      • 
   2. Deactivate the discovery_server probe (wait till Port and PID are not used and the probe is completely deactivated):
      • 
   3. Activate the mon_config_service probe:
      • 
   4. Activate the discovery_server probe:
      • 
7. Rerun the following queries again after the discovery_server probe is completely up and running, this step will verify the deleted devices have been re-discovered:

select cs_id, name from CM_COMPUTER_SYSTEM where name = 'sqlserver1' or name = 'sqlserver2' or name = 'sqlserver3' or name = 'sqlserver4' or name = 'sqlserver5'

8. The above query’s output confirms the deleted devices have been successfully re-discovered:
   • 
9. Login to the Operator Console, click on Groups -> Select the Group SQL Server to verify the robots sqlserver1 to sqlserver5 have been added back to the Group. Verify under the column “Monitored by” you should see “sqlserver”:
   • 

Note:

1. If you do not see the robots added back in the Groups, restart the Primary UIM Hub Server:
   •