June 15, 2011 at 8:31 am
I have some SQL 2003, SQL 2005 and SQL 2008 servers. In addition to some windows XP machines, that connect to server.
The servers are used in building automation. There is an operator watching 24/7. I need to give the operator the ability to determine if there is a problem with any of the servers. The simplest is a ping.
But ideally I need to give them some thing visual (like green and red buttons, etc). In addition to respond to ping, maybe something like an alert if there is a problem with the database?
Where can I find such a utility?
The utility can be a VB script I guess. Or better yet an ASP page , since I have an IIS server that I can use for this purpose.
Thanks,
June 15, 2011 at 8:44 am
techzone12 (6/15/2011)
I have some SQL 2003, SQL 2005 and SQL 2008 servers. In addition to some windows XP machines, that connect to server.The servers are used in building automation. There is an operator watching 24/7. I need to give the operator the ability to determine if there is a problem with any of the servers. The simplest is a ping.
But ideally I need to give them some thing visual (like green and red buttons, etc). In addition to respond to ping, maybe something like an alert if there is a problem with the database?
Where can I find such a utility?
The utility can be a VB script I guess. Or better yet an ASP page , since I have an IIS server that I can use for this purpose.
Post makes me think you are after a simple heartbeat monitoring.
In this scenario, pinging the host is a good starting point but a alive-and-kicking host does not means the database is up, available and responsive.
I'll be sure the database is allive by complementing the basic ping with a script running a query against the database - if it can connect and return the desired value database is at least alive and responsive - this also tells you other remote users are able to connect to it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 15, 2011 at 9:35 am
Servers Alive is a nice tool for this
June 15, 2011 at 9:57 am
Hi,
Few Months back i was also on the same road to find a wayout because our area of scope expanded to 3 new data centers, i found few options :
1. if you have SCCM in your environement, ask the Person responsible for SCCM to scan all the servers (give him a range or a sub-domain if available) for services with name MSSQLServer or with a display name like "SQL Server (MSSQLSERVER)" This works like anything. (you can also scan the systems based on Software installed)
2. I searched few Registry keys that gets registered when there is a SQL instalation, These are different for Diff versions, then prepared one command to readregistry. This command was picking up server with SQL and giving me the Edition Info in a file, you can run it on your network with the help of Powershell or any tool available with WINTEL, you can also ask SCCM team to prepare a package which will make it easy. (but this will not consider named instances 🙂 )
3. Simplest is identify one server in each domainetwork and use EXEC master..XP_CMDShell 'OSQL -L' to list down all SQL Servers broadcasting their presence until and unless they are hidden. 😉 (to know more how to hide SQL instance read: http://www.sqlservergeeks.com/blogs/sarab/sql-server-bi/9/hiding-sql-server-instance)
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
June 15, 2011 at 10:58 am
We use SQL Diagnostic Manager. http://www.idera.com/Products/SQL-Server/SQL-diagnostic-manager/
Lots of green and red buttons and dials and charts and alerts!
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 16, 2011 at 2:18 am
toddasd (6/15/2011)
We use SQL Diagnostic Manager. http://www.idera.com/Products/SQL-Server/SQL-diagnostic-manager/Lots of green and red buttons and dials and charts and alerts!
i use this too, just upgraded to the newest version. and yes lots of colours and alerts 🙂
June 16, 2011 at 7:06 am
The suggestions already posted for a heartbeat monitor are fine. But if you really want to tell the operator "what is wrong" other than the server is there or not there, you need to either build or buy a monitoring solution.
Building a solution isn't hard, it just takes lots of work and time. Buying solutions are much easier and quicker, but you have to lay out cash. If you do decide to go the route of buying a solution, I'd like to recommend Red Gate SQL Monitor (I do work for that company).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2011 at 9:15 am
Thanks for the advice. I will have to talk to the customer and layout the different alternatives.
June 16, 2011 at 9:58 am
Not to be plugging Red-Gate BUT I have been using their SQL Monitor product and have been pretty happy with it.
CEWII
June 16, 2011 at 2:06 pm
I know it's a Redgate site and all but I really do like the SQL Monitor tool (you can try it out online here[/url]). Simple interface, web-based so you can get to it from anywhere and works nicely on popular mobile devices such as iPhones and iPads.
I've also used Quest and Idera's products. LOTS of bells and whistles there as well but you certainly pay for what you get.
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
June 28, 2011 at 6:43 am
I also personally recommend RedGate.
Thank you.
Aatish Patel
June 28, 2011 at 1:22 pm
June 28, 2011 at 2:52 pm
Jayanth_Kurup (6/28/2011)
If your on a budget there is always the SSRS route , assuming you know which system tables to query
I don't see this as much of a solution, my biggest issue is that it stored no history and any counters you are using either have to support history already or you are going to have to build something to save it.
What this solution can do is give you a snapshot of right now.
CEWII
June 29, 2011 at 7:01 am
Yes I am on a budget as you can imagine. Long term, we want to get something with all the bells and whistles (like Red gate), but for now I am looking for something simple.
Can you elaborate on the Solution you are suggesting?
Thanks
June 29, 2011 at 7:03 am
Rather than trying to roll your own solution, there are already free and pretty decent solutions available for free on Codeplex. Check out SQL Live Monitor, for instance: http://sqlmonitor.codeplex.com/[/url]
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply