public static SQLServerProperties GetSqlServerProperties(string instance, string sqlLogin, string sqlPassword, string serverType, bool azureADAuth)
{
Debug.Assert(!string.IsNullOrEmpty(instance));
// Validate input.
if (string.IsNullOrEmpty(instance))
{
logX.loggerX.Error("ERROR - no instance specified for getting SQL Server properties");
throw new ArgumentNullException("Instance is not specified");
}
instance = instance.Trim();
var result = new SQLServerProperties();
var bldr = SqlHelper.ConstructConnectionString(instance, sqlLogin, sqlPassword, serverType, azureADAuth);
using (var connection = new SqlConnection(bldr.ConnectionString))
{
connection.Open();
var isSQL2012OrHigher = IsSQL2012OrHigher(connection.ServerVersion);
var confQuery = @"select isnull(SERVERPROPERTY('HadrManagerStatus'),0) as HadrManagerStatus,
isnull(SERVERPROPERTY('MachineName'),'') as MachineName,
isnull(SERVERPROPERTY('ServerName'),'') as ServerName,
isnull(SERVERPROPERTY('InstanceName'),'') as InstanceName,
isnull(SERVERPROPERTY('Edition'),'') as Edition;";
if (isSQL2012OrHigher)
{
confQuery += @"SELECT top 1 cluster_name as HadrClusterName,
isnull(CONNECTIONPROPERTY('local_net_address'),'') as LocalNetAddress,
isnull(CONNECTIONPROPERTY('client_net_address'),'') as ClientNetAddress
FROM sys.dm_hadr_cluster;";
}
using (var rdr = SqlHelper.ExecuteReader(connection, null, CommandType.Text, confQuery, null))
{
if (rdr.HasRows && rdr.Read())
{
result.Version = connection.ServerVersion;
result.InstanceName = rdr["InstanceName"].ToString();
result.MachineName = rdr["MachineName"].ToString();
result.ServerName = rdr["ServerName"].ToString();
result.HadrManagerStatus = GetHadrManagerStatus(rdr["HadrManagerStatus"].ToString());
result.Edition = rdr["Edition"].ToString();
if (isSQL2012OrHigher &&
result.HadrManagerStatus == HadrManagerStatus.StartedAndRunning &&
rdr.NextResult() &&
rdr.HasRows &&
rdr.Read())
{
result.HadrClusterName = rdr["HadrClusterName"].ToString();
result.LocalNetAddress = rdr["LocalNetAddress"].ToString();
result.ClientNetAddress = rdr["ClientNetAddress"].ToString();
}
}
}
SqlConnection.ClearPool(connection);
}
return(result);
}
public static bool TryGetSqlServerProperties(string instance, string sqlLogin, string sqlPassword, out SQLServerProperties serverProperties, string serverType, bool azureADAuth)
{
try
{
serverProperties = GetSqlServerProperties(instance, sqlLogin, sqlPassword, serverType, azureADAuth);
return(true);
}
catch
{
logX.loggerX.WarnFormat("ERROR - while getting server properties. For instance: {0}.", instance);
serverProperties = null;
return(false);
}
}
