public void Refresh() { try{ // can ping the server if (this.IsConnected(200)) { ServerState = State.good; //System.Console.WriteLine("ping ok"); } else { ServerState = State.fatal; return; } // can make an sql connection with the master database // Server=myServerAddress;Database=myDataBase;Trusted_Connection=True; String connectionString = "Server=" + this.name + ";Database=master;Trusted_Connection=True;"; String dmcdbs = "select a.db,b.mirroring_state,b.mirroring_role from dbo.backup_paths a inner join sys.databases c on a.db = c.name inner join sys.database_mirroring b on b.database_id = c.database_id"; String witness = "select database_name from sys.database_mirroring_witnesses"; SqlConnection connection = new SqlConnection(connectionString); { // test for witness SqlCommand command = new SqlCommand(witness , connection); SqlDataReader rs; try { command.Connection.Open(); rs = command.ExecuteReader(); } catch (Exception e) { throw e; } if (rs.HasRows) { // mark the server as a witness with a good state DefaultRole = MirroringRole.WITNESS; ServerState = State.good; } else { ServerState = State.fatal; connection.Close(); command = new SqlCommand(dmcdbs, connection); command.Connection.Open(); rs = command.ExecuteReader(); // determine the instance roles (primary, mirror , down) and update the role variable // for each database in master.dbo.backup_paths instances = new Dictionary<string, Instance>(); while (rs.Read()) { String db = rs.GetValue(0).ToString().Trim(); if (!instances.ContainsKey(db)) { instances.Add(db, new Instance(db)); } try { // can we read the role and the state from the recordset ( null values throw an exception ) instances[db].state = (MirroringState)rs.GetByte(1); instances[db].role = (MirroringRole)rs.GetByte(2); } catch (Exception e) { // exception says we don't have a mirroring context //System.Console.WriteLine(e.Message); instances[db].role = MirroringRole.INDETERMINATE; instances[db].state = MirroringState.DISCONNECTED; // mark the server as bad ( warning ) this.ServerState = State.warning; } switch (instances[db].state) { // if disconnected or null case MirroringState.DISCONNECTED: this.ServerState = State.warning; break; case MirroringState.NULL: this.ServerState = State.warning; break; default: this.ServerState = this.ServerState == State.warning?State.warning:State.good; break; } } } } // } catch (Exception e) { System.Console.WriteLine(e.Message); throw (e); } }
public Sqserver(String name ,String DefRole) { this.name = name; ServerState = State.fatal; instances = new Dictionary<string, Instance>(); DefaultRole = (String.Equals(DefRole.ToUpper(), "MIRROR")) ? MirroringRole.MIRROR : (String.Equals(DefRole.ToUpper(), "PRINCIPAL")) ? MirroringRole.PRINCIPAL : (String.Equals(DefRole.ToUpper(), "WITNESS")) ? MirroringRole.WITNESS : MirroringRole.INDETERMINATE; Refresh(); }