Monitoring

Question

Monitoring

Chris Kitchen

Hall of Fame

Points: 3090
More actions
August 1, 2007 at 3:36 am

#117669

Hi,
I have a client running a mixture of SQL Server 2000/2005 instances on Win2k3 boxes, with a seriously limited budget who is wanting to put in place a monitoring solution for a single domain.
They have a dedicated monitoring server within the domain and they are wanting to monitor the following:
# SERVER INFORMATION
1. General Settings
2. Disk Capacity
3. Free Disk Space
4. RAM info
5. CPU info
# DATABASE INFORMATION
1. General Database Settings
2. Used Database Space
3. Current Activity
They have scripts in place already to view login/user account info, what DBs are on what drives etc .... but the main thing they are concerned with is space and performance. They don't have a lot of disk space and there CPUs are always flat lining so they are wanting to monitor and produce reports on this information so they can put together a strong business case for upgrading the hardware.
As I have previously mentioned the budget is seriously tight (can't afford Spotlight/Foglight etc...) and I am hoping someone can suggest some pieces of software that could help my client achieve there goal.
Any help/suggestions would be greatly appreciated.

Thanks in advance.
www.sqlAssociates.co.uk

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 1

As well as the information above my client is also looking for a monitoring and support contract for the SQL Server environment and I was wondering if anyone can recommend any companies that can do this, and if possible what they would roughly pay for the following types of monitoring/support:

1. 24*7 365 days a year.

2. 7:00am - 7:00pm 365 days a year.

3. 9:00am - 5:00pm 365 days a year.

Thanks in advance for your help, much appreciated.

www.sqlAssociates.co.uk

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

well I swear by Idera SQL Server Diagnostic Manager, it costs around £700 per server in uk - negotiable according to number - It's been my right hand for 8 odd years.

Many of what you ask you can write yourself, and the performance dashboard , for sp2, will give you some monitoring. I blogged on some of this, for sql 2000 , but I'm running the same stuff on sql 2005 ( slighlty modified )

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2006/11/24/creating-baselines-for-p-o.aspx

there are three posts.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Dave Mellors-404473 Newbie Points: 7 More actions · Answer 3

If the budget is seriously tight then you might want to consider some open source solutions such as Nagios and Big Brother/Big Sister.

Also, MRTG or RRDTool might provide some useful visual representation of performance and capacity data.

You might have to write some of your own scripts to collect all the information you want. So, the software may not be exactly what you are after but at least it might give you some options.

Dave Mellors
Red Gate Software

Stevo Smocilac Mr or Mrs. 500 Points: 564 More actions · Answer 4

Have a look at a free Microsoft tool called "SQL Server Health and History Tool", it has a component which allows you to collect any performance monitor counters. It is fully documented and except for a few small issues with SQL Server 2005 I have found that it works pretty well. The only drawback is that it doesn’t have any real-time reporting, you need to schedule the tool to download each servers performance data.

You can download it here, http://www.microsoft.com/downloads/details.aspx?familyid=eedd10d6-75f7-4763-86de-d2347b8b5f89&displaylang=en.

Regards

Stevo

EdVassie SSC Guru Points: 60476 More actions · Answer 5

I have developed a tool that can capture any of the SQL Server performance counters, plus some other details such as free disk space. It works on SQL 2000 and 2005 (should also work on SQL7). The reporting side is just ad-hoc queries, so you have the opportunity to develop exactly what you need.

If you send me your email address I will send a copy of the scripts and documentation.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

cmille19 SSCertifiable Points: 5970 More actions · Answer 6

I haven't used this package, since a couple folks mentioned open source. This one is .NET/SQL Server based:

http://sourceforge.net/projects/healthmonitor/

I would also recommend MS Health and History Checker

EdVassie SSC Guru Points: 60476 More actions · Answer 7

The Health & History checker works for SQL 2005 only. You will need to look at something else for SQL 2000 and below. The SQL 2008 July CTP includes a monitoring data warehouse as a standard component. I have only had a very quick look at this so far, but if it gets to RTM it is likely to replace the H & H tool.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Stevo Smocilac Mr or Mrs. 500 Points: 564 More actions · Answer 8

Just to clarify, the SQL Server Health and History tool works with both SQL Server 2000 and SQL Server 2005. However there are a few small issues with SQL Server 2005 like some perf mon counters not being captured correctly, i.e. Buffer Cach Hit Ratio.

Mai Nguyen-460836 SSC-Addicted Points: 481 More actions · Answer 9

EdVassie,

Would you e-mail me copy of the scrip and documentation. I greatly appreciated!

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 10

Thanks guys for all your comments and suggestions, it's greatly appreciated.

EdVassie can you email me a copy of the scripts/documentation? I've dropped you an email with my private email address.

Thanks again.

www.sqlAssociates.co.uk

Minaz Amin SSChampion Points: 11052 More actions · Answer 11

There are some tools available from Microsoft to monitor and diagnoses the issues:

1. Read80Trace is a command line utility for processing trace files generated by SQL Server 2000. As output, it generates RML files and/or a database populated with normalized data that can be useful for analyzing the performance of the system. Read80Trace requires that the destination database also run SQL Server 2000 or later.

Link: http://www.microsoft.com/downloads/details.aspx?familyid=5691AB53-893A-4AAF-B4A6-9A8BB9669A8B&displaylang=en

2. PSSDIAG is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files. PSSDIAG can natively collect Performance Monitor logs, SQL Profiler traces, SQL Server blocking script output, Windows Event Logs, and SQLDIAG output.

http://support.microsoft.com/kb/830232

Minaz

"More Green More Oxygen !! Plant a tree today"