SQLSERVER probe, how to generate an alarm when a database is online.

Document ID : KB000010801
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

The SQLServer probe has the default checkpoint "Database_State". This can generate an alarm when the databases are offline. In some instances however it is desirable to alarm if a database that should be offline comes online. eg a database replica

This can be achieved by creating a custom checkpoint and this document details how to configure this

Background:
Instructions:
  • Open probe
  • Select templates
  • r-click - "Create New"
    • Enter name
    • Click ok
  • Select Query Tab
    • Ciick "Edit/New"
      • Enter the dbms credentials and click test. This must succeed before you can progress

        1.png

 

  • Enter the following query in the window and click test
    • select name,state_desc,state from sys.databases where name = '<dbname>'
    • If all is working you should see the query result 
    • 2.png

  • Click close
  • Enter a file name in the "Query File field"
  • Click "Edit" next to message variables and define the following

    Name

    character

    row key

    State_desc

    character

    Row key

    state

    numeric

    value


  • It should look like this

    • 3.png
    • Click ok
    • Define the following in Row Identification
      •  $name,$state_desc
    • It should now look like this

      • 4.png

  • Switch to the general tab
  • r-click in the Thresholds / Schedules and select "New"
      • Define the following
        • Threshold object name = "state"
        • Condition = "="
        • Threshold value = "0"
        • Severity = "Your choice of alarm severity"
        • Message = "database_state_2"
        • Message text = "Profile $profile, instance $instance, database $name state is $state_desc "
        • Clear message = "database_state_1"
      • Click "OK"
        • You will receive the following

        • 5.png

        • Click ok and define a new name for the Message
        • Click "Ok" and the new checkpoint template is created
          • This will now generate an alarm when ever the database "tempdb" is online