Kim tra SQL Server bng Windows
PowerShell – Phn 3
Phn 1 ca lot bài này ã gii thiu cho các bn kim tra u tiên v SQL Server—cách ping
mt host. Trong phn 2 là gii thiu v cách kim tra tt c các dch v ca Windows có liên
quan vi SQL Server. Trong phn 3 này chúng tôi s gii thiu v cách tìm mt s thông tin v
phn cng và h iu hành t máy host.
Bc 1
ánh hoc Copy và paste on mã bên di vào file C:\CheckSQLServer\Checkhardware.ps1.
#Function to check Hardware information from a host
Function checkhardware([string] $Hostname )
{
$computer=get-wmiobject -class win32_computersystem -computername
$hostname -errorvariable errorvar
$errorvar.size
if (-not $errorvar)
{
$message="Host="+$Hostname
write-host $message -background "GREEN" -foreground "BLACk"
$message="Description=" +$computer.Description
write-host $message -background "GREEN" -foreground "BLACk"
$message="NumberOfLogicalProcessors="+
$computer.NumberOfLogicalProcessors
write-host $message -background "GREEN" -foreground "BLACk"
$message="NumberOfProcessors="+ $computer.NumberOfProcessors
write-host $message -background "GREEN" -foreground "BLACk"
$message="TotalPhysicalMemory=" +$computer.TotalPhysicalMemory
write-host $message -background "GREEN" -foreground "BLACk"
$message="Model=" +$computer.Model
write-host $message -background "GREEN" -foreground "BLACk"
$message="Manufacturer=" +$computer.Manufacturer

$message= "Version="+$os.Version
write-host $message -background "GREEN" -foreground "BLACk"
$message= "WindowsDirectory="+$os.WindowsDirectory
write-host $message -background "GREEN" -foreground "BLACk"
$message= "PlusVersionNumber="+$os.PlusVersionNumber
write-host $message -background "GREEN" -foreground "BLACk"
$message= "FreePhysicalMemory="+$os.FreePhysicalMemory
write-host $message -background "GREEN" -foreground "BLACk"
$message= "FreeSpaceInPagingFiles="+$os.FreeSpaceInPagingFiles
write-host $message -background "GREEN" -foreground "BLACk"
$message= "FreeVirtualMemory="+$os.FreeVirtualMemory
write-host $message -background "GREEN" -foreground "BLACk"
$message= "PAEEnabled="+$os.PAEEnabled
write-host $message -background "GREEN" -foreground "BLACk"
}
}
Bc 3
Gn thêm vào file C:\CheckSQLServer\CheckSQL_Lib.ps1 on mã bên di.
. ./checkhardware.ps1
. ./checkOS.ps1
Lúc này file C:\CheckSQLServer\CheckSQL_Lib.ps1 s có pinghost, checkservices,
checkhardware và checkOS nh th hin bên di.
#Source all the functions relate to CheckSQL
. ./PingHost.ps1
. ./checkservices.ps1
. ./checkhardware.ps1
. ./checkOS.ps1
Lu ý: File CheckSQL_Lib.ps1 này s c cp nht t các kch bn mi nh
checkhardware.ps1 và checkOS.ps1
Bc 4

Lu ý: File CheckSQLServer.ps1 này s c cp nht các iu kin và các tham s mi trong
các phn sau ca lot bài.
Ngun dn s load các hàm c lit kê trong file kch bn và làm cho nó hin hu trong toàn b
PowerShell session. Trong trng hp này, chúng tôi dn ngun mt kch bn, kch bn này s
li ly ngun t nhiu kch bn khác.
Bc 5
Lúc này chúng ta hãy thc thi kch bn, CheckSQLServer.ps1 bng cách passing “Powerpc” host
vi t cách mt i s nh th hin bên di.
./CheckSQLServer.ps1 PowerServer2
Khi ó kt qu cho ra s nh sau (tham kho hình 1.0)
Checking SQL Server

Arguments accepted : PowerServer2

Pinging the host machine

PowerServer2 is REACHABLE
Checking windows services on the host related to SQL Server

Host=PowerServer2 MSSQLSERVER Running OK True .\Administrator
Host=PowerServer2 MSSQLServerADHelper100 Stopped OK False NT
AUTHORITY\NETWORK SERVICE
Host=PowerServer2 MSSQLServerOLAPService Stopped OK False .\Administrator
Host=PowerServer2 SQLBrowser Stopped OK False NT AUTHORITY\LOCAL SERVICE
Host=PowerServer2 SQLSERVERAGENT Stopped OK False .\Administrator
Host=PowerServer2 SQLWriter Stopped OK False LocalSystem
Checking hardware Information

Host=PowerServer2
Description=AT/AT COMPATIBLE

Kt qu
Checking SQL Server

Arguments accepted : TestMachine

Pinging the host machine

TestMachine is NOT reachable
Checking windows services on the host related to SQL Server

Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
At C:\checksqlserver\checkservices.ps1:5 char:24
+ $Services=get-wmiobject <<<< -class win32_service -computername $hostname| where
{$_.name -like '*SQL*'}| select-obj
ect Name,state,status,Started,Startname,Description
Host=TestMachine
Checking hardware Information

Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
At C:\checksqlserver\checkhardware.ps1:5 char:24
+ $computer=get-wmiobject <<<< -class win32_computersystem -computername $hostname -
errorvariable errorvar
Checking OS Information

Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
At C:\checksqlserver\checkOS.ps1:5 char:18
+ $os=get-wmiobject <<<< -class win32_operatingsystem -computername $hostname -
errorvariable errorvar

Hình 1.1

Write-host "Arguments accepted : $Hostname"
write-host " "
Write-host "Pinging the host machine"
write-host " "
pinghost $Hostname
if ($global:errorvar -ne "host not reachable")
{
Write-host "Checking windows services on the host related to SQL Server"
write-host " "
checkservices $Hostname
Write-host "Checking hardware Information "
Write-host " "
checkhardware $Hostname
Write-host "Checking OS Information "
Write-host " "
checkOS $Hostname
}
Lúc này hãy thc thi kch bn bng cách passing tên máy “testmachine”, tên thc s không tn
ti vi t cách mt i s.
./CheckSQLServer.ps1 TestMachine
Kt qu
Checking SQL Server

Arguments accepted : TestMachine

Pinging the host machine

TestMachine is NOT reachable
Lu ý bn có th download các an mã mi nht trong phn ba ti ây.
Kt lun