March 18, 2009 at 10:32 am
I'm attempting to set up a notification system, with as little effort as possible, to tell me when one of my servers goes offline. I've been attempting to set this up using Policies but I need to send out an e-mail either when the server goes to offline status or checking on server status on a schedule. Am I barking up the wrong tree in trying to use Policy Management for this?
I need to monitor SQL 2000, 2005 and 2008 servers and already have a Central Management Server set up.
July 25, 2011 at 12:27 pm
Bumping an old thread.
I've a test system that I'm attempting to get this working on. I've set up my condition as follows:
Facet:Database
@IsSystemObject = False
AND
@Status = Normal
If I evaluate this policy, everything looks good if all the databases are online. However, if I take a database offline and evaluate the policy, it only reports those db's that are online...it completely ignores the offline db. Any thoughts?
July 25, 2011 at 12:55 pm
jshurak (7/25/2011)
Bumping an old thread.I've a test system that I'm attempting to get this working on. I've set up my condition as follows:
Facet:Database
@IsSystemObject = False
AND
@Status = Normal
If I evaluate this policy, everything looks good if all the databases are online. However, if I take a database offline and evaluate the policy, it only reports those db's that are online...it completely ignores the offline db. Any thoughts?
I have to admit I have no experience with policies yet, and I just finished reading up on them a couple of days ago. However, how bout try a facet of instance and run a select off of sysdatabases?
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
July 25, 2011 at 2:21 pm
I didn't see an instance facet. I tried to just use an executeSQL() to just query the table, but that results in a 'The policy: 'Check Online dbs's' contains scripts. You should only run policies from a trustworthy source.' error. Will keep tooling around
July 25, 2011 at 3:04 pm
The facet is "Server". Also don't forget that when you set your expression to a query it has to return a true\false. The example in my book uses IsNull(ExecuteSQL(....
I should probably also note that my book says that if you do this you can only run the check manually, and you can't schedule it, so I'm not sure if that will work for you.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
July 25, 2011 at 4:34 pm
Hi
I wouldn't use SQL policies to monitor for instances being down or not.
Why not SQL policies, put it like this, you are going to monitor an instance different to the one that runs the policy.
Because if that instance is down it won't be able to run the policy.
So you are going to run it from another instance and most likely another machine.
This means you are going via a NIC. you can get false alerts on network timeouts.
You are also going on port 1433 which could also have a lot of false positives. You also have to relay on another instance with it's own set of problems which could also go down etc.
Have you thought about using alerts in windows ?
If you use this method you use the OS that sits behind your SQL.
Cheers
Jannie
July 27, 2011 at 7:14 am
Kenneth Fisher-475792 (7/25/2011)
The facet is "Server". Also don't forget that when you set your expression to a query it has to return a true\false. The example in my book uses IsNull(ExecuteSQL(....I should probably also note that my book says that if you do this you can only run the check manually, and you can't schedule it, so I'm not sure if that will work for you.
I didn't realize it had to be a true false, I thought it only had to be a query that resulted in a single cell result (and obviously one with specific value)
Jannie-186227 (7/25/2011)
Have you thought about using alerts in windows ?
If you use this method you use the OS that sits behind your SQL.
I hear you on the network issue. But I don't believe you can achieve the individual database granularity through the OS. I know I can check if the entire instance is online or offline. Maybe a script that executes something through sqlcmd?
July 27, 2011 at 8:00 pm
In Win 2008 you can check the counter databases and maybe monitor the last active trans.
However you might need to do some exceptions for dormant db's
Or shoot off a powershell select from sys.databases and grab the status.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply