Why in the world would anyone need advanced health monitors for thier SQL servers and MySQL servers ? isn’t the default ping and TCP monitors good enough ?. The answer is “N0″, the default monitors can only check for the server’s IP stack being alive by performing a ping or a basic tcp connection check. Database Server health needs to be determined by executing a light weight SQL Query and only then can these servers be engaged with real-time traffic to ensure the best availability. In most cases the SQL server/MySQL tcp ports are good from a connection perspective, but the critical database server components might have failed or switched over to another server unbenounced to the clients.
Without the advanced SQL monitors, for example on SQL Server, one has to enable IIS and run a aspx scpript like the one shown below. The script determines which of the SQL servers in the mirror setup is the “Principal”. The script is then executed as part of a HTTP-ECV monitor from NetScaler which is expecting “PRINCIPAL” in the HTTP response. While this approach appears simple and comprehensive, it drags along several operational challenges. Who is going to maintain the scripts ? the DBA or the network admin ? What happens when the server is rebuilt from scratch ? who makes sure IIS is enabled and the aspx is copied in the right location? what about the possible security violations of hosting a HTTP server on the DB server?.
With NetScaler DataStreamTM any SQL query/Stored procedure can be executed and the results evaluated from a monitoring perspective for both MySQL and Microsoft SQL servers. Also none of the challenges of the IIS/aspx/http-ecv monitor approach are relevant with the Native SQL health monitors. The health monitors are part of the NetScaler config, requiring no active or passive components on the database server. These health monitors can be bound to any service TCP/HTTP/MSSQL/MYSQL.. you get the idea, right?. Here are the steps to create the SQL health monitor with NetScaler DataStreamTM . This example determines which of the SQL servers in the mirror setup is the “Principal” just like the aspx script but without all of the additional operational complexity. Now relax and enjoy the ”Simplicity is Power” approach of NetScaler DataStreamTM . This blog is a continuation from my previous post Mirror mirror on the wall who’s the best SQL Server of all ?
SQL/MySQL Health Monitors with NetScaler DataStreamTM
add db user Netscaler -password XXXXX
SQL monitor to determine “PRINCIPAL/MIRROR”
add lb monitor SQL_MIRROR MSSQL-ECV -userName Netscaler -LRTM DISABLED -database master -sqlQuery “select mirroring_role_desc from sys.database_mirroring where database_id = DB_ID(‘Auth’)” -evalRule “MSSQL.RES.ROW(0).TEXT_ELEM(0).EQ(\”PRINCIPAL\”)”
SQL monitor to check for checking the presence of SQLAgent on the server
add lb monitor sql-lb-test MSSQL-ECV -userName Netscaler -LRTM DISABLED -database master -sqlQuery “select program_name from sys.dm_exec_sessions where Program_name like \’SQLAgent%\’” -evalRule “MSSQL.RES.TYPE.EQ(OK)”
ASPX Script to be run from IIS server -> the complicated way of determining SQL server Health
NetScaler HTTP-ECV Monitor
add lb monitor http-sql-ecv HTTP-ECV -send “GET /sqlmonitor.aspx” -recv PRINCIPAL -LRTM DISABLED -downTime 5 -destPort 80
<%@ Page Language=”C#” EnableSessionState=”false” EnableViewState=”false”%>
<%@ Import namespace=”System.Data.SqlClient” %>
<%@ Import namespace=”System.Data.Odbc” %>
<script language=”C#” runat=’server’>
protected override void OnLoad(EventArgs e)
{
String status = “”;
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString =
”Driver={SQL Server};” +
“Server=localhost;” +
“DataBase=master;” +
“Uid=NetScaler;” +
”Pwd=XXXXXX;”;
try
{
conn.Open();
OdbcCommand cmd = new OdbcCommand(“select mirroring_role_desc from sys.database_mirroring where database_id = DB_ID(‘Auth’) “, conn);
OdbcDataReader reader = null;
reader = cmd.ExecuteReader();
while (reader.Read())
{
status = reader.GetValue(0).ToString();
status = status.Trim();
if (status.CompareTo(“PRINCIPAL”) == 0)
{
Context.Response.StatusCode = 200;
Context.Response.ContentType = “text/plain”;
Context.Response.Write(“PRINCIPAL”);
}
else
{
Context.Response.StatusCode = 200;
Context.Response.ContentType = “text/plain”;
Context.Response.Write(“MIRROR ” + status);
}
break;
}
}
catch(Exception ex)
{
Context.Response.Write(“\nError in connecting to the db\n” + ex.ToString());
Context.Response.StatusCode = 200;
Context.Response.ContentType = “text/plain”;
Context.Response.Write(“Failover needed .. because connection failed” + status);
}
conn.Close();
}
</script>
