November 7, 2008 at 6:48 pm
Harold Buckner (11/7/2008)
how stupid of me. I kept going though the script and finally hit me to check the permission on the procedure. I grant execute on my db role but something must have happened and I did not read the message or something. When I went back to check it, it was missing the permissions. Once I fix it, I'm getting the event now.I'm really sorry to wast your time.
No problem at all, I'm relieved there was a simple explanation. 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 13, 2008 at 1:01 pm
I'm not convinced how truly practical or useful this really is...
I haven't used SCOM in anger yet, but have been thrown it by my manager to take a look at for SQL Monitoring. Truth is we have a wealth of monitoring tools and scripts that do the job perfectly well, without having to spend time learning to "program" a new system that, in all likelihood, will morph or disappear as Microsoft chooses...
Like many a DBA out there, I have enough on my plate without spending hours working out how to customise a tool to play nicely with SQL Server...
I'm not saying "No" here, just waving the flag of cynicism...
If SCOM 2007 is so SQL Server 2000/2005/2008 compatible, I don't expect to have to jump through hoops to get it to detect blocks and send emails... that seems like a basic task for a monitoring tool...
November 14, 2008 at 8:18 am
HarryH (11/13/2008)
I'm not convinced how truly practical or useful this really is...I haven't used SCOM in anger yet, but have been thrown it by my manager to take a look at for SQL Monitoring. Truth is we have a wealth of monitoring tools and scripts that do the job perfectly well, without having to spend time learning to "program" a new system that, in all likelihood, will morph or disappear as Microsoft chooses...
Like many a DBA out there, I have enough on my plate without spending hours working out how to customise a tool to play nicely with SQL Server...
I'm not saying "No" here, just waving the flag of cynicism...
If SCOM 2007 is so SQL Server 2000/2005/2008 compatible, I don't expect to have to jump through hoops to get it to detect blocks and send emails... that seems like a basic task for a monitoring tool...
It's true that SCOM in its current version still leaves much to be desired, at least as far as I am concerned.
Having said that, SCOM 2007 it is a big improvement over its previous version, MOM 2005, and that's encouraging for the future. There are a lot of SQL monitoring features that come out-of-the-box, and database blocking is one of them.
So the issue is not that monitoring of database blocking is not available, but that the standard functionality out-of-the-box is not sufficient if one wants detailed information on the blocking event plus the ability to archive the blocking-event info in a table for later analysis.
That's where customization comes in. Arguably, one doesn't need SCOM, or any other monitoring tool for that matter, to monitor blocking and archive the information. This can be done simply through SQL scripting in every single instance that one needs to monitor. But think of the situation where one needs to monitor 50 or 100 SQL instances. Think of the effort involved in deploying the script 100 times (once on every instance), and the effort required to make changes and maintenance on that script during its lifecycle. Or, if one does it in a smarter way, think of the effort required to keep a lookup table, containing info of all SQL instances that need to be monitored, up-to-date so as to use it with a tool like Integration Services to loop through the instances and apply the monitoring script in one go.
With SCOM, discovery of which SQL instances to run monitoring scripts to is simple and is done once; when a new Windows computer is added to the network. As long as the SCOM Agent has been deployed on the managed computer and the Microsoft SQL management packs have been imported, SQL instances installed on a SCOM-managed computer WILL BE MONITORED from then onwards. Of course, as with any tool, SCOM can fail too, and one needs to be proactive and monitor SCOM failures and fix them promptly. There are ways for automating that as well. But the automatic discovery and monitoring of SQL instances and databases by SCOM removes a lot of the manual work involved in making sure SQL scripts run on all instances all the time.
Regarding the effort required to learn how to customize and work with SCOM: it's true that it is a steep learning curve. Having worked it out for a few case scenarios already though, it is now child's play. I have created monitoring scripts for a variety of situations, such as monitoring configurational changes, database-mail failures, backup file sizes, long-running processes etc., and for every new scenario I just take one of my existing scripts and mould it to the new requirements within the day. I have worked with several other monitoring tools, such as IDERA and SQL Sentry, and none that I've seen provide the flexibility SCOM does to mould existing out-of-the-box functionality to fit one's needs quickly and efficiently.
The scalability, maintenability and customization SCOM provides are huge advantages in my opinion.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 14, 2008 at 2:08 pm
I appreciate the detailed reply, Marios. You certainly seem to understand the position I am in - looking at SCOM with a view to using it for some useful SQL monitoring and thinking,
"What the #$@^!???" etc...
SCOM 2007 seems like a beast... but from what you are saying -- and your article backs this up -- it is a beast that can be tamed, if approached with the right attitude.
I too have used Idera and SQL Sentry plus a number of other tools, which tend to have nice GUIs, but I have often resorted to writing my own scripts for monitoring. And yes, trying to deploy scripts to multiple database servers is a complete pain - especially when every deployment seems to require some kind of customization... so perhaps, as you are saying, the pain of the SCOM 2007 learning curve is worth it for the ability to centrally monitor multiple database servers...
Again thanks for the follow up - I have a better feel for what I am facing now and, perhaps more importantly, appreciate why I should bother... 🙂
November 15, 2008 at 8:24 am
HarryH (11/14/2008)
I appreciate the detailed reply, Marios. You certainly seem to understand the position I am in - looking at SCOM with a view to using it for some useful SQL monitoring and thinking,"What the #$@^!???" etc...
SCOM 2007 seems like a beast... but from what you are saying -- and your article backs this up -- it is a beast that can be tamed, if approached with the right attitude.
I too have used Idera and SQL Sentry plus a number of other tools, which tend to have nice GUIs, but I have often resorted to writing my own scripts for monitoring. And yes, trying to deploy scripts to multiple database servers is a complete pain - especially when every deployment seems to require some kind of customization... so perhaps, as you are saying, the pain of the SCOM 2007 learning curve is worth it for the ability to centrally monitor multiple database servers...
Again thanks for the follow up - I have a better feel for what I am facing now and, perhaps more importantly, appreciate why I should bother... 🙂
I was in your position just a few short months ago, and, believe me, a lot of cursing was involved getting SCOM to work the way I wanted... 😉
Articles like mine and others' before are meant as a roadmap for someone who is just getting into SCOM, so that their learning curve is hopefully shorter and less painful than mine.
Experimentation with various SCOM features is key, as MS has done a deplorable job documenting SCOM as a tool. It really is to their disadvantage, because had they better promoted it to the DBA community, I bet you their revenues from the product would rise drastically. Hell, they should be paying me commission, for doing the job their sales and marketing people should be doing and are not!!
Feel free to post any SCOM-related questions to this site (and this thread), and if I or one of several others here know the answer, we will respond promptly.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 7, 2009 at 1:55 pm
Hey Marios
I've been looking for something like this for a while. We don't really want to store the data for future analysis on a seperate db. Is there anyway you can modify the scripts so it doesnt include the steps to write to another db?
January 8, 2009 at 3:33 am
jesse.roberts (1/7/2009)
Hey MariosI've been looking for something like this for a while. We don't really want to store the data for future analysis on a seperate db. Is there anyway you can modify the scripts so it doesnt include the steps to write to another db?
Part of the reason of having the central db is for persisting the current status of the blocking condition so the alerts would be emitted every 10 minutes instead of every minute (the vb scripts run once a minute).
Would you mind it if alerts were created every time the scripts run, ie. every minute? If you don't mind getting alerts so frequently, I can create a version of the scripts where no central db is required.
Pls let me know.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 8, 2009 at 6:46 am
Hey Marios
Thanks for the reply. I can get operations manager to supress the alert creation using alert suppression. When creating an alert generating rule against an NT event log with we can surppess similar alerts based on a numer of different fields. So tto answer your question it would be great if yuo could modify those scripts.:D
Thanks
Jesse
January 8, 2009 at 8:57 am
jesse.roberts (1/8/2009)
Hey MariosThanks for the reply. I can get operations manager to supress the alert creation using alert suppression. When creating an alert generating rule against an NT event log with we can surppess similar alerts based on a numer of different fields. So tto answer your question it would be great if yuo could modify those scripts.:D
Thanks
Jesse
That's true, I've known about alert suppression, although have not tried it in this scenario.
If this is not an issue then, I will simply remove any code related to the central analysis repository and post the scripts here.
Will do shortly.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 8, 2009 at 9:02 am
Great thank you so much Marios. This would be a big help I've been trying to modify it myself but am a novice with sql.
Jesse
January 8, 2009 at 9:06 am
jesse.roberts (1/8/2009)
Great thank you so much Marios. This would be a big help I've been trying to modify it myself but am a novice with sql.Jesse
No problem.
The SQL part of the vb scripts will stay the same because it simply retrieves the blocking/blocked-process info.
I just need to remove all references and dependencies on the analysis db.
More shortly...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 8, 2009 at 9:35 am
Marios,
Great article. At last I found something that explains customizing SCOM monitoring from SQL. It seems that the tool is very powerful but hard to learn. I have spent so many hours trying to setup simple monitoring and it still doesn't work as I expect.
You mentioned about creating customized monitors for blocking database sessions. Do I need to do same for changing thresholds of 'Collect Database Free Space (MB)' rule of 'SQL 2005 DB' management pack. When I tried to override this rule, there is no parameter to specify alert thresholds. I wante to create custom thresholds for db and log free space for production database instances and get notified if they exceed.
Thanks,
nilesh
January 8, 2009 at 10:53 am
jesse.roberts (1/7/2009)
Hey MariosI've been looking for something like this for a while. We don't really want to store the data for future analysis on a seperate db. Is there anyway you can modify the scripts so it doesnt include the steps to write to another db?
I have modified the 2 vbs scripts so there is no central-db repository needed, and I attach the zip file here.
Pls let me know if there is a problem, as I didn't have time to test. I tried to edit carefully... 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 8, 2009 at 11:07 am
Nilesh Patel (1/8/2009)
Marios,Great article. At last I found something that explains customizing SCOM monitoring from SQL. It seems that the tool is very powerful but hard to learn. I have spent so many hours trying to setup simple monitoring and it still doesn't work as I expect.
You mentioned about creating customized monitors for blocking database sessions. Do I need to do same for changing thresholds of 'Collect Database Free Space (MB)' rule of 'SQL 2005 DB' management pack. When I tried to override this rule, there is no parameter to specify alert thresholds. I wante to create custom thresholds for db and log free space for production database instances and get notified if they exceed.
Thanks,
nilesh
Hi Nilesh,
There is a monitor - Percentage Change in DB % Used Space - targetting SQL 2005 DB engines that I have been using to alert me of changes in the DB % Used Space. I have defined the following overrides on the monitor:
Upper Threshold: 20% (from default 45%)
Lower Threshold: 10% (from default 25%)
If you want to be more specific and capture data and log space separately, I'm afraid you will need to go with your own custom solution. I don't think there is anything like the monitor above that does that out-of-the-box (could be wrong).
I think collection rules, by their nature, cannot generate alerts. They are intended for reporting. See Grant's article that I mention in this article where he grapples with a very similar problem. It might provide what you need.
Yes, SCOM is tough to get used to, and this was the idea behind this article. Once I knew how to tackle the blocking problem, I thought I had discovered the meaning of life. 🙂
And wanted to share with the world...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 8, 2009 at 12:32 pm
there already are monitors for db and log free space
DB Space Free (%) and MB
Transaction Log Space Free (%) and MB
The problem with Percentage Change in DB % Used Space is you don't know what the direction of change is.
Viewing 15 posts - 16 through 30 (of 73 total)
You must be logged in to reply to this topic. Login to reply