SQL Server Instance failure alert


I wanted to know if there are any automated methods/ways to check for SQL server instance alerts.

I guess, in SQL server 2008, central management servers can be used for this by implementing Policy Management.

I need to know how would I receive an alert (possibly email) when a SQL server 2000/2005 instance goes down.

any sort of help is highly appreciated!!!...

thanks a lot, Mandar Alawani INDIA

more ▼

asked Feb 26 '10 at 07:07 AM in Default

Mandar Alawani gravatar image

Mandar Alawani
366 29 33 38

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

If you're just starting down the road of setting up monitoring (which it sounds like you are), then you should really look at Red Gate's SQL Response.

more ▼

answered Feb 26 '10 at 07:09 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Got this, use it a lot - it does way more than just check if the instance is there or not. They are working on a new version currently which is coming up for Beta soon.
Feb 26 '10 at 08:14 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

In addition to matt's suggestion, I use Idera's tools (specifically SQLdm)

Idera SQLdm

With this you can also monitor your SQL server via blackberrys or Windows Mobile devices.

Oh, and no! I dont work for Idera ;-) I just rate their tools

more ▼

answered Feb 26 '10 at 07:14 AM

sp_lock gravatar image

9k 24 27 30

Currently, I do have access to buy/use any Third party tools...can this be done using any script or any SQL server feature??
Feb 26 '10 at 07:26 AM Mandar Alawani
@Mandar - There may well be, but the investment of your time to get proper monitoring would far exceed the investment of just buying the tool. Sure, you can simply monitor for 'totally broken' but there is a lot more you should be monitoring, and these tools will help immensely.
Feb 26 '10 at 07:44 AM Matt Whitfield ♦♦

As @Matt is suggesting the ROI is far greater. Not sure on the redgate tool but idera allows to monitor just about everything, it also captures the data for historical reporting on CPU, DISK, MEM etc and also always you to capacity plan. Dev and QA lics are 1/2 price. Plus you can get good discount for buying in bulk!

Alt, you can look at http://www.mssqltips.com/tip.asp?tip=1861 for some ideas
Feb 26 '10 at 07:48 AM sp_lock
@Matt/Jonlee: i will see if i can get my bosses get a monitoring tool. and I have seen Idera, it is really good..
Feb 26 '10 at 08:36 AM Mandar Alawani
(comments are locked)
10|1200 characters needed characters left

you could probably knock something together with PowerShell, just to query to make sure the service is running. In fact here you go, I just found this in my favourites http://www.simple-talk.com/sql/database-administration/powersmo-at-work-part-2/

more ▼

answered Feb 26 '10 at 08:17 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

@Fatherjack: I did not find any script on http://www.simple-talk.com/sql/database-administration/powersmo-at-work-part-2/ which tells if any sql server is down..maybe I missed smethin, can u help?
Feb 26 '10 at 08:36 AM Mandar Alawani
It isnt an exact fit for your requirements but it shows how to connect to a server, start with that and then move on to just check for the instance status. I would research this more for you and write the script you need but I dont have the time right now
Feb 26 '10 at 09:16 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

@Mandar - You have to be careful with "is down". "down" to IT will be different (IMO) to the end users.

SQL Server service stopped, server un available or network down is generally what IT are interesting, but to the users and a DBA things like blocking, high cpu, poor perf'ing queries will be what causes "users" to see a disruption to a Service/Application.

A good monitoring tool will provide all this.

When setting up a tool (like idera), make sure you install the services and DB on a different server/db server.

more ▼

answered Feb 26 '10 at 09:05 AM

sp_lock gravatar image

9k 24 27 30

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 26 '10 at 07:07 AM

Seen: 2667 times

Last Updated: Feb 26 '10 at 07:07 AM