StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODetecting a SQL cluster
text
Body
copied!<p>I am trying to write some code that will determine if a list of SQL servers are up. I have tried to WMI, SQLDMO, SqlDataSourceEnumerator, and Pinging port 1433 of each server, with varying degrees of success (see results below).</p> <p>Using SQLDMO and SqlDataSourceEnumerator, i found 3 out of 6, it has to be said that 2 of the 3 missing SQL servers form a cluster.</p> <p>Pinging port 1433 found 4 out of the 6, the 2 missing are the 2 servers that form the SQL cluster. </p> <p>WMI proved to be the least successful, in that it only found 1 out of 6 servers.</p> <p>Here is the code I used to prototype the server discovery:</p> <pre><code>private void buildServerMap(bool useLibCOM) { sqlServersMap = new Dictionary<string, string>(); if (useLibCOM) { //get all available SQL Servers SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass(); SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers(); ArrayList servs = new ArrayList(); for (int i = 0; i < sqlServers.Count; i++) { object srv = sqlServers.Item(i + 1); if (srv != null) { sqlServersMap.Add(srv.ToString(), srv.ToString()); } } } else { System.Data.Sql.SqlDataSourceEnumerator enumSQL = System.Data.Sql.SqlDataSourceEnumerator.Instance; System.Data.DataTable table = enumSQL.GetDataSources(); foreach (System.Data.DataRow row in table.Rows) { foreach (System.Data.DataColumn col in table.Columns) { sqlServersMap.Add((string)row[col], (string)row[col]); } } } } private bool pingSqlServer(string server) { bool success = false; TcpClient client = new TcpClient(); try { client.Connect(server, 1433); success = true; } catch (Exception) { //throw; } return success; } public StringBuilder buildWmiServerList(string path, string sqlServer, string sqlServerServiceName, StringBuilder sb, out bool reportNeeded) { reportNeeded = false; try { ManagementScope ms = new ManagementScope(path); ms.Connect(); ManagementObjectSearcher searcher = new ManagementObjectSearcher("SELECT * FROM Win32_Service WHERE Started = TRUE AND DisplayName='" + sqlServerServiceName + "'"); searcher.Scope = ms; if (searcher != null && searcher.Get() != null) { foreach (ManagementObject service in searcher.Get()) { sb.Append(sqlServer + " SQL Server service - RUNNING\n"); } } } catch (Exception e) { sb.Append(sqlServer + " SQL Server service - UNVERIFIABLE\n"); reportNeeded = true; } return sb; } </code></pre> <p><strong>Any ideas in how to resolve/detect SQL servers that form a SQL cluster?</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload