The recommended way to monitor any type of database, including Microsoft SQL Server, is to create a hidden web-page in IIS, Apache or whatever you’re using, that does a specific database query, and when successfully completed, sends a word (e.g. “OK”) to the web browser. This method is secure because you need not open SQL ports in your firewall to allow our monitor through, but it also tests the web server at the same time. Generally, if either the database or the web server(s) fail, you’ll most likely want to consider an application as down.
You could write a query within that page to simply select a record count from a table and if it returns any integer, consider that successful. In the case of MS SQL, you could also write a query to obtain the value of “state” from sys.databases to see if it returns the value you are looking for, e.g. “0” which means “online”. This could be especially helpful if the database is replicated, because a state of “restoring” might be undesirable and cause for diverting traffic. Check out this Technet article for more details: http://technet.microsoft.com/en-us/library/ms178534.aspx
A number of our customers like this hidden web-page method because it allows them to do a number of things behind the scenes to ensure that their web servers, network, databases and other components are functioning as desired. When all of the behind-the-scene tests pass, you then send “OK” to be displayed in the browser.
To look for the word “OK”, you would create an HTTP-CONTENT monitor type. On the “Special Parameters” sub-tab, change the “Send String” to include your hidden page (e.g. GET /mypage.aspx) and then in the “Receive String” box, enter the word(s) you are looking for, e.g. OK.
That should do it! Now set the monitoring interval to the time period you want (e.g. every 1 minute), attach it to your server and give it a test!
To learn more about all of the different monitor types, here is the Monitors TAB Manual Page.