public async Task <bool> LastConnectionTest(string serverName)
{
bool returnValue = false;
await fileDb.ReadTable(FileDb.TableName.TBL_SERVER);
TBL_SERVER_VALUE tbl_server_value = new TBL_SERVER_VALUE();
if (fileDb.TBL_SERVER.Data.ContainsKey(new TBL_SERVER_KEY {
serverName = serverName
}))
{
tbl_server_value = fileDb.TBL_SERVER.Data[new TBL_SERVER_KEY {
serverName = serverName
}];
}
string connString = string.Empty;
if (tbl_server_value != null)
{
try
{
string decryptedPassword = TranString.DecodeRijndael(
tbl_server_value.serverPassword,
LogClient.Config.Instance.GetCryptionKey());
connString = new SqlConnectionStringBuilder
{
DataSource = tbl_server_value.serverPublicIp + "," + tbl_server_value.serverPort,
UserID = tbl_server_value.serverUserId,
Password = decryptedPassword,
InitialCatalog = "master",
ConnectTimeout = 5,
}.ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = @"select top 1 'normal' as healthStatus";
cmd.CommandTimeout = 5;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
config.DatabaseValue <string>(reader["healthStatus"]);
}
reader.Close();
returnValue = true;
}
conn.Close();
log.Warn("last connection test success!");
}
}
catch (Exception ex)
{
log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
}
}
return(returnValue);
}
private SqlServerLicenseData GetSqlServerLicenseData(string serverName)
{
string batchRequestsPerSec = "";
string onlineDbCnt = "";
string cpuCnt = "";
string hyperthreadRatio = "";
string physicalMemoryKb = "";
SqlServerLicenseData sld = new SqlServerLicenseData();
try
{
AsyncHelpers.RunSync(() => fileDb.ReadTable(FileDb.TableName.TBL_SERVER));
TBL_SERVER_VALUE tbl_server_value = new TBL_SERVER_VALUE();
if (fileDb.TBL_SERVER.Data.ContainsKey(new TBL_SERVER_KEY {
serverName = serverName
}))
{
tbl_server_value = fileDb.TBL_SERVER.Data[new TBL_SERVER_KEY {
serverName = serverName
}];
}
string connString = string.Empty;
if (tbl_server_value != null)
{
try
{
string decryptedPassword = TranString.DecodeRijndael(
tbl_server_value.serverPassword,
LogClient.Config.Instance.GetCryptionKey());
connString = new SqlConnectionStringBuilder
{
DataSource = tbl_server_value.serverPublicIp + "," + tbl_server_value.serverPort,
UserID = tbl_server_value.serverUserId,
Password = decryptedPassword,
InitialCatalog = "master",
ConnectTimeout = 5,
}.ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText =
@"
set nocount on
set transaction isolation level read uncommitted
declare @pre int, @post int, @BatchRequestsPerSec int, @OnlineDbCnt int, @CpuCnt int, @HyperthreadRatio int, @PhysicalMemoryKb int
begin try
select @CpuCnt = cpu_count, @HyperthreadRatio = hyperthread_ratio, @PhysicalMemoryKb = physical_memory_kb from [sys].[dm_os_sys_info]
select @OnlineDbCnt = count(*) from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb', 'LazyLog') and state_desc = 'ONLINE'
select @pre = cntr_value from sys.dm_os_performance_counters WHERE counter_name ='Batch Requests/sec'
waitfor delay '00:00:01.000'
select @post = cntr_value from sys.dm_os_performance_counters WHERE counter_name ='Batch Requests/sec'
select @BatchRequestsPerSec = (@post - @pre)
end try
begin catch
end catch
select
cast(@BatchRequestsPerSec as varchar(100)) batchRequestsPerSec
, cast(@OnlineDbCnt as varchar(100)) onlineDbCnt
, cast(@CpuCnt as varchar(100)) cpuCnt
, cast(@HyperthreadRatio as varchar(100)) hyperthreadRatio
, cast(@PhysicalMemoryKb as varchar(100)) physicalMemoryKb
";
cmd.CommandTimeout = 5;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
batchRequestsPerSec = config.DatabaseValue <string>(reader["batchRequestsPerSec"]);
onlineDbCnt = config.DatabaseValue <string>(reader["onlineDbCnt"]);
cpuCnt = config.DatabaseValue <string>(reader["cpuCnt"]);
hyperthreadRatio = config.DatabaseValue <string>(reader["hyperthreadRatio"]);
physicalMemoryKb = config.DatabaseValue <string>(reader["physicalMemoryKb"]);
}
reader.Close();
}
conn.Close();
}
GetClusterInfo(GetServerName(), out string loadBalancerInstanceNo, out string serverInstanceNo, out string clusterName, out string publicIp, out string privateIp, out string serverRole);
sld.loadBalancerInstanceNo = loadBalancerInstanceNo;
sld.loadBalancerInstanceName = clusterName;
sld.serverName = serverName;
sld.serverInstanceNo = serverInstanceNo;
sld.privateIp = privateIp;
sld.serverRole = serverRole;
sld.batchRequestsPerSec = batchRequestsPerSec;
sld.onlineDbCnt = onlineDbCnt;
sld.cpuCnt = cpuCnt;
sld.hyperthreadRatio = hyperthreadRatio;
sld.physicalMemoryKb = physicalMemoryKb;
}
catch (Exception ex)
{
log.Error(string.Format("get licensedata error {0}, {1}", ex.Message, ex.StackTrace));
}
}
}
catch (Exception ex)
{
log.Error(string.Format("{0}, {1}", ex.Message, ex.StackTrace));
}
return(sld);
}
