Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Server Farm Reporting - Part 1


Server Farm Reporting - Part 1

Author
Message
bnordberg
bnordberg
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 569
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mtierney/3103.asp



Zubeyir
Zubeyir
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 87

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


Steve Maxwell-405660
Steve Maxwell-405660
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 97

Another option is SQLH2 which comes with a reporting pack and a comprehensive stack of supporting tables.

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


MVDBA
MVDBA
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 Visits: 860
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
JohnMarks
JohnMarks
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 569

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.





Simon Facer
Simon Facer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1587 Visits: 724

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





MVDBA
MVDBA
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 Visits: 860
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
Mark Tierney
Mark Tierney
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 208
I was unaware of Health Check. I'll definitely check it out.

Thanks, Mark


Best Regards,

Mark Tierney

noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6328 Visits: 2048
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
Rudy Panigas
Rudy Panigas
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 1303
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 : )



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search