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

Spread the word

    Joshua Johnson

    Joshua Johnson is a Southern California based IT professional specializing in IT Infrastructure, InfoSec, and fighting entropy.

    More Posts

    Comments are closed.