January 23, 2007 at 1:44 pm
Hi,
What is the best way to monitor multiple servers. I need to watch db size, number of users, etc. I know how to collect it all. I just don't know the best way to get it back to a centralized server. Should we use DTS and create our monitoring on each server and modify the dts on the monitoring server each time we add a server? This is what we currently do. Is there an easier way? Are linked servers recommended?
Thanks!
Sam
January 23, 2007 at 2:47 pm
If your company can spare/spend money on this you can buy good tools...
If you want to write the code...
You can do using DTS/BCP/LINKED SERVER/OSQL....depends on your servers security requirements...
I use OSQL for this purpose because I used WIDOWS authentication to connect to all servers...
MohammedU
Microsoft SQL Server MVP
January 23, 2007 at 3:41 pm
You might also want to look at using WMI to monitor your servers..
- James
--
James Moore
Red Gate Software Ltd
January 23, 2007 at 4:44 pm
Yes, I will be writing code.
Is it possible to set a script up to read our server names from a table in the 'monitor' computer and retrieve the data and then insert it into the monitor tables? I was thinking i could write a sp which would read in the table names and run a xp_cmdshell statement. BUT, I have no clue how to get the results of an osql statement back into the source db. Is this possible? Some simple steps would be greatly apprecitated.
Thanks!
Sam
January 23, 2007 at 9:39 pm
For my servers I use multi-server jobs and snapshot replication to copy my SPs across. Comes really handy, so you don't have to configure the same reports/alerts on each server.
January 24, 2007 at 6:20 am
I think if you're asking this question then you shouldn't be trying to do what you're asking. I'd recommend SQL Diagnostic Manager as a very useful tool for this type of thing - it just runs in the background, no agents, collecting information. I don't favour linked servers for this type of thing , but I generate data like this locally as html pages , that way each server process is isolated and independent and has no dependencies.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 24, 2007 at 9:40 am
You may want to look into MOM 2K5 ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 24, 2007 at 11:13 am
Maintain a central server (database) with all server names and make all servers as linked servers and run all your stored procs in the loop ( server by server) locally\OPENQUERY. Make sure to check the server availabilty before running the above. Ping could be a good way for doing that.
January 24, 2007 at 8:29 pm
If you're considering 3rd party tools, then you can use tools like SQL Farm Combine to run all your scripts that you write in parallel against all databases and servers and then get the results to a centralized location...
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
January 25, 2007 at 12:03 am
I appreciate all the ideas. I've got to get something together quickly for a report on our environment, and I don't think I'm going to be able to work through the bugs in time. For now I'm going through the process of running the script on each server and then setting up a SSIS job to import all the data - yes, painful. With ~15 servers and 3 tables, that's 45 transforms. I haven't found a way to use the forEAch container in SSIS to pass in a server and create a connection, if that is possible. I'm guessing this would probably be doable with an activeX script.
I ran into some problems with linked servers - some work and some don't. Also replication and multiserver admin had some glitches as well. But I"ll work through it in the future and learn alot, I'm sure.
January 25, 2007 at 10:52 am
Check out the command-line tool RELOG. It allows you to take log files created by Windows SysMon/PerfMon and convert them to different formats, including exporting them to SQL Server databases. (Type RELOG /? at a command prompt to get the list of options.)
Set up a perfmon trace for the counters you're after on each server, set it to roll over and build new files, and store the results in the binary (.blg) format. The binary format is the most efficient. Start simple and only select a few counters that you are interested in. Add more after you've worked the kinks out.
With SSIS, you can then copy over the files from the different servers and import them using RELOG on a schedule.
Omri: When plugging a company's product, especially when not answering the OP's question, it is polite to point out that you work for that company.
-Eddie
Eddie Wuerch
MCM: SQL
December 7, 2007 at 4:22 pm
Hi Sam
I had same issue, but discovered that when running SSIS packages from SQL agent, you can set the connection string. So for more or less same task, I created one generic SSIS package and 1 Agent job with x number of job steps, where x is our number of SQL servers.
Just a hint for you.
//SUN
December 7, 2007 at 4:44 pm
This is similar to what I did. I have a table that stores all the servers. The SSIS grabs that recordset and puts it into an object variable. Multiple "For Loops" then process it, using sql commands and various transfers.
I made one more that polls the servers and marks them as unavailable OnError and pages me with a list of down servers - it then checks back at intervals and pages me when the servers are back up.
I then created a few sql reports to view this information. I've got job history, disk space, modified jobs, new logins...whatever I can dream up. Love it.
An article by rodney landrum got me started - http://www.sqlmag.com/Article/ArticleID/95385/sql_server_95385.html
you can get the example files for free - to read the whole article, it is $5 a month or something. He also has a follow up on the sql reports side of it.
December 7, 2007 at 4:50 pm
Eddie Wuerch (1/25/2007)
Check out the command-line tool RELOG. It allows you to take log files created by Windows SysMon/PerfMon and convert them to different formats, including exporting them to SQL Server databases. (Type RELOG /? at a command prompt to get the list of options.)
-Eddie
I like that idea - very useful. I was pulling from the 2005 DM views to get this stuff, but there are things you can't get. Next week we're going to be implementing another monitoring tool called Zabbix which can work with perfmon data - so we'll be using that for non-sql specific stuff.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply