June 9, 2011 at 4:09 am
Hello,
I just came across some articles talking about Management Data Warehouse. I set it up to collect data. It looks OK.
Now my ultimate goal is to be able to create a report that shows servers, the databases on that server and database growth. This would be great if I could do this through SSRS.
I'm not sure if Management Data Warehouse is my answer? Has anyone been able to use this as a database growth for multiple production servers?
I am running SQL Server 2008 on several servers. A few are SQL 2005 and I have two that are still SQL 2000.
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
June 9, 2011 at 7:52 am
Are you talking about Data Collector?
If so that only collects data from SQL 2008 boxs.
http://www.sqlservercentral.com/articles/Product+Reviews/66278
I think you would need to use something like sp_spacedused as i belive that works on all the versions you need.
http://www.sqlservercentral.com/articles/Administration/2771/
Though im sure there are better ways to do it, at least this starts you off! 🙂
S
June 9, 2011 at 8:20 am
If the data collector can only collect from SQK 2k8 servers than that won't work for considering the number of 2005 and 2000 servers still running.
I'm actually doing something similar. I'm building a database monitoring system to retain historical data for backups, database status, server hdd space. I'm planning on adding job outcomes, database growth and whatever other metrics I can think of.
Currently my structure revolves around a server table (just a list of servers I want to monitor) and a database lookup table (currently manually maintained).
I have powershell scripts that queries the server table to get the list of servers then for each server in the list it connects and executes queries on system views and/or table to collect data. The scripts output to a csv. SSIS packages pickup these csv files and load them into their respective tables.
I then have stored procedures that analyze the data for the day and generate html reports. I plan to encorporate monthly and yearly reports as well.
June 9, 2011 at 8:28 am
Yep you'll have to roll your own.
Central Management Server, Policy Based Management, works great with versions below 2008, Management Datawarehouse is 2008 on up.
Arron Nelson has a great powershell script for getting disk space
http://sqlvariant.com/wordpress/index.php/2010/11/quick-blog-powershell-disk-and-mountpoint-check/
Check out his Powershell blogs on SQL University as well, lot of great stuff.
http://sqlchicken.com/sql-university/
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
June 9, 2011 at 8:33 am
SQLBalls (6/9/2011)
Central Management Server, Policy Based Management, works great with versions below 2008, Management Datawarehouse is 2008 on up.
Is there a way to execute a query on a CMS through SQL Server agent? I spent a decent amount of time researching this but couldn't find any information past 'you can execute queries across multiple servers'. No mention of scheduling something...
June 9, 2011 at 8:53 am
Thanks everyone.
So I know that I have to create a project and start working on scripts.
I thought there were custom reports that that you can install and show database growth. I might go that way for now. Even if it is not on on server.
Our IT policy doesn't allow me to run powershell scripts.
I can implement solutions in SQL Server, but management wouldn't want me to run powershell scripts.
I get calls about a server going slow or sometimes the consultants do huge data loads and don't tell me. I just need someway to monitor all the databases and show the growth of the database.
Again, I might try some custom reports. I saw somewhere there are custom reports that do this.
Thanks again for all the responses.
Things will work out. Get back up, change some parameters and recode.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply