Having a good web hosting is one of the major cornerstones of success for any online website. Without the right kind of hosting service, you can never achieve the success that you are seeking for your business. A hosting...
SQL Server Monitoring
UPDATE! We've added native support for MySQL monitoring, in case that's why you're here. You can get alerts by phone call, SMS, XMPP and e-mail.
We frequently are asked if Alertra is able to monitor database servers like MySQL, Microsoft SQL Server, and Oracle directly. The idea being to check the health of the database server because if the database is down, then the website that depends on it is down too. SQL server monitoring sounds like a good idea, however, exposing your database server directly to the Internet so a third-party can access it may not be a good idea.
On possible solution is to create a web page that checks the database connection and then monitor that web page.
Verify Text
The code to test the database connection is fairly straight forward. In general, you want to create a connection to the database, and then output text to the web page: "OK" if the connection worked and "ERR" if it didn't. Of course, the actual text to use is up to you. The critical bit is when you configure the HTTP/S device on Alertra you want to set the "Verify Text" option.
The "Verify Text" option allows you to specify some text that must appear on the web page for the check to be considered successful. Typically this is used to make sure the page rendered correctly and contained all the data it was supposed to. In this case though, we can use "Verify Text" to check our SQL monitoring page for "OK" to see if the database connection worked.
Here is a simple example written in PHP:
<?php
if ( $conn = mysql_connect( "db.example.com", "jsmith", "password" ) ) {
print( "Database Connected: Yes [OK]<br>" );
mysql_close($conn);
} else {
print( "Database Connected: No [ERR]<br>" );
}
?>Really, that's all there is to it. Those few lines of code in a web page would tell you whether the database server is up or not. Similar code could be created using ASP against a Microsoft SQL Server.
In the "Verify Text" field, put the text "OK". As long as the web server can connect to the database server the text "OK" should appear on the page. If the web server fails to connect to the database server, "ERR" will be there instead causing Alertra to notify you of the problem.
Final Example
You can get more complicated with your database checking. That the database is up an accepting connections may not accurately reflect whether your application can actually get the work done it needs to have done. In the code below, I have extended the database check to allow you to check any number of conditions. The code reports on the status of each condition and then finally the status as a whole.
For each condition, the variable "final_result" is updated if that condition is in an error state. At the end of the script, the contents of "final_result" is output to the web page. This allows you to configure "Verify Text" on the Alertra device setup page to contain "ALL OK". Only the "final_result" variable output at the end can ever contain this text, so if "ALL OK" is replaced with "ERR", then Alertra will notify you that there is a problem. Then, the neat thing is, you can go to your dbstatus.php page yourself and see just which database condition is in error.
<?php
# These defines should be in their own include file
# so that if the page error's out, the database connection
# information won't be returned in the HTML. They
# are included here only for demonstration purposes.
#include( '/var/www/include/dbstatus.inc' );
define( 'DB_HOST', 'db.example.com' );
define( 'DB_USER', 'jsmith' );
define( 'DB_PW', 'password' );
$final_result = 'ALL OK';
# Maximum simultaneous connections we allow; Set this to something
# above what your normal connection level would be, but well below
# the MySQL limit on connections you have configured.
$max_connections = 700;
# Set the maximum number of aborted connections (failed logins) that
# we allow. Aborted connections are usually caused by failed
# passwords, this check might help spot a brute force attempt to gain
# access to the server.
$max_failures = 100;
# Connect to database
if ( $conn = mysql_connect( DB_HOST, DB_USER, DB_PW ) ) {
print( "Database Connected: Yes [OK]<br>" );
mysql_select_db( DB_DB, $conn );
# Too many database connections
$result = '[OK]';
$sql = "show status like 'threads_connected'";
$rows = mysql_query( $sql, $conn );
$row = mysql_fetch_array( $rows );
$threads_connected = intval( $row[1] );
if ( $threads_connected > $max_connections )
$result = '[ERR]';
print( "Connections: $threads_connected $result<br>" );
if ( $result != '[OK]' )
$final_result = '[ERR]';
# Too many connect errors, maybe brute force attack
$result = '[OK]';
$sql = "show status like 'Aborted_connects'";
$rows = mysql_query( $sql, $conn );
$row = mysql_fetch_array( $rows );
$aborted_connects = intval( $row[1] );
if ( $aborted_connects > $max_failures )
$result = '[ERR]';
print( "Connection Errors: $aborted_connects $result<br>" );
if ( $result != '[OK]' )
$final_result = '[ERR]';
mysql_close($conn);
} else {
print( "Database Connected: No [ERR]<br>" );
$final_result = "[ERR]:;
}
print( "<p>$final_result</p>" );
?>Summary
In the future, Alertra may very well offer direct protocol level SQL server monitoring. Administrators who use that sort of feature would definitely want to restrict access to the database server to only Alertra's monitoring stations. A good solution though is to implement a web page that checks the connection to the database and then use Alertra's network monitoring services to check that web page. You get the benefit of checking your database for problems and being notified by our service, without having to open your database server to the outside world.
Follow us:
