September 6, 2017 at 12:36 am
Hi,
I need this help as I am not much knowledged on scripting nor finding a complete solution in net .
I have seen in my previous environments where we had less no# of servers between 35 - 50 and no third party monitoring tools were available ....all these small no's of SQL servers were monitored and alerted using jobs , SP's and SSIS packages by creating utility databases in all servers to be monitored .
But when I tried to search for the complete solution so I can implement or use for small set of servers within mine could't trace a complete package or atleast minimal monitoring features package .. if you have already implemented it or have found something like in internet can you please share ?
I am basically looking for these to be monitored and alerted .
SQL Services
Jobs
Backups (with different occurrences like weekly , Daily)
Logins in Sysadmin or simply any changes in Server level (sp_configure , memory settings etc)
Adding / Dropping Db's or simply any changes @ db level
Jobs success / failure
Alerts on Blocking , latency and SQL Error logs for failed and warning messages (like above Sev 17)
etc
Thanks ,
M_Mr
September 6, 2017 at 6:03 am
Full disclosure, I work for a software vendor that makes a monitoring product, so feel free to dismiss this on that basis.
Don't try to build your own monitoring solution. It takes tons of time and effort, all of which is much better served in dealing with the real problems in your environment.
However, you can do it. For Alerts, Job success failure, backups, you should look to the functionality within SQL Agent. You can also get multi-server management set up to help with gathering metrics. Changes to the DB is going to require you to set up triggers or a secondary system to poll the system. I'd go with the secondary server polling because it will allow for the loss of databases, etc. You'll just have to build that out. Use another SQL Server instance and SQL Agent to put that together.
All this is doable, but it's a ton of labor. Honestly, your time is better spent elsewhere. One plus side, you'll know a heck of a lot more about SQL Server and automation at the end of it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 15, 2017 at 7:28 am
I am actually looking something like the one mentioned here
http://www.sqlsaturday.com/161/Sessions/Details.aspx?sid=23615
any help ? I was able to download the .zip and couldnt open as its all XML
September 15, 2017 at 9:42 am
That zip is a Powerpoint deck for the presentation. Not code.
What Russ likely does is have a number of jobs that check and monitor other servers and report back to him. As Grant mentioned, this is a lot of work to build and maintain. That's OK if you have time and are willing to work on this and its' worth it to your company. However, if you count your salary, it might not be cheaper than purchasing something like SQL Monitor from Redgate, or one of the other monitoring products from different vendors.
Disclosure: Grant and I work for Redgate.
There are articles here on the site that can help you cobble something together, but many experienced DBAs find it's not worth the time if they have other work they can be doing. If they don't, then try.
http://www.sqlservercentral.com/articles/101955/
http://www.sqlservercentral.com/articles/71388/
http://www.sqlservercentral.com/articles/2679/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply