SQL server monitor

The SQL Server monitor opens a connection to the specified SQL server and tests the performance of its subsystems to determine the server's general health. The overall performance of the server is identified by its weakest performing subsystem.

Administrators can use the SQL server monitor to:

• Use pre-configured performance counters provided by the Windows Management Instrumentation (WMI) service to test multiple SQL Server subsystems at once.
• Identify performance degradation in critical SQL Server components.
• Determine the exact point of failure.
• Take corrective action before outages occur.

The built-in internal sampling incorporated in the monitor helps to combat counter spikes. The monitor issues the WMI query five times—once every second—and calculates an average based on the query results.

If you do not have local account credentials, you can enable cross-domain WMI with Windows impersonation for use with RPC. Credentials using NTLM Authentication Schemes (Windows LT Lan Manager) will function across domains without local accounts.

Create an SQL server monitor

1. Verify that:
   1. WMI is enabled and functioning to monitor the SQL server.
   2. The remote SQL server is accessible through an RPC connection to run the WMI queries.
2. Log in to ipMonitor as an administrator.
3. Click Devices in the toolbar.
4. Locate and click the targeted device you want to monitor.

5. In the toolbar, click Add > Add New Monitor.

6. In the Select Monitor menu, click SQL Server Performance Counters.

7. Under Identification, complete the fields and selections.

   

   1. Enter a name in the Monitor Name field using up to 64 characters.

      This name will appear in the Monitors List, Monitor Status, Logs pages, and your reports.

      You can change this name later, if necessary. ipMonitor does not use this field to internally identify this monitor.

   2. Select Enabled to enable the monitor.

      When enabled, the monitor tests the specified resource using the settings you enter under Test Parameters. You can disable the monitor later if required.

   3. (Optional) Select Store Monitor Statistics for Recent Activity and Historical Reports to enable this functionality.

8. Under Test Parameters, complete the options.

   

   1. Enter the server name or primary IP address of the SQL server.

      Click Browse to locate the system in your network.

   2. Enter the SQL server database instance name you want to monitor. Leave this field blank to select the default instance.
   3. (Optional) Click Select and assign a credential. When the monitor runs the executable file, it will use the credential account and password combination to authenticate to the machine, directory, or file.

9. Under Analysis of Test Results, select the counters you want to enable using WMI.

   

   You do not need to enable every counter. The monitor can test all counters during the monitoring process. You can choose to enable the counters that directly relate to the subsystems you wish to monitor.

   Except for the Minimum SQL Memory (kb) and the Cache Hit Ratio Percentage counters, the default value represents a threshold rate that cannot be exceeded.

   Counters are tested in the order that they appear. If you experience multiple counter failures, only the first counter error encountered will be reported.

10. Click Verify to test the monitor. This ensures that it can connect to the targeted SQL server and retrieve each counter value.

    Counters outside the threshold display in red.

11. Under Timing, configure the fields for the monitor testing states.

    

    1. In the Maximum Test Duration field, enter the maximum test duration rate (in seconds) that the monitor times out before the test is considered a failure.

    2. In the remaining fields, enter the number of second between each test while the monitor is in an OK state (Up), a failed state while alerts are processed (Down), and a failed state and the maximum number of alerts have been processed (Lost).

       In the Lost state, no additional failure alerts are processed. However, a recovery notification is sent if the monitor recovers.

12. Enter the amount of time delay for each monitor testing state. For example, you may choose to intensify testing when a monitor enters a Warn state and reduce testing when the monitor enters a Lost state.

13. Under Notification Control, complete the fields to determine how many test failures must occur before an alert is sent.

    

    1. Enter the number of test failures that occur for each alert before ipMonitor generates an alert for the monitor. The default option is 3.

    2. Enter the maximum number of alerts to send before the monitor enters a Lost state.

       The monitor must be assigned to a notification alert to generate an action.

14. Under Recovery Parameters, complete the fields to indicate the corrective action used to automatically restore a resource using the External Process Recovery, Reboot Server Recovery, or Restart Service Recovery action.

    

    1. Enter the Fully Qualified Domain Name (FQDN), NetBIOS, or IP Address of the machine hosting the service that needs a restart or the machine that needs a restart. You can also click Browse to locate and select the machine.
    2. Select the set of credentials used by the recovery alert. You can select a specific credential to execute recovery alerts that require access to restricted resources, such as Reboot Server, Restart Service, or External Process.
    3. Select the list of services to restart on the target machine specified in the FQDN/NetBIOS/IP Address field. This field is only required for the Restart Service alert. If a service has dependencies, select all dependent services.
15. Click OK.