SCOME - Centralize Monitoring with ASP.NET - Part 1
-
Comments posted to this topic are about the item SCOME - Centralize Monitoring with ASP.NET - Part 1
-
I like your article. Seems interesting, and also a good idea. However, a several drawbacks could be:
1. getting that "own SQL Server "SQL_Admin"" might be a bit hard
2. how many DBAs are actually savvy .NET coders?
3. creating how MANY linked servers
Here are several topics that might bring us to a better solution:
1. SSIS works quite well for monitoring SQL Servers on the network
2. SSRS can give you the opportunity to code your own report as needed. (You can actually drown in the amount of functionality which SSIS and SSRS provide!)
Another thing: a few months back I started working as a DBA at a company, where my main task was to monitor and maintain 41 servers (development, testing, production, clusters). On that Monday morning at 9 am - my first day at work - my first self-initiated task was to setup the "Central Management Server" in SQL2008. I used this to manually overview the other 40 server for about a week until I built a SSIS package to monitor the system automatically.
Make everything as simple as possible, but not simpler.
Albert Einstein -
sibir1us (4/20/2009)
I like your article. Seems interesting, and also a good idea. However, a several drawbacks could be:1. getting that "own SQL Server "SQL_Admin"" might be a bit hard
2. how many DBAs are actually savvy .NET coders?
3. creating how MANY linked servers
Hi, thanks sibir1us. I agree getting your own server may not be easy but it's not a must, you could use an existing one. The procs aren't resource hungry at all.
Hey, you don't need to be .Net savvy as these articles will be step by step guides. It's an intro to .Net that I'm hoping will tempt DBAs to use their own existing T-SQL skills to build anything they want. With .Net the world is your oyster. And once you're building in ASP.Net, you can build .Net windows forms or anything on the framework, it's all the same. I have deliberately chosen pure .Net and purposely tried to keep away from standard SQL Server tools to monitor, as programatically .Net opens up the world to anything and everything.
SSIS and SSRS are very thorough tools and can achieve the same. But as a DBA (and not a developer) I think it's more fun using .Net. As a side project, it has taken me over a year to build this suite of reports, to cover *all* my needs, graphs, bells and all, totalling 39 reports. Maybe you could kindly share some of your reports with us. It would be good to bounce ideas.
Kind regards
Drew
-
The background I come from is as vast as .NET + SQLServer developer. I know from experience what it takes to be responsible for development in multi-tier environment. (I was lucky to work at a very small company, where I wrote all my SPs, all my C# classes, all my aspx, ascx, js, etc.) Coming from there, I know how labor-intensive it is to manage it all.
I am a naturally lazy person - i.e. if the path of least resistence leads to the same quality of a result, why not take it?
In other words, 1 year of development seems a lot. No offence. I am sure that your system works very well, and also, your intention to push DBAs to the .NET abyss seems reasonable.
It took me 2 weeks to develop and test an SSIS package which iterates through a dynamic list of servers and collects the data from them, and then it took me another week to develop SSRS reports.
Regarding the sharing of the reports, I am not quite sure, since they were developed exclusively for the company I work for, and hence they are owned by them. At a later time, maybe I can post an article with some general directions of my development work.
Make everything as simple as possible, but not simpler.
Albert Einstein -
Promising start ... eagerly awaiting more ... this is one DBA that doesn't mind diving into .Net, as long as its C# 😉
Also, for the record, the most servers I've monitored at one time was about 20 odd. They were all setup as target servers to one master server. The master server being MY SQL Server 😀 From my server I was able to monitor failed jobs across all servers and push out changes to jobs. All alerts from the servers were forwarded back to my server as well. And thats were any alert emails were generated from.
The one addition I would have liked time to develop was an internet facing web interface, for those times that I couldn't use the corporate VPN and RDP in for a status check, but politics and workload always got in the way :rolleyes: :satisfied:
--------------------
Colt 45 - the original point and click interface
-
philcart (4/20/2009)
...as long as its C# 😉Oh no!!! 🙂 I could provide c# scripts too, but I think the articles have already been lined up for publishing. I thought for those new to this, it would be more intuitive to use vb.
The one addition I would have liked time to develop was an internet facing web interface, for those times that I couldn't use the corporate VPN and RDP in for a status check, but politics and workload always got in the way :rolleyes: :satisfied:
Funny you should say that, for the purposes of the article, I was going to go through building the reports into an external DNN site, but thought it maybe a bit much at first.
Thanks for posting, Phil.
Drew
-
What is the minimum editions required of SQL and Visual Studio?
Being only a VBScript person and not a full blown .NET programmer (I can read it but not really write it to well) I would say putting 1 year into developing this top of project is reasonable, and helpful in displaying your need to the company (a.k.a. job security). A similar web app is used where I work now for monitoring Sun Solaris servers and Window Servers. I don't know how long it took the person to develop it.
How often will the articles be published (1 per day, per week, etc)?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton -
shawn.melton (4/20/2009)
What is the minimum editions required of SQL and Visual Studio?Being only a VBScript person and not a full blown .NET programmer (I can read it but not really write it to well) I would say putting 1 year into developing this top of project is reasonable, and helpful in displaying your need to the company (a.k.a. job security). A similar web app is used where I work now for monitoring Sun Solaris servers and Window Servers. I don't know how long it took the person to develop it.
How often will the articles be published (1 per day, per week, etc)?
Hi Shawn,
You know what, as far as I am aware, you can use both the free version of SQL Server (Express) and the free version of Visual Studio Web Developer edition to accomplish what we need. Why don't you download them if you haven't got them already and follow the articles. If the editions fall short, let me know, we'll find a way around the area in question.
I should have originally emphasised that it took me a year to build the suit of reports only when I could fit them in around work. There were at times weeks between writing a report. If it takes others a couple of weeks to build reports in SSMS/SSRS, I can pretty much guarantee that this isn't because of the ease of these two tools over .Net, but rather the complexity of the reports. Come to think of it, it took me at least two weeks just to figure out what reports we needed as an organisation, let alone build them. 🙂
I kid you not; VB.Net is far more intuitive than vbscript. You'll love it.
Part 2 is published on Wednesday and Part 3 on Friday. Then I think dependant on popularity, I'll be given the go ahead to publish the rest. The actual ASP.Net bit doesn't start till Part 4 though, and to the best of my knowledge there is a 4 week wait before any article is published (I submitted these in early Feb). If the wait is too long, I'll put them up on my own site, http://www.thebuddingdba.com
Thanks for writing,
Drew
-
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
-
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.
Just about every place I've gone into has at least one cast off machine.
And most have a room full of stuff. Getting the new monitoring app (or any other) off the ground takes time and usually requires few CPU cycles at first, so you can start with old hardware. And most places are more than happy to let you build a new machine from old parts - usually older workstations. So, rather than wait for a new machine, I get permission to use old parts, and if necessary, use EVAL editions to get going. Proof of concept is a wonderful approach.
Yes, you have to have everything well structured incase you need to rebuild in 6 months with a new EVAL license, until you get the real hardware, but you should have that anyway.
The more you are prepared, the less you need it.
-
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.
-
Interesting topic.
However I am a C# programmer as well, but I still look forward to reading it all the same.
-
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 -
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!
-
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
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply