MSSQL Monitor

Problem this snippet solves:

The MS-SQL monitor (when properly configured) runs a query and compares the response to the receive string. If recvrow and recvcolumn are specified, the receive string comparison will be against only that data, rather than the full query result.

Here is a working example. (Thanks to Consultant Extraordinaire Nelson Ge for proving in the field.)

How to use this snippet:

• Follow the required procedures in the LTM Manual:
  • Installing the JDBC driver (reboot LTM after installing for best results)
  • Configuring the monitor
• Use a username that can be logged in >1x concurrently to prevent false DOWN if both LTMs in a redundant pair connect simultaneously
• Use only integers for the optional fields "recvcolumn" and "recvrow", or the monitor won’t fire at all
• Use the debug option (found in the Advanced screen) -- very helpful in troubleshooting

Note: Having a SQL DBA on hand can be quite helpful in making sense of the monitor configuration fields and debug output.

Code :

monitor sql_field_check {
   defaults from mssql 
   database "DB1" 
   debug "no" 
   password "bigip" 
   recvcolumn "1" 
   recvrow "2" 
   recv "MyDogFrankie" 
   send "select top 1 ItemID from Activity order by ActivityID" 
   username "bigip" 
}