PART 2!!! Status of MSSQL Server Mirrored DB’s!
// February 7th, 2013 // Uncategorized
Right, So please allow me to preface this with some background on the last 4 months. It’s been ridiculously crazy, busy! With that said, lets get on to a post that I had wanted to write some months ago but just didn’t have the time.
Back in September I had made a quick posting of a powershell script to check the status of MSSQL server replication. I apologize for not providing some additional details, it’s use case, etc. Suffice it to say, IT’S NOT A CORNER CASE. (We’ll get into that in just a bit.)
Below is the C# .Net code for an IIS driven, healthcheck for detecting the current status of an MSSQL replicated cluster.
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataReader rdr = null;
protected void Page_Load(){
try {
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn1"].ToString()))
{
SqlCommand cmd = new SqlCommand("SELECT mirroring_role_desc, mirroring_state_desc FROM sys.database_mirroring WHERE database_id = db_id('dbFoo')", cn);
cn.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (rdr.Read()) {
Response.Write("{" + (char)34);
Response.Write("role" + (char)34 +"=");
Response.Write(rdr.GetValue(0));
Response.Write("},{" + (char)34);
Response.Write("state" + (char)34 +"=");
Response.Write(rdr.GetValue(1));
Response.Write("}");
}}} finally {
if(rdr != null)
rdr.Close();
if(cn != null)
cn.Close();
}
}
</script>
Right, so back to the use case. Load balancing MSSQL server for HA and redundancy.
http://www.ijoshuajohnson.com/?p=200
