Monitor for mssql HA (always on)

[s.wiki]

Hi,
May I know is there any monitoring plugin for mssql always on?
I have google but it seems like only this https://support.nagios.com/forum/viewto ... 07&p=75487.
May I know is the script safe to work?
Thanks

[bolson]

Hello s.wiki,

Third party or user written scripts are beyond the purview and scope of us here at Nagios. I don't see anything in the script that appears to be unsafe. However, if your MSSQL installation is mission critical, you might consider setting up a test/dev environment to perform some testing before you deploy. Let me know if this answers your question and if we may close this thread.

Thank you!

[s.wiki]

Hi,
I am still trying to do the powershell script(form the link above) on the always-on server.
I am not familiar with any database at all.

But here is what i am trying to do, correct me if im wrong , or any ideas are welcome.
1. set up the checkexternalscript in the windows server, then add the powershell script.
3. check from nagios server, using nrpe -H ip -c customscript

From the link attached, other users commented on this : https://docs.microsoft.com/en-us/sql/da ... rTblshtAGs

Important

You must have CONNECT, VIEW SERVER STATE, and VIEW ANY DEFINITION permissions to execute these cmdlets.

I have created a whoami script and by using my nagios server to check, the return output is
nt authority\system| ,which i assume this is the user who will execute the powershell script.

The Database team has granted access to a specific database user 'nagios', but at the moment the credentials logged in to the windows / powershell is a different credential.
Thus running the script manually has no database access.
Is it possible to switch to user 'nagios' in the powershellscript?


Correct me if i am wrong from the beginning >.<

Thanks

[cdienger]

Have you taken any steps to verify that the script works first on the windows machine? It's always best to start with a script that works on the client machine before trying to run it with npre. Also, you can try running the commands manually. What happens on the windows machine when you run:

Code: Select all

cd SQLSERVER:\SQL\"SERVERNAME"\"SQLINSTANCE"\AvailabilityGroups

or

Code: Select all

Test-SqlAvailabilityGroup "AVAILGROUPNAME"

*replace the variables with your environment info*

from the command line? Are you prompted to login with sql credentials? It's not clear to me where/if you'd get prompted to use sql credentials.

[s.wiki]

Have you taken any steps to verify that the script works first on the windows machine? It's always best to start with a script that works on the client machine before trying to run it with npre. Also, you can try running the commands manually. What happens on the windows machine when you run:

Code: Select all

cd SQLSERVER:\SQL\"SERVERNAME"\"SQLINSTANCE"\AvailabilityGroups

or

Code: Select all

Test-SqlAvailabilityGroup "AVAILGROUPNAME"

*replace the variables with your environment info*

from the command line? Are you prompted to login with sql credentials? It's not clear to me where/if you'd get prompted to use sql credentials.

Hi, the script was not able to run properly on the local windows machine, i suspected it was the user permission thats why i tried to execute it using nrpe.
Anyway, there are 2 different error when i run the script.


first error( only appear on first run)

Code: Select all

PS C:\Program Files\NSClient++\scripts\custom> C:\Program Files\NSClient++\scripts\custom\Test_HAsql.ps1
cd : Cannot find drive. A drive with the name 'SQLSERVER' does not exist.
At C:\Program Files\NSClient++\scripts\custom\Test_HAsql.ps1:1 char:1
+ cd SQLSERVER:\SQL\"${Env:ComputerName}"\DEFAULT\AvailabilityGroups
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (SQLSERVER:String) [Set-Location], DriveNotFoundException
    + FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.SetLocationCommand
 
Test-SqlAvailabilityGroup : Cannot find path 'SQLSERVER:\ITMSAGDatabaseTest' because it does not exist.
At C:\Program Files\NSClient++\scripts\custom\Test_HAsql.ps1:2 char:12
+ $Results = Test-SqlAvailabilityGroup "ITMSAGDatabaseTest" | Select HealthState
+            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (ITMSAGDatabaseTest:String) [Test-SqlAvailabilityGroup], ItemNotFoundExceptio 
   n
    + FullyQualifiedErrorId : PathNotFound,Microsoft.SqlServer.Management.PowerShell.Hadr.TestSqlAvailabilityGroupCommand
 
 - Error
 

after the first error, the default path change from C:\Program Files\NSClient++\scripts\custom> to SQLSERVER:\>, and then giving permission error.

Code: Select all

PS SQLSERVER:\> C:\Program Files\NSClient++\scripts\custom\Test_HAsql.ps1
Test-SqlAvailabilityGroup : Cannot find path 'SQLSERVER:\SQL\DCCDBFINDURTEST\DEFAULT\AvailabilityGroups\ITMSAGDatabaseTest' 
because it does not exist.
At C:\Program Files\NSClient++\scripts\custom\Test_HAsql.ps1:2 char:12
+ $Results = Test-SqlAvailabilityGroup "ITMSAGDatabaseTest" | Select HealthState
+            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (ITMSAGDatabaseTest:String) [Test-SqlAvailabilityGroup], ItemNotFoundExceptio 
   n
    + FullyQualifiedErrorId : PathNotFound,Microsoft.SqlServer.Management.PowerShell.Hadr.TestSqlAvailabilityGroupCommand
 
 - Error

Kindly assist, thanks

[cdienger]

It looks like you need to run the sqlps command first. Try running this and then the command within the script.

[s.wiki]

It looks like you need to run the sqlps command first. Try running this and then the command within the script.

Hi,
I have tested earlier ,adding sqlps at the line 1 of the script but it is not working.
my mistake on earlier post as I did it wrongly.

From the link , the op suggest to create 2 script.

1st script is : sqlps -Command "&{C:\Scripts\AvailableGroupStatus.ps1}"
2nd script is: C:\Scripts\AvailableGroupStatus.ps1

But it didnt work as expected, even when i run the 2nd script seperately.
Then I addedImport-Module "SQLPS" -DisableNameChecking at the line 1 of the 2nd script, and I think it is work as expected, however i am still not able to execute from 1st script.

[s.wiki]

Hi,
Thanks for your assistance earlier,
the issue i was facing has been solved.

however i have face another issue.

when I execute check_nrpe -H ipaddress -c check_availability,
i get my expected output but it looks like this, may i know how do i exclude the first output?


Thanks

[cdienger]

Glad to hear some progress has been made. Where exactly are you seeing that new message? What is returned when you run check_nrpe command from the command line?

[s.wiki]

Glad to hear some progress has been made. Where exactly are you seeing that new message? What is returned when you run check_nrpe command from the command line?

Hi, Kindly refer to the information below:

Code: Select all

# /usr/local/nagios/libexec/check_nrpe -H x.x.x.x -c test-sqlavailabilitygroup
C:\Program Files\NSClient++>powershell.exe -NoProfile -ExecutionPolicy Bypass -Command "& 'C:\Program Files\NSClient++\scripts\custom\Test-SqlAvailabilityGroup.ps1'"
OK: AvailabilityGroup Databases are Healthy|

Here is my nsclient.ini:

Code: Select all

[/settings/external scripts/scripts]
test-sqlavailabilitygroup=scripts\custom\batch-Test-SqlAvailabilityGroup.bat

and the batch script to call the powershell:

Code: Select all

powershell.exe -NoProfile -ExecutionPolicy Bypass -Command "& 'C:\Program Files\NSClient++\scripts\custom\Test-SqlAvailabilityGroup.ps1'"

Kindly assist