How to monitor sql server switch to mirror database

[Frédéric GRANAT]

Hi,
We've got two databases in mirroring (a main database and a mirror database).
We need to monitor when there's a switch to the mirror database.

How could I do that ?

Frederic

[abrist]

Are these dbs mysql, or mssql, or something else?

[Frédéric GRANAT]

Hi,
Microsoft SQL Server please

[belvdr]

I would suggest writing a SQL Server query monitor. Query sys.database_mirroring (http://technet.microsoft.com/en-us/libr ... 78655.aspx) to determine each server's state.

Code: Select all

SELECT mirroring_role FROM sys.database_mirroring

The principal should return 1 and the mirror(s) should return 2.

Alternatively, configure SQL Server to send out alerts directly: http://technet.microsoft.com/en-us/libr ... 66392.aspx

EDIT: It looks like my last edit was removed. You can modify the query above to query individual databases:

Code: Select all

SELECT mirroring_role FROM sys.database_mirroring WHERE database_id = DB_ID('<database name>');

[lmiltchev]

Frédéric GRANAT, let us know if belvdr's suggestion helped you solve your issues.

[Frédéric GRANAT]

Hi,
I would like to run check_mssql using "Windows authentication" and not "SQL Server Authentication" please tell me how to proceed.

Regards,

Frederic

[lmiltchev]

You are not going to be able to use "Windows authentication" with this plugin. The issue has been discussed in the past on the forum:

https://support.nagios.com/forum/viewto ... 0&start=10

I am not sure if there is another plugin the Nagios Exchange that can offer you this functionality. I haven't seen one, yet.

[Frédéric GRANAT]

Hi,
I created an "sa" account.
The check works correctly but the "Test Check Command" in Nagiosxi doesn't work :
COMMAND: /usr/local/nagios/libexec/check_mssql -H xxxxxxxxxx -U sa -P xxxxxxxxxx -d xxxxxxxx -q "SELECT mirroring_role FROM sys.database_mirroring WHERE database_id = DB_ID\(\'xxxxx\'\)" -r 1
OUTPUT: CRITICAL: Could not execute the query.

Here's $arg1$ : -H xxxxxxxxxx -U sa -P xxxxxxxxxx -d xxxxxxxx -q "SELECT mirroring_role FROM sys.database_mirroring WHERE database_id = DB_ID('xxxxx')" -r 1

If I run the $arg1$ via ssh running : /usr/local/nagios/libexec/check_mssql -H xxxxxxxxxx -U sa -P xxxxxxxxxx -d xxxxxxxx -q "SELECT mirroring_role FROM sys.database_mirroring WHERE database_id = DB_ID('xxxxx'), it works.

We can conclude that, when clicking on "Test Check Command", Nagiosxi add serveral "\", that makes the query fail.

How can I solve that problem ?

Rgds,

Frederic

[hsmith]

The test check command button has a lot of issues with the way that it handles special characters. If you're going to run a test, the true way to test it is by running it from the command line. We're hoping to see improved functionality on the test button in the future, but it's not reliable at the moment.

[Frédéric GRANAT]

Hi,
Thanks, you can close the post.

Frederic