question

Mandar Alawani avatar image
Mandar Alawani asked

SQL Server Instance failure alert

Hi,

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

sqlserver-instancesalerts
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

4 Answers

·
Matt Whitfield avatar image
Matt Whitfield answered

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.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
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.
0 Likes 0 ·
sp_lock avatar image
sp_lock answered

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

5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mandar Alawani avatar image Mandar Alawani commented ·
Currently, I do have access to buy/use any Third party tools...can this be done using any script or any SQL server feature??
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@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.
0 Likes 0 ·
sp_lock avatar image sp_lock commented ·
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
0 Likes 0 ·
sp_lock avatar image sp_lock commented ·
or on technet... http://gallery.technet.microsoft.com/ScriptCenter/en-us/site/search?f[0].Type=Tag&f[0].Value=SQL%20Server%202005
0 Likes 0 ·
Mandar Alawani avatar image Mandar Alawani commented ·
@Matt/Jonlee: i will see if i can get my bosses get a monitoring tool. and I have seen Idera, it is really good..
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

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/

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mandar Alawani avatar image Mandar Alawani commented ·
@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?
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
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
0 Likes 0 ·
sp_lock avatar image
sp_lock answered

@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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.