SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sadequl Hussain / Saving Time with Proactive DBA Monitoring / Latest Posts

RE: Saving Time with Proactive DBA Monitoring

PugMaster — Mon, 27 Jul 2009 04:46:58 GMT

I have done a similar thing with SSIS, you just need to add the new monitored instance into a table in your repository and ensure the account running the package has permission on the target Instances. I then have 3 packages, one that runs stat's daily, one that runs hourly and one that checks status, agent heart beat, PLE etc every 5 minutes. I then have a aspx page looking at the repository for status info and some reporting services reports pulling of trend graphs etc.I beleive i got the original SSIS packages from a feature on this site and modified it to add more metrics and update the code for SQL 2008 instances.

RE: Saving Time with Proactive DBA Monitoring

BU69 — Wed, 22 Jul 2009 07:45:55 GMT

I read the article and it was very useful, some of the things like running queries accross multiple servers can now be done in SQL 2008 and some of the other tasks I could think of better ways to retrieve the information like disk space but for finding out Windows information I should think this is very useful, if I can't get our monitoring tool to pick up information from the event viewer I will look into this as a way of getting this.

RE: Saving Time with Proactive DBA Monitoring

Vee — Wed, 22 Jul 2009 02:41:00 GMT

Using the linked server concept, we also need to load the ole's in sql server to get the clients disk space data. using the power shell, its quite simple.

RE: Saving Time with Proactive DBA Monitoring

Vee — Wed, 22 Jul 2009 02:39:51 GMT

here is one link http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/

RE: Saving Time with Proactive DBA Monitoring

Vee — Wed, 22 Jul 2009 02:35:55 GMT

No need to have 2008. We can install on one server and can remotely access the data from the client server.

RE: Saving Time with Proactive DBA Monitoring

BU69 — Wed, 22 Jul 2009 02:21:06 GMT

Is that only in SQL 2008 though? It would interesting to hear more about this, there seems to be so many different ways now, especially with 2008, to administer SQL accross a few servers that it's really becoming an art in itself, which one is best? I've tried several different approaches but am still unsure that my master target method in 2005 is the best, it's certainly best for keeping it consistent but what is more efficient?

RE: Saving Time with Proactive DBA Monitoring

Vee — Wed, 22 Jul 2009 02:11:08 GMT

I have already implemented such a design in my place. I believe much better to achieve the same is using the powershell, where you no need to create the linked server, as linked servers sometime can cause performance issues. Powershell is the best way to achieve the same.

RE: Saving Time with Proactive DBA Monitoring

BU69 — Wed, 22 Jul 2009 01:00:50 GMT

An easier way to distribute monitoring jobs is to use the master and target feature that way you can implement all your jobs or maintenance plans on a central server which then distributes and controls the jobs even holding the logs centrally and this way you are gurenteed that all monitoring jobs on each server are exactly the same so no inconsistencies creep in.

RE: Saving Time with Proactive DBA Monitoring

Chris Hamam — Tue, 21 Jul 2009 17:21:27 GMT

Nice article Sadequl - love your work!:-)

RE: Saving Time with Proactive DBA Monitoring

DavidP-340734 — Tue, 21 Jul 2009 15:36:24 GMT

At the last three jobs I have had as a DBA I have set up “Dashboards”. The first was an ASP page that connected to each data server and executed code to monitor failed jobs, files and disk sizes and reported on other database statistics. The last two places I have used reporting services. In my current position I have set up a reporting services dashboard that has a color coded list of each production data server’s health. We have approximately 40 production databases spread over 10 servers.Each server name links to another report the directly runs dynamic queries of the master and msdb tables to report on failed jobs, lists of disk drives and space used, server up time, version info and each database. The databases listed shows logical names, segments, size, space used, and growth type. I also run a monthly job that runs a defrag analysis and chkdsk with output to a text file that I import into a table. When a job has failed it sends an e-mail to the IT department. The dashboard will list the failed job and links to a “run sheet” that lists: job name, level, contact, re-run instructions, location of scripts, output files, and links to log files. Anyone in the IT department can go to the reporting services report and see the health of the entire production database system and quickly identify anything that might need immediate escalation.

RE: Saving Time with Proactive DBA Monitoring

aqkhan_k — Tue, 21 Jul 2009 08:34:06 GMT

Hi Sadequl HussainCan you share some of the scripts for stored procedures so that I aslo start work on thatAQ KHAN

RE: Saving Time with Proactive DBA Monitoring

Grumpy DBA — Tue, 21 Jul 2009 08:22:51 GMT

There are plenty of scripts on this web site that can be used to implement database monitoring on your servers. The author gets you started with an outline of what to monitor, you actually need to implement the jobs yourself. Too many people are looking for the "easy" button and want things done for them, you will learn more by doing it yourself.

RE: Saving Time with Proactive DBA Monitoring

nitin1353 — Tue, 21 Jul 2009 05:10:06 GMT

Thanks, please let me know if you can help me in creating the same environmentRegards

RE: Saving Time with Proactive DBA Monitoring

Sadequl Hussain-911759 — Tue, 21 Jul 2009 05:03:16 GMT

[quote][b]nitin1353 (7/21/2009)[/b][hr]I was talking about the latter one,with Each Monitoring DB on each instance.Can you help me in setting this upRegards[/quote]Hi Grasshopper, the techniques I mentiopned in the article can be implemented as stored procedures in a database. The database can be rolled out in each individual instance and configured to send e-mails to a designated DBA.How you implement the functionality (specifics of the actual monitoring procedures) is subject of further articles, code snippets etc.

RE: Saving Time with Proactive DBA Monitoring

nitin1353 — Tue, 21 Jul 2009 03:27:25 GMT

I was talking about the latter one,with Each Monitoring DB on each instance.Can you help me in setting this upRegards

RE: Saving Time with Proactive DBA Monitoring

Sadequl Hussain-911759 — Tue, 21 Jul 2009 03:24:44 GMT

[quote][b]nitin1353 (7/21/2009)[/b][hr]Hi, Excellent articleI am working in a big environment where we have a Monitoring DB on each instance but i am still confused how do we set that up.Can you help me in thatRegards[/quote]Hi Grasshopper, thanks for your post. As I mentioned in the article - monitoring can be done either via a central server or from each server individually. Both are valid approaches. Can you be specific about which area you are seeking help about?

RE: Saving Time with Proactive DBA Monitoring

nitin1353 — Tue, 21 Jul 2009 01:47:13 GMT

Hi, Excellent articleI am working in a big environment where we have a Monitoring DB on each instance but i am still confused how do we set that up.Can you help me in thatRegards

Saving Time with Proactive DBA Monitoring

Sadequl Hussain-911759 — Mon, 20 Jul 2009 23:32:03 GMT

Comments posted to this topic are about the item [B]Saving Time with Proactive DBA Monitoring[/B]