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