SQL Monitoring

Hi!

We use SAM solarwinds. I want to set up a simple SQL monitoring and do not want to use the template Appinsight SQL .. What I want to monitor is if the SQL server service is running on the server and then I want to know if the database on the server is running or not. Very simple then. What is required, account? How do I set it up in SAM?

  • To monitor that the SQL Engine service is running you only need normal windows server credentials.

    Then I should ran some SQL query also to see that its actually responding also. What the ask is up to you but I ask how many databases that are NOT backed up last 24h. That is a good thing to ask also.

    SELECT
    d.database_id,
    DatabaseName = d.name ,
    LatetstBackUpDate = ISNULL(CONVERT(VARCHAR(30),MAX(b.backup_finish_date),120),'No backup'),
    'Days' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) / 24,
    'Hours' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) % 24
    INTO #BackupInfo
    FROM master.sys.databases d
    LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
    WHERE d.name NOT IN ('tempdb','model','ReportServerTempDB','ReportServer','master','msdb') and d.state = 0
    GROUP BY d.database_id, d.name
    HAVING ISNULL(MIN(DATEDIFF(hh,b.backup_finish_date,getdate())),999) >= 24 * 2

    IF (Select count(*) as "FirstCount" from(select b.database_id from #BackupInfo b) as FC)>0
    BEGIN
    Select
    count(*) as "Rowcount",
    t.Backupinfo
    from(
    Select
    substring(
    (
    Select ', '+bkt1.DatabaseName AS [text()]
    From #BackupInfo bkt1
    For XML PATH ('')
    )
    , 2, 1000) [Backupinfo]
    From #BackupInfo bkt2)
    as t
    group by t.backupinfo
    END
    ELSE
    Select 0

    drop table #backupinfo

    If you only want to check how many databases that are online, use this:

    Declare @nr int
    Declare @Mess varchar(30)

    select
    @nr=count(*)
    ,@Mess = 'Number of databases online'
    FROM
    sys.databases
    where state=0

    Select @NR, cast(@nr as varchar(3)) + ' databases online'

    And yes, you need an account that has permission to ask that SQL querie. Add to the SQL server and also to Orion. SQL local account or AD-account.

  • I do not really understand what you mean? But this is not in SAM Solarwinds, is it? If so, can you describe a little more how I set it up?

  • Yes, this is for SAM. I have tried to share the template for this below, hope it works.

    So in a SAM template you can have different components. For example some that monitors services, like the SQL engine service, and other components can run SQL queries against the monitored server, the "SQL Server User Experiance Monitor". There you can write your own queries for what info you want to know. 

    The template below includes:

    So both services and sql queries.

    Download the template, import it into your orion (Settings/All settings/ SAM settings/Manage templates/Import...)

    Then assign it to the node you want to monitor. You may try to use "inherit credentials from node" or choose something else.

    SQL Server Basics.apm-template

  • Thanks for your help, you are cruel!
    It seems to work, I will let it roll in a couple of days now and I will return with a definitive answer. You may not be able to do anything similar with IIS. I just want to know if the IIS service is running on a server and then I want to monitor the web pages that the server takes care of, there can be several.

  • For IIS, if you don't want to use Appinsight for IIS, it is good,  there is another template called "Internet Information Service (IIS) 10". It looks at alot also. But in there you have the service. you can disable to component you are not interested in. 

    Also you can add a http- or https-monitor to check if the website is responding. 

    I don't have a "IIS Server Basics" template like with SQL but it's easy to create. Do you want me to do that you do you want to try for yourself?

  • Yes please if you have time to create IIS templare for me i Will be very statisfied.

  • Created an IIS Server Basics template. Attached below.

    Change the http and https components to match your environment. Add more or disable as required. Probably you need to adjust the url they point to also.

    Hope it works fine for you!!

    IIS Server Basics.apm-template


  • Do you have an idea of ​​what might be wrong or what I can check?

  • If you have a SQL Named instance, the SQL service name might be different than the default. So check the service name on the node and if needed update the template if needed:

  • I'm probably a little sluggish, but if I use this template for multiple servers, should I name each SQL instance in the box marked in yellow or do I do it correctly? Do I have a server that I connect this template to that does not have a sql instance that I put there, so there will be an alarm?