Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Drew Salem » SCOME - Centralize Monitoring with ASP.NET -...
Add to briefcase
33 posts, Page 2 of 4
««1234»»»

SCOME - Centralize Monitoring with ASP.NET - Part 1 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Andrew Peterson-472853
Posted Monday, April 20, 2009 8:41 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 7:40 AM
Points: 136, Visits: 259
Follow up:

Let's all thank Drew for the contribution. Monitoring is a necessary, but a neglected topic.
Getting a centralized monitoring server is a real must, even for smaller shops.

For those not wanting to learn and/or write ASP web pages, I highly suggest you consider using SSRS as the reporting tool. You have less control with the vanilla framework, but its up and running fast. ~ But, Drew's ASP.net approach will look better and will be more impressive.

Consider capturing all activity, and include succeed/fail flag, along with start/stop duration data. Then in the report, you can review just the exceptions, or all data, plus, with SSRS you can chart out time durations, etc, etc.

The more you are prepared, the less you need it.
Post #700677
Kris-420132
Posted Monday, April 20, 2009 8:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:40 PM
Points: 35, Visits: 171
Interesting topic.

However I am a C# programmer as well, but I still look forward to reading it all the same.
Post #700687
sibir1us
Posted Monday, April 20, 2009 9:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 24, 2013 12:47 PM
Points: 21, Visits: 291
Sean, thanks for the message - I completely and utterly agree with you! I am not to discourage the author of this attempt to give us an elaborate tutorial on how to use .NET to report with SQL Server. I will definitely read the rest of the posts.

When it comes to the reports, here are several hints. (Remember: One of the most valuable human qualities is that we can classify, de-classify, sort and measure by applying different criteria!)

1. it is very important to fit your intentions in manageable and proper categories - what would you like to monitor, how often do you want to / need to monitor, what points of view would like to get
2. certain assets you want to monitor oce a day, others, once a month
3. you want to see resources, security and performance points of view
4. gather a list of counters / events / statuses which you want to monitor
5. write a SSIS package which collects data
6. write reports which show you the data that is important



sean hawkes (4/20/2009)
Great start to a topic I've been considering for a few weeks myself. I find it very interesting that the first reply post was to use SSIS and SSRS, because that’s the way I was considering. I agree that its a noble idea, and encourage you to finish, writing this in .NET, but I have to agree with sibir1us about using existing SQL Server tools. On the flip side, I am going to use this as an excuse to get back into coding so I really can't wait.

sibir1us, you mention that you can't share code but could you at least share a list what reports you have written. I would find that to be useful without the liability of sharing intellectual property. I know that GrumpyOldDBA has posted several of his reports which were quite useful as a starting point, but I'm curious what reports others have done.

Again Thanks and can't wait for Part x, y, z,
Sean

Make everything as simple as possible, but not simpler.
Albert Einstein
Post #700706
Bill Scott
Posted Monday, April 20, 2009 10:28 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Saturday, February 25, 2012 5:37 PM
Points: 174, Visits: 398
We did something like this at a previous employer. We ended up monitoring so many servers and so many processes we had a consolidated display with stoplights. Green lights for the things that went according to schedule, yellow if there was a nonfatal, and the loudest meanest red for when things went bad. Its easy to see at a glance when there is something that needs your attention. You can put the messages on tooltips or on another link drilling down by clicking the stoplight. Dedicate a monitor to the display and updated it once a minute.

Since the web site was all database driven it didn't matter what was used to create the data, just had to make the check and put it into the database. Some of our monitors were scheduled sql jobs, some were vb applications and some were in c++. They all fed the same database with status information and timestamps.

heck, we even went so far as to set up monitoring on all the monitoring programs :)

This is a GREAT idea for a busy administrator!
Post #700771
Scott Arendt
Posted Monday, April 20, 2009 10:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:45 AM
Points: 3,043, Visits: 1,255
Drew-

Great start. I am looking forward to the rest of the series.

I am always looking for tools that make monitoring SQL Server easier, especially now that I need to watch a staging environment that some people rely on. (Of course they didn't tell me until a failure happened)

Thanks,
Scott
Post #700789
Drew Salem
Posted Monday, April 20, 2009 1:44 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 11:32 PM
Points: 68, Visits: 169
I'm glad there's been interest. Got to admit, I was a bit anxious with it being my first article and all.

Andrew Peterson (4/20/2009)
Step One - get a server.

Most people need to see it to believe it. No matter what the topic.
So, rather than spend time justifying a new server, I take the 'show them' route...


A Do'er!

Andrew Peterson (4/20/2009)
Follow up:

For those not wanting to learn and/or write ASP web pages, I highly suggest you consider using SSRS as the reporting tool. You have less control with the vanilla framework, but its up and running fast. ~ But, Drew's ASP.net approach will look better and will be more impressive.


Of course it will.


sean hawkes (4/20/2009)

On the flip side, I am going to use this as an excuse to get back into coding so I really can't wait.
Sean


Wise move. I think that was originally why I decided to write them in ASP.Net. The thing with using reporting services is that there is always a possibility that 6 months or a year down the line, you think of this great report to do, but realise after a bit of research that it's just too tedious to do in SSRS or it can't be done at all because, as Andrews says it's vanilla. Over the last year many of the reports have been injected with steroids. For example, one of the articles to come is the Test Restore report, that consists of two pages. One page to set up which databases on which servers I want restoring to the Test Server, and another to tell me the outcome of restores.

Set up what we want restoring when and where to:



Last nights outcome:



In the background, we're using .Net and T-SQL to copy the databases across the network, specify which backup sets to restore and so on...

Another report is not a report at all, but an auditing page that allows me to record any work done on any server, upload attachments used etc.

Bill Scott (4/20/2009)
We did something like this at a previous employer. We ended up monitoring so many servers and so many processes we had a consolidated display with stoplights. Green lights for the things that went according to schedule, yellow if there was a nonfatal, and the loudest meanest red for when things went bad....


That's so cool. You didn't take a copy with you, did ya?

Bill Scott (4/20/2009)


heck, we even went so far as to set up monitoring on all the monitoring programs :)


Yes, to come in article Part 6! It's no good having a monitoring system if the jobs that trigger the monitoring system themselves fail.

Incidently, there are many online vb.net to c# convertors for the c#'ers, but I can supply the scripts in C# if you want.

Thanks to all, speak to you soon.

Drew
Post #700958
Thaskalas
Posted Monday, April 20, 2009 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 02, 2010 1:08 PM
Points: 1, Visits: 15
Looking good Drew! I don't have quite that many servers to contend with, but I'm always looking for ways to streamline monitoring. I've got two years of VB.NET application development under my belt, but until reading this, I hadn't given any thought to writing a central monitoring app like this. I'm looking forward to your next article.

Thanks!
Thaskalas

'When are you going to understand that being normal isn't necessarily a virtue, it rather denotes a lack of courage."
Post #701007
Marios Philippopoulos
Posted Monday, April 20, 2009 6:42 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477
Drew,

Thanks for the article. Looking forward to the upcoming installments.

When I first saw the title of your article, I actually thought it was about using Microsoft System Center Operations Manager (SCOM) for SQL Server monitoring. It seems though that, except for the first 4 letters of your acronym, and the common mandate for centralized monitoring, your approach has nothing to do with that other SCOM. What a weird coincidence!

In our organization we are using SCOM for system monitoring and we harness the reporting capabilities of SCOM for some of our reporting. For the rest of our monitoring and reporting we use SSRS and some in-house solutions.

I always find it interesting to learn about different ways of monitoring SQL environments. Every approach has its own strengths, and I think your approach's greatest strength is its versatility, something lacking to some degree in canned products like SCOM, SSRS etc.

I have always maintained that DBAs need to keep training their development muscles - myself being a former developer turned DBA some time ago. It is approaches like yours that help in this direction.

Keep up the good work!

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #701104
Drew Salem
Posted Tuesday, April 21, 2009 2:34 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 11:32 PM
Points: 68, Visits: 169
Marios Philippopoulos (4/20/2009)
Drew,

Thanks for the article. Looking forward to the upcoming installments.

When I first saw the title of your article, I actually thought it was about using Microsoft System Center Operations Manager (SCOM) for SQL Server monitoring. It seems though that, except for the first 4 letters of your acronym, and the common mandate for centralized monitoring, your approach has nothing to do with that other SCOM. What a weird coincidence!

In our organization we are using SCOM for system monitoring and we harness the reporting capabilities of SCOM for some of our reporting. For the rest of our monitoring and reporting we use SSRS and some in-house solutions.

I always find it interesting to learn about different ways of monitoring SQL environments. Every approach has its own strengths, and I think your approach's greatest strength is its versatility, something lacking to some degree in canned products like SCOM, SSRS etc.

I have always maintained that DBAs need to keep training their development muscles - myself being a former developer turned DBA some time ago. It is approaches like yours that help in this direction.

Keep up the good work!




That completely passed me. I was asking Steve Jones of SQLServerCentral.com what I should call the artcles and he suggested using the acronym. I hope people haven't thought that was deliberate

What's SCOM like to use generally?

Drew
Post #701227
Marios Philippopoulos
Posted Tuesday, April 21, 2009 7:56 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477
Drew Salem (4/21/2009)


That completely passed me. I was asking Steve Jones of SQLServerCentral.com what I should call the artcles and he suggested using the acronym. I hope people haven't thought that was deliberate

What's SCOM like to use generally?

Drew


SCOM has a painful learning curve, partly because of poor documentation. Its greatest strength is the ability to "detect" new SQL instances, databases, servers etc. and automatically add those to the monitoring process. For example, if one wants to check for failed jobs, all one has to do is write a script to configure all jobs to write to the Event Log on failure. The script is deployed ONCE and can be targetted to ALL sql instances in the environment, without having to reference them by name. Every computer on the domain, in which the SCOM agent is installed, will pick up and run that script on a pre-configured schedule and alerts will be sent out when jobs fail. The failures are also listed on the SCOM console.

The strength of your solution is primarily in the reporting aspect. SCOM is somewhat lacking there.

There are ways to also customize the type of information to gather using SCOM. I wrote an article some time ago on this subject:

http://www.sqlservercentral.com/articles/Blocking/64038/

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #701468
« Prev Topic | Next Topic »

Add to briefcase
33 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.