Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Monitor Remote DB's

Author  Topic 

sqlsponge
Starting Member

2 Posts

Posted - 2011-04-05 : 12:55:37
Hello...

I have been tasked with building a process that will track certain aspects of our databases on remote systems. I have one main sql server and eight remote sql servers. Each has databases on it that we would like to track the following:

Database Data Size
Database Log Size
Last Backup
Server Memory Usage
Server CPU Usage
Other Statistics about SQL Server and the Server Itself

I am having a tough time figuring out where to start and how to build the database structure to bring all this data together into one database on the central server.

If anyone has any links to samples or suggestions on how I can best bring this data together so we can get daily updates via e-mail or a SSRS report subscription I would be thankful.

Thanks
sqlSponge

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-05 : 13:04:32
Have you considered using Microsoft's SCOM product?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

sqlsponge
Starting Member

2 Posts

Posted - 2011-04-05 : 17:17:56
Unfortunately that is not an option. The servers I we are managing are at a client location and not on a domain we have access to in order to use those tools.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-04-05 : 21:18:25
Most of what you want can be had with perfmon. Have perfmon run and log to a file.

For last backup and database size, write custom scripts and pop the values in a database.

Then periodically harvest the data, generate reports, whatever you want to do with it.

Query:
msdb.dbo.backupset for backup info.
master.sys.master_files for file size.
Use RELOG to harvest the perfmon logs.
Go to Top of Page
   

- Advertisement -