需求描述
一般在生產(chǎn)環(huán)境中，有時(shí)候需要自動(dòng)的檢測(cè)指標(biāo)值狀態(tài)，如果發(fā)生異常，需要提前預(yù)警的，比如發(fā)郵件告知，本篇就介紹如果通過Power shell實(shí)現(xiàn)狀態(tài)值監(jiān)控。
監(jiān)控值范圍
根據(jù)經(jīng)驗(yàn)，作為DBA一般需要監(jiān)控如下系統(tǒng)能行指標(biāo)。
cpu：
Processor(_Total)% Processor Time
Processor(_Total)% Privileged Time
SQLServer:SQL StatisticsBatch Requests/sec
SQLServer:SQL StatisticsSQL Compilations/sec
SQLServer:SQL StatisticsSQL Re-Compilations/sec
SystemProcessor Queue Length
SystemContext Switches/sec
Memory:
MemoryAvailable Bytes
MemoryPages/sec
MemoryPage Faults/sec
MemoryPages Input/sec
MemoryPages Output/sec
Process(sqlservr)Private Bytes
SQLServer:Buffer ManagerBuffer cache hit ratio
SQLServer:Buffer ManagerPage life expectancy
SQLServer:Buffer ManagerLazy writes/sec
SQLServer:Memory ManagerMemory Grants Pending
SQLServer:Memory ManagerTarget Server Memory (KB)
SQLServer:Memory ManagerTotal Server Memory (KB)
Disk:
PhysicalDisk(_Total)% Disk Time
PhysicalDisk(_Total)Current Disk Queue Length
PhysicalDisk(_Total)Avg. Disk Queue Length
PhysicalDisk(_Total)Disk Transfers/sec
PhysicalDisk(_Total)Disk Bytes/sec
PhysicalDisk(_Total)Avg. Disk sec/Read
PhysicalDisk(_Total)Avg. Disk sec/Write
SQL Server:
SQLServer:Access MethodsFreeSpace Scans/sec
SQLServer:Access MethodsFull Scans/sec
SQLServer:Access MethodsTable Lock Escalations/sec
SQLServer:Access MethodsWorktables Created/sec
SQLServer:General StatisticsProcesses blocked
SQLServer:General StatisticsUser Connections
SQLServer:LatchesTotal Latch Wait Time (ms)
SQLServer:Locks(_Total)Lock Timeouts (timeout > 0)/sec
SQLServer:Locks(_Total)Lock Wait Time (ms)
SQLServer:Locks(_Total)Number of Deadlocks/sec
SQLServer:SQL StatisticsBatch Requests/sec
SQLServer:SQL StatisticsSQL Re-Compilations/sec
上述指標(biāo)含義，可以參照我上一篇文章：SQL Server需要監(jiān)控哪些計(jì)數(shù)器
監(jiān)控腳本
$server = "(local)"
$uid = "sa"
$db="master"
$pwd="password"
$mailprfname = "SendEmail"
$recipients = ""
$subject = "數(shù)據(jù)庫指標(biāo)異常了！"
$computernamexml = "f:computername.xml"
$alter_cpuxml = "f:alter_cpu.xml"
function GetServerName($xmlpath)
{
$xml = [xml] (Get-Content $xmlpath)
$return = New-Object Collections.Generic.List[string]
for($i = 0;$i -lt $xml.computernames.ChildNodes.Count;$i++)
{
if ( $xml.computernames.ChildNodes.Count -eq 1)
{
$cp = [string]$xml.computernames.computername
}
else
{
$cp = [string]$xml.computernames.computername[$i]
}
$return.Add($cp.Trim())
}
$return
}
function GetAlterCounter($xmlpath)
{
$xml = [xml] (Get-Content $xmlpath)
$return = New-Object Collections.Generic.List[string]
$list = $xml.counters.Counter
$list
}
function CreateAlter($message)
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$CnnString ="Server = $server; Database = $db;User Id = $uid; Password = $pwd"
$SqlConnection.ConnectionString = $CnnString
$CC = $SqlConnection.CreateCommand();
if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() }
$cc.CommandText=" EXEC msdb..sp_send_dbmail
@profile_name = '$mailprfname'
,@recipients = '$recipients'
,@body = '$message'
,@subject = '$subject'
"
$cc.ExecuteNonQuery()|out-null
$SqlConnection.Close();
}
$names = GetServerName($computernamexml)
$pfcounters = GetAlterCounter($alter_cpuxml)
foreach($cp in $names)
{
$p = New-Object Collections.Generic.List[string]
$report = ""
foreach ($pfc in $pfcounters)
{
$b = ""
$counter ="\"+$cp+$pfc.get_InnerText().Trim()
$p.Add($counter)
}
$count = Get-Counter $p
for ($i = 0; $i -lt $count.CounterSamples.Count; $i++)
{
$v = $count.CounterSamples.Get($i).CookedValue
$pfc = $pfcounters[$i]
#$pfc.get_InnerText()
$b = ""
$lg = ""
if($pfc.operator -eq "lt")
{
if ($v -ge [double]$pfc.alter)
{$b = "alter"
$lg = "Greater Than"}
}
elseif ($pfc.operator -eq "gt")
{
if( $v -le [double]$pfc.alter)
{$b = "alter"
$lg = "Less Than"}
}
if($b -eq "alter")
{
$path = "\"+$cp+$pfc.get_InnerText()
$item = "{0}:{1};{2} Threshold:{3}" -f $path,$v.ToString(),$lg,$pfc.alter.Trim()
$report += $item + "`n"
}
}
if($report -ne "")
{
#生產(chǎn)警告 參數(shù) 計(jì)數(shù)器，閥值，當(dāng)前值
CreateAlter $report
}
}
其中涉及到2個(gè)配置文件：computernamexml,alter_cpuxml分別如下：
<computernames>
<computername>
wuxuelei-pc
</computername>
</computernames>
<Counters>
<Counter alter = "10" operator = "gt" >Processor(_Total)% Processor Time</Counter>
<Counter alter = "10" operator = "gt" >Processor(_Total)% Privileged Time</Counter>
<Counter alter = "10" operator = "gt" >SQLServer:SQL StatisticsBatch Requests/sec</Counter>
<Counter alter = "10" operator = "gt" >SQLServer:SQL StatisticsSQL Compilations/sec</Counter>
<Counter alter = "10" operator = "gt" >SQLServer:SQL StatisticsSQL Re-Compilations/sec</Counter>
<Counter alter = "10" operator= "lt" >SystemProcessor Queue Length</Counter>
<Counter alter = "10" operator= "lt" >SystemContext Switches/sec</Counter>
</Counters>
其中 alter 就是閥值，如第一條，如果 閥值 > 性能計(jì)數(shù)器值，就會(huì)發(fā)出警告。
其實(shí)這種自定義配置的方式，實(shí)現(xiàn)了靈活多變的自動(dòng)化監(jiān)控標(biāo)準(zhǔn)：
1、比如可以檢測(cè)磁盤空間大小
2、檢測(cè)運(yùn)行峰值狀態(tài)
3、定時(shí)的根據(jù)歷史運(yùn)行值，更改生產(chǎn)系統(tǒng)中的閥值大小，也就是所謂的運(yùn)行基線
警告實(shí)現(xiàn)方式
1、SQL Agent配置Job方式實(shí)現(xiàn)
2、計(jì)劃任務(wù)
以上兩種配置方式，可以靈活掌握，操作還是蠻簡單的,如果不會(huì)，可自行g(shù)oogle。當(dāng)然，如果不想干預(yù)正常的生產(chǎn)系統(tǒng)，可以添加一個(gè)Server專門用來自動(dòng)化運(yùn)維檢測(cè)來用，實(shí)現(xiàn)遠(yuǎn)程監(jiān)控。
后續(xù)文章中會(huì)分析關(guān)于Power Shell的遠(yuǎn)程調(diào)用，并且能實(shí)現(xiàn)事故當(dāng)前狀態(tài)下，自動(dòng)化截圖….自動(dòng)Send Email……為DBA現(xiàn)場(chǎng)取證第一手材料…方便診斷問題…
效果圖如下
以上只提供實(shí)現(xiàn)方式，如需要內(nèi)容更新，自己靈活更新。