A table tennis power machine

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.


import optparse
import pymssql
import sys

def main():

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

    except TypeError:
        print "Could not connect to SQL Server"

    # 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 + "'"


    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
        print "CRITICAL - Check the mirrored database"


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):
        (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 
Tags: db, monitoring, mssql, python.

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