In a typical database production environment, it is desired that the read requests go to the slave or secondary instance of the database only if the replication is complete. Hence, there is a need for a SQL health check to query the slave/secondary instance and if replication is off by a certain amount of time, then go to the master/primary instance of database.

Microsoft SQL Server

In case of Microsoft SQL Server, the mirrored instance is in the SYNCHRONIZING state when it lags behind the principal instance, and in the SYNCHRONIZED state when it does not lag behind. The following monitor marks the mirrored instance UP only if it has synchronized completely with the primary instance.

Add Load Balancing Monitor

add lb monitor AdventureWorks_Monitor MSSQL-ECV -userName DB_User -LRTM ENABLED -destIP 172.25.86.74 -destPort 1433 -database AdventureWorks -sqlQuery "select mirroring_role_desc, mirroring_state_desc from sys.database_mirroring where database_id = DB_ID(\'AdventureWorks\')" -evalRule "MSSQL.RES.ROW(0).TEXT_ELEM(0).EQ(\"MIRROR\") && MSSQL.RES.ROW(0).TEXT_ELEM(1).EQ(\"SYNCHRONIZING\")"

Bind Monitor to SQL Service

bind service mssql_svc1 –monitorName AdventureWorks_Monitor

MySQL

In case of MySQL, the command shows slave status when executed on the slave instance provides the Seconds_Behind_Master value which is stored in column #33. To allow for a replication lag of not more than 5 seconds, the following monitor should be bound to the slave service.

Add Load Balancing Monitor

add lb monitor mysql_slave MYSQL-ECV –username root –database mysql –sqlQuery "show slave status\G" –evalrule "MYSQL.RES.ROW(0).NUM_ELEM(32).LT(6)"

Bind Monitor to SQL Service

bind service mysql_svc1 –monitorName mysql_slave