SWick

Sysadmin-by-Nature

Python script for Nagios to monitor SQL Server 2005 database mirroring
16th March 2009

Python Port of a Perl script I uploaded to NagiosExchange last year.

The script should be run on the PRINCIPAL with a read-only user. If you want to run it on the MIRROR, the user must have the Sysadmin role on it (ask Microsoft for the reason). Otherwise you get NULL.

You have to install the module pymssql manually if it's not shipped with your distro.

#!/usr/bin/python

import optparse
import pymssql
import sys

def main():

    #Connect to MSSQL Server
    try:
        con = pymssql.connect(host=host, user=user, password=password, database=database)
        cur = con.cursor()

    except TypeError:
        print 
        print "Could not connect to SQL Server"
        print 
        sys.exit(1)

    # Execute Query which checks if database is mirrored
    query="""SELECT d.name, m.mirroring_role_desc, m.mirroring_state_desc
             FROM sys.database_mirroring m
             JOIN sys.databases d ON m.database_id = d.database_id
             WHERE mirroring_state_desc IS NOT NULL AND name = """ + "'" + database + "'"

    cur.execute(query)

    results = cur.fetchall()

    for row in results:
        name  = row[0]
        role  = row[1]
        state = row[2]

    exit_val = 2

    if cur.rowcount > 0:
        if (role == "PRINCIPAL") and (state == "SYNCHRONIZED"):
            exit_val = 0

    if exit_val == 0:
        print "OK", "-", name, "-", role, "-", state
    else:
        print "CRITICAL - Check the mirrored database"

    con.close()


if __name__ == "__main__":

    # Command line Options
    parser = optparse.OptionParser()

    parser.add_option("-H", "--host",     dest="host",     metavar="HOST", help="IP or hostname with the mirrored database")
    parser.add_option("-d", "--database", dest="database", metavar="DB",   help="Name of the mirrored database")
    parser.add_option("-u", "--user",     dest="user",     metavar="USER", help="User to login")
    parser.add_option("-p", "--password", dest="password", metavar="PW",   help="Password of the user")

    if (len(sys.argv) < 2):
        args=["-h"]
        (options, args) = parser.parse_args(args)

    (options, args) = parser.parse_args()

    host     = options.host
    user     = options.user
    password = options.password
    database = options.database

    # Main function 
    main()
Tags: db, monitoring, mssql, python.

"People said I should accept the world. Bullshit! I don't accept the world." -- Stallman