Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Server Farm Reporting - Part 1 Expand / Collapse
Author
Message
Posted Monday, July 16, 2007 4:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 11:38 AM
Points: 269, Visits: 501
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mtierney/3103.asp


Post #382128
Posted Wednesday, August 1, 2007 12:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 9, 2012 6:44 AM
Points: 167, 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

Post #386886
Posted Wednesday, August 1, 2007 2:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 26, 2010 3:21 AM
Points: 50, 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

 

Post #386906
Posted Wednesday, August 1, 2007 4:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #386918
Posted Wednesday, August 1, 2007 6:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:10 PM
Points: 64, Visits: 489

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. 




Post #386956
Posted Wednesday, August 1, 2007 7:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, October 10, 2014 11:06 AM
Points: 1,570, Visits: 680

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

 




Post #386976
Posted Wednesday, August 1, 2007 7:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #386979
Posted Wednesday, August 1, 2007 7:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 7:25 AM
Points: 30, Visits: 208
I was unaware of Health Check. I'll definitely check it out.

Thanks, Mark



Best Regards,

Mark Tierney

Post #386984
Posted Wednesday, August 1, 2007 9:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:06 AM
Points: 6,259, Visits: 2,030
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
Post #387049
Posted Wednesday, August 1, 2007 9:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
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 : )



Post #387052
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse