Server Farm Reporting - Part 1
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mtierney/3103.asp
-
Quite excellent and reporting services is a good idea. But we sometimes need just need to know more than the free space in db files. In these cases I personally suggest MS MOM (MS Operations Manager). I know it costs but it really helps you with every details of reporting. Besides it does not have a simple UI as yours.
Zubeyir
-
Another option is SQLH2 which comes with a reporting pack and a comprehensive stack of supporting tables.
-
This is something i've been looking at for a long time and it's intersting to see how other people approach the problem.
unfortunatly I came across a stumbling block in using linked servers and MSDTC that prevented me from getting all teh monitoring info i wanted.
sure you can execute myserver.master.dbo.xp_fixeddrives and pull the info back to your repository.
but you can't run DBCC commands via a linked server (try it- dbcc is not associated with any database!)
in the end we've written a custom activex control (so it will be sql 2005 and 2000 compatable) which can run any query against a list of servers and put the results in the repository.
i'm happy to supply source code for the module and procs to run it if anybody is interested.
MVDBA
-
Nice article and the reports look great but you seem to be reinventing the wheel. Check out sql health check. Pretty easy to implement and it can collect a lot of data. As the other poster pointed out it comes with over half a dozen reporting services reports.
-
We had the same problem - 80+ servers, 800+ databases. I also went with Pull architecture, but everything is run from the data warehouse server (SQL 2005) using dynamically created (created and dropped after the calls) Linked Server, we decided we didn't want the overhead of implementing and maintaining code on every server we own (we're trying to reduce our workload after all).
-
but if you're using linked servers how are you running commands like
dbcc sqlperf(logspace)
which would tell you if a log were large or not required intervention (i.e a developer has deployed a new database in the wrong recovery mode on your dev server)
MVDBA
-
It *is* doable:
select a.* from
openquery(SERVERNAME,'SET FMTONLY OFF; EXEC(''dbcc sqlperf(logspace)'')') a
Just make sure that:
1.you have the necessary permissions
2.lazy schemavalidation = true
Cheers!
* Noel -
Hello Everyone,
Just looked at the article and see that some people want to execute DBCC commands on a remote server. I not sure you want to do this as it is a power full tool and you can really make a mess of things. To collect data and stats is one thing but to do anything else, I believe that you should be doing that on that server, not remotely.
I've written my own server farm code that performs the same function but I've been playing with Quest SpotLight for SQL Servers Enterprise and it really does the trick. If your company has 30+ servers that you are maint/manage/monitoring then you should get them to spend some money and get something with more teeth. The app does real time monitoring and historical data collection, which is great for diagnosing an issue. I also use another product call Capacity Manager from Quest (..no I don't work for or get kick backs from Quest!) it too has tons of information on growth, growth patterns, disk space etc.
Check them out I think you will find them worth the talk to you management.
On a side note, I use my "home grown" code to produce an hourly report that show all failed jobs on all SQL servers. The developer have access the test/uat/dev reports and we look at the production reports. It's a great way to see what's happened or happening on a job level.
Just my 3 cents worth : )
Rudy
-
Hi there,
Great article! We do something similar here, but with a push method as we have a lot of overseas servers etc. We also use the DBCC SQLPERF(LOGSPACE) command to populate the remote repository using DTS package data pump task with the command as the source. The downside is obviously this has to reside on each server... But I like Noel's one, that's me sorted for the rest of the day!
We did try Spotlight on SQL Servers Enterprise, and to be honest it didn't really do the trick as a monitoring solution. It was very pretty indeed, but I found the alert side of things was lacking, so you needed to be sitting there looking at it. I should imagine it would be great for a diagnosis tool if a server starts playing up though!
On a side note, I have written a service which polls all our SQL servers on a sheduled basis for perfmon counter information and stores that info in the same repository leading to some great daily performance reports with day on day comparison so it's quite easy to see when a machine starts struggling and also if a certain time of day is particularly bad (i.e. a rough job that could do with streamlining). It's VB.NET, and it's bespoke to my company, but I'm sure it wouldn't take too much fiddling to get it going elsewhere.
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font] -
I got around the dbcc issue by creating sp's on master that are then called by the linked servers
-
Isn't creating user objects in master a cardinal sin?
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply