In this blog, I will focus on Two State / Three State monitor in SCOM for Windows Azure SQL Database. In my previous blog, I talked about custom queries can be defined in Authoring console with option to create Two state or Three State monitoring Windows Azure SQL database. Now let me walkthrough the process.
Scenario: Alerts Notification for deadlocks in Windows Azure SQL database, We are going to create two state monitor alert for Deadlocks count exceeding > 0.
In the Operation Manager Console, go to “Authoring”
Under Management Packs Objects. Right Click on Monitor –> Create a Monitor –> Unit Monitor
In “Select a monitor type” wizard. Expand Windows SQL Database.
Select “User-defined SQL Query Two State Monitor” and Select Designation Management Pack
Click Next. In the General Properties.
Enter: “specific friendly name”
Monitor Target” select target Windows Azure SQL database”. Enable Monitor.
SQL Query: Database: “master”. (Deadlock query runs in Master database).
Query: type SQL query with SQL Database name
| select count(*) as Count from sys.event_logwhere database_name = ‘Enter SQL database Name’and event_type=’deadlock’ |
Test Condition: Select Scalar Value
In Test condition: Enter Friend Name
In Cell Value: Equal to 0.
(Value 0 will be the normal status without deadlock. Moment deadlock occur, value will not match with defined value. It will cause Critical Alert Notification)
Configure your schedule: 15 min. (Here SQL query will run every 15 min to verify the defined value)
Click on Next. Leave Configure Health as default and click on Next.
Configure Alerts Setting: Check Generate alert for Monitor
Alert Name: Specify Friendly alert name and click on Create.
Now custom alert is configured for any deadlock occur in Azure SQL Db. You can use the same procedure for any custom alert using SQL query and Two/ Three State Monitor. Hope this will gives pointers for custom alert management using SCOM.
Happy Reading!
Puneet
