Kaseya Community

Seeking an Elegant Solution to Monitoring *ALL* SQL Server Instances' Service Status

This question is answered

Greetings!

I decided to take a new client onboarding as an opportunity to integrate Microsoft SQL Server monitoring into my "Automatic Monitoring" methdology (which uses an "Automatic_Monitoring_Detected*" and "Automatic_Monitoring_Excluded*" Custom Fields in conjunction with a daily maintenance script and Policy Management to automatically apply Monitor Sets) when I hit a snag...

Specifically, my predecessor had set up the monitor set you see below (and I've been naively running with this ever since):

 

The problem here (and yes, this hadn't "clicked" in my head until now Eek) is that many of our SQL servers that we monitor are not using the "default" MSSQLServer instance.  Because the "Update List By Scan" feature doesn't purge out (to the best of my knowledge) the Services that no longer exist on any client machines, we've amassed a great number of SQL instance names to monitor for... this is just a sample (trust me, there's 3 pages):



Naturally, the thought of building Monitor Sets for all of these makes me a very, very Saaaaad Paaaaanda, as does the fact that the service name is based on what was calculated during the discovery.

I did set up a monitor set with a manually-added Service Name of MSSQL$* but it appears that it doesn't like the wildcard (based on the fact that servers that I know are running a MSSQL$ instance still reported a Service ALARM):



So... has anyone found an elegant solution to this problem?  Might you be willing to share?

Verified Answer
All Replies
  • Have you had a look at Kaseya Network Monitor (KNM)? You can easily create a Lua script that uses either Windows Performance counters or WMI to dynamically list and check all SQL Server instances. We can help you setting up such a script if you like.

  • Tomas: Thanks for the suggestion to use KNM; however, I'm just not happy enough with the integration between it and the core Kaseya VSA.  Until such time that they share the same database and are 100% integrated, I just don't see the value proposition in trying to maintain two systems to do ostensibly what should be achieved by the VSA's "single pane of glass."  We also have a different monitoring technology currently employed that renders KNM redundant.

    LUA scripts are powerful things... but I'm sure someone has run into (and has a solution for) this problem using only the VSA... I hope... Confused



    [edited by: Brian Dagan at 12:49 PM (GMT -7) on 4-23-2012] Clarification
  • Would it work to get the live Instance name(s) either from the either Server Services or WMI then use that/those instance(s) to set your monitors?



    [edited by: zippo at 7:15 PM (GMT -7) on 4-23-2012] Fixed issue
  • It might work to pull the list of Service "short names" from WMI or sc and parse through that looking for MSSQL$*, but I'd still have to build a Monitor Set for each.  Alternatively, I could throw all of the SQL instances into a single Monitor Set and Enable Counter Matching, but if we onboard new customers (which we're doing at a pretty good clip), I would have to go back and revisit this list during every onboard.

    I think I'm going to submit a Support Ticket, just to see what a Specialist has to say... I'll update this thread when I get an answer :-)

  • Ahhh. I get it, I think. I don't know that there's an "elegant" solution and I'll be interested to hear what support has to say about it. I suppose you could set up a VBScript to take a default monitor XML file and use the "Service short names" (discovered using another VBScript, I suppose) to write out a new monitor file with the correct Instance names in the new monitor XML file but you'd still have to import it manually, I think.

  • Hi Brian,

    As it stands today with our core monitoring you cannot wildcard services in Service checks.  The only supported way of handling what you are trying to achieve is to create and then subsequently manage a Monitor Set that contains all instances of SQL Server services that exist within your supported machine base.  As you point out, as new SQL Server instances are discovered via the Update List By Scan process, you will need to add them to the Monitor Set.  All SQL Server services I believe run from the binary sqlserver.exe, so alternatively you could use a Process check for sqlserver.exe.  While not quite the same as a Service check, it would at least let you know an up/down status of that process.  The only problem with the Process check, would be if a single machine was running multiple instances of SQL Server (i.e. multiple instances of sqlserver.exe running).  I recommend that you submit this as a Feature Request via our normal Support Ticket process if you would like this to be considered for inclusion in a future version of our core monitoring capability.

    Regards,

    Matt Warburton

    Kaseya Professional Services

  • Matt Warburton
    I recommend that you submit this as a Feature Request via our normal Support Ticket process if you would like this to be considered for inclusion in a future version of our core monitoring capability.

    Did that back in the first week of May Stick out tongue

    CS102815

    Thanks for the follow-up.  I think, worst case, I'll have to do what you describe and take the couple hours to either:
    A) Figure out the SQL INSERT statement to bulk-add the SQL$* instances to the Counter-Matched Monitor Set in the database -or-
    B) Manually add each instance, one by one, to a Counter-Matched Monitor Set -or-
    C) Export an existing monitor set, then tweak copy/paste XML blocks to add all Update Lists By Scan Services into the XML document, then re-import it as a Counter-Matched Monitor Set

    In all seriousness though, if you guys manage to figure out a wildcard option...

  • I have a VBScript and Kaseya Procedure that will take a default SQL Service monitor XML file and modify it to add all SQL Server Instances on a computer then will send an email indicating that a new Monitor file is available for import. It might take a few simple mods to meet your needs but you're welcome to it if you think it would help you, Brian. I've tested it as much as possible on my systems but can't guarantee it on yours, of course, but it might be worth a try.

  • Zippo,

    Thanks for the idea... I definitely like the approach.  I went a slightly different route... I had already been playing around trying to find all of the MSSQL$_____ instances in the database and wound up looking at MonitorTab/editLists.asp to determine the SQL query I'd need in order to extract a list of everything that was found in Update Lists By Scan.

    The rest of it was just SQL... and for all of the DBAs out there, yes, I'm new to SQL, so try not to mock this too much Embarrassed

    -- SQL Query: Monitoring - Create Monitor Set To Import With All MSSQL$ SQL Instances    
    -- Author: Brian Dagan of Connected WorkPlace Solutions (bdagan@cwps.com & brian@briandagan.com)    
    -- Disclaimer: Use at your own risk, no warranties expressed or implied, batteries not included    
    -- Purpose: Output an importable XML monitor set of all SQL instances in the ksubscribers DB    
    -- Usage: In the Management Studio, change the output to comma-delimited text, then run against    
    --                                the ksubscribers database (not Master)    
    -- Output: Copy-paste it into a new XML document and import it as a Monitor Set    

    -- Declare the variables    
    DECLARE @MonitorSetName VARCHAR(100)    
    DECLARE @MonitorSetDescription VARCHAR(1000)    
    DECLARE @XMLHeader VARCHAR(MAX)    
    DECLARE @XMLFooter VARCHAR(MAX)    
    DECLARE @ServiceName VARCHAR    
    DECLARE @RestartAttempts INT    
    DECLARE @RestartInterval INT    
    DECLARE @RearmTime INT    

    -- Do not display the row count    
    SET NOCOUNT ON    

    -- Populate all values below, and note that the Restart & Rearm values are in seconds    
    SELECT @MonitorSetName = '_CWPS SQL Service'    
    SELECT @MonitorSetDescription = 'Automatically generated from all MSSQL$ instances'    
    SELECT @RestartAttempts = 0    
    SELECT @RestartInterval = 0    
    SELECT @RearmTime = 600    

    -- Define the XMLHeader based on Monitor Set parameters    
    SELECT @XMLHeader = '<?xml version="1.0" encoding="UTF-16" ?>    
    <monitor_set_definition version="1.0">    
    <MonitorSet name="' + @MonitorSetName + '" description=''' + @MonitorSetDescription + ''' enableCounterMatching=''1''>    
    <Counters>    
    </Counters>    
    <Services>'    

    PRINT @XMLHeader    

    SELECT '<Service name='''+name+    
             ''' serviceDescription='''+name+         
             ''' restartAttempts='''+CAST(@RestartAttempts AS VARCHAR)+         
             ''' restartInterval='''+CAST(@RestartInterval AS VARCHAR)+    
             ''' reArm='''+CAST(@RearmTime AS VARCHAR)+    
             '''/>'    
            FROM serviceList    
            WHERE serviceListId > 999 AND -- Mentioned on MonitorTab/editLists.asp    
            name LIKE '%MSSQL$%' AND -- Finds all MSSQL$* instances    
            name NOT LIKE '%SQLAgent%' OR -- Ignores any SQLAgent services with MSSQL$ in the name    
            name = 'MSSQLServer' -- Always include the default instance    

    -- Define the XMLFooter    
    SELECT @XMLFooter = '</Services>    
    <Processes>    
    </Processes>    
    </MonitorSet>    
    </monitor_set_definition>'    

    PRINT @XMLFooter

    You do have to make a couple changes in the SQL Management Studio in order to get usable XML output:

    The resulting imported Monitor Set includes all of the SQL instances that Update Lists By Scan had managed to discover across the entire customer base:

    What are the drawbacks to this approach?

    1) This query must be run regularly, so that new clients with newly-discovered custom SQL instance names get added to the Monitor Set
    2) If you use Policy Management to deploy the Monitor Set, you'll need to delete the old Monitor Set, import the new one, then add the new one to the Policy

    So yes, I'd still be thrilled if Kaseya could include a wildcard option in the service name field so that all of this song-and-dance wouldn't be necessary, but for now, this is the best I've got Smile  Hope this helps out other folks in the same predicament!

  • Sitting laughing at Brian's SQL - No I'm not, only kidding around.

    I like your fix but unfortunately it'd become a part time job for one of the engineers to do this with the amount of servers we have and check in each week.  I do hope that someone from the Kaseya mothership sees this and adds something similar that we can use without going round the houses.

  • Nice work, Brian, and your SQL skills are far above mine so no disparagement from me!  :-)   Thanks for sharing that idea and execution...

    One also wonders why it is that we seem to be the only ones able to enter "Product Feature Requests". One might think that if Kaseya ran across a good idea for an improvement to the functionality to their product (such as your suggestion) that they would be able to enter the request themselves instead of telling us to do it for them.... Very strange...

  • Guess what?

     Big Smile It's in 6.3 Big Smile

    From the 6.3 Help file:  http://help.kaseya.com/webhelp/en/vsa/6030000/index.htm#2865.htm 

     

    To whomever the Kaseyan was who made this happen, THANK YOU!

  • Awesome. I just peed a little.

  • Thank you indeed for this answer!

    These are the solutions we are looking for.