Keeping Track of Virtual Databases in Windows
If you have SQL Server or Oracle databases in Microsoft Windows clustered environment, then you always want to keep virtual servers running on their preferred nodes especially if you have more than one virtual database in a cluster.
However, sometimes you may discover that database servers are not performing well and you may find that two or more virtual servers may be running on same node. This can happen if there was any error on one of the servers and virtual databases failed over to other node. Or you may have applied patches on Windows operating servers and rebooted them but forgot to failover the virtual servers to proper nodes.
You may have server monitoring system in place to alert you about any resource failovers but as an administrator you may get tons of alerts of different criticality everyday and may miss such email.
To overcome such situation, you can write a script to go after all your clustered databases and find where they are running. You can run this VBScript every morning or schedule it and send report on email, which you should not ignore.
The following script (save as c:\checkservice\checkservice.vbs) generates a report in HTML format. You can change the color scheme to your liking.
The script uses arrays to store server name and service names you want to report on. It can report on clustered or stand alone servers, the number of services on standalone will be half of clustered ones.
First entry in the array is physical server name, rest are service names.
You can add as many servers and services in the list, just make sure to change the array SQLServer and OracleServer, also change the number in WHILE LOOP as well.
This script calls WMI script to query each server in the list to query service status, so the account you login to run the script must have administrative rights on all the servers.
'Usage : Double click on the file if you are logged in with Admin account or
' Open a command window with Admin account and run cscript.exe
' checkservice.vbs
'
Const ForWriting = 2, ForReading = 1, RunningColor = "#339900", StoppedColor = "#CC0000", White = "#FFFFFF", LightGrey = "#C0C0C0"
Dim SQLServer(3,4), OracleServer(3,4), bgColor, sDisplayName
On Error Resume Next
' standalone server with MSSQL and Agent services only
SQLServer(0,0) = ("SKSQLSDB01")
SQLServer(0,1) = ("SQL Server (MSSQLSERVER)")
SQLServer(0,2) = ("SQL Server Agent (MSSQLSERVER)")
' clustered servers with named instances
SQLServer(1,0) = ("SKSQLCDB03")
SQLServer(1,1) = ("SQL Server (SKSQLCNDB03)")
SQLServer(1,2) = ("SQL Server Agent (SKSQLCNDB03)")
SQLServer(1,3) = ("SQL Server (SKSQLCNDB04)")
SQLServer(1,4) = ("SQL Server Agent (SKSQLCNDB04)")
SQLServer(2,0) = ("SKSQLCDB04")
SQLServer(2,1) = ("SQL Server (SKSQLCNDB04)")
SQLServer(2,2) = ("SQL Server Agent (SKSQLCNDB04)")
SQLServer(2,3) = ("SQL Server (SKSQLCNDB03)")
SQLServer(2,4) = ("SQL Server Agent (SKSQLCNDB03)")
' standalone server with Oracle database ORCL and listener services only
OracleServer(0,0) = ("SKORADB01")
OracleServer(0,1) = ("OracleServiceORCL")
OracleServer(0,2) = ("OracleOraDb10g_home1TNSListener")
' clustered servers Oracle FailSafe databases and listeners
OracleServer(1,0) = ("SKORACDB03")
OracleServer(1,1) = ("OracleServiceORCL03")
OracleServer(1,2) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB03")
OracleServer(1,3) = ("OracleServiceORCL04")
OracleServer(1,4) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB04")
OracleServer(2,0) = ("SKORACDB04")
OracleServer(2,1) = ("OracleServiceORCL03")
OracleServer(2,2) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB03")
OracleServer(2,3) = ("OracleServiceORCL04")
OracleServer(2,4) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB04")
Set fso = CreateObject("Scripting.FileSystemObject")
Set Logfile = fso.OpenTextFile("C:\CheckService\status.htm", ForWriting, True)
LogFile.WriteLine("<HTML>")
LogFile.WriteLine("<HEAD>")
LogFile.WriteLine("<META content=""text/html; charset=utf-8"" http-equiv=Content-Type>")
LogFile.WriteLine("<TITLE>Database Services Status</TITLE>")
LogFile.WriteLine("</HEAD>")
LogFile.WriteLine("<BODY>")
LogFile.WriteLine("<H3>SQL Server Services Status</H3>")
LogFile.WriteLine("<p style=""font-family:verdana"">")
LogFile.WriteLine("<P>")
LogFile.WriteLine("<P><TABLE BORDER=1>")
LogFile.WriteLine("<TR><TH ALIGN=LEFT>ServerName</TH><TH ALIGN=LEFT>ServiceName</TH><TH ALIGN=LEFT>Status</TH></TR>")
bgColor = White
i = 0
Do While i <= 3
strComputer = SQLServer(i,0)
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
If Err.Number <> 0 Then
LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">Error " & CStr(Err.Number) & " Occurred</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & Err.Description & "</FONT></TD></TR>")
Else
j = 0
Do While j <= 4
If IsEmpty(SQLServer(i,j)) Then
Exit Do
End If
sDisplayName = SQLServer(i,j)
Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service where DisplayName = '" & sDisplayName & "'")
For Each objService in colRunningServices
If objService.State = "Running" Then
LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & RunningColor & ">" & objService.State & "</FONT></TD></TR>")
Else
LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & objService.State & "</FONT></TD></TR>")
End If
Next
j = j + 1
Loop
End If
LogFile.WriteLine()
i = i + 1
If bgColor = White Then
bgColor = LightGrey
Else
bgColor = White
End If
Loop
LogFile.WriteLine("</TABLE>")
LogFile.WriteLine("<H3>Oracle Server Services Status</H3>")
LogFile.WriteLine("<p style=""font-family:verdana"">")
LogFile.WriteLine("<P>")
LogFile.WriteLine("<P><TABLE BORDER=1>")
LogFile.WriteLine("<TR><TH ALIGN=LEFT>ServerName</TH><TH ALIGN=LEFT>ServiceName</TH><TH ALIGN=LEFT>Status</TH></TR>")
bgColor = White
i = 0
Do While i <= 3
strComputer = OracleServer(i,0)
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
If Err.Number <> 0 Then
LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">Error " & CStr(Err.Number) & " Occurred</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & Err.Description & "</FONT></TD></TR>")
Else
j = 0
Do While j <= 4
If IsEmpty(OracleServer(i,j)) Then
Exit Do
End If
sDisplayName = OracleServer(i,j)
Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service where DisplayName = '" & sDisplayName & "'")
For Each objService in colRunningServices
If objService.State = "Running" Then
LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & RunningColor & ">" & objService.State & "</FONT></TD></TR>")
Else
LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & objService.State & "</FONT></TD></TR>")
End If
Next
j = j + 1
Loop
End If
LogFile.WriteLine()
i = i + 1
If bgColor = White Then
bgColor = LightGrey
Else
bgColor = White
End If
Loop
LogFile.WriteLine("</BODY>")
LogFile.WriteLine("</HTML>")
Logfile.close
Set oShell = WScript.CreateObject ("WSCript.shell")
oShell.run "iexplore C:\CheckService\status.htm"
Set oShell = Nothing
You can run this script by double clicking it and it generates a file status.htm and opens it with Internet Explorer.
Other option is to comment out the oShell.run line, write a batch file and schedule it to run every morning and send the report in email.
Here is the batch file checkservice.bat
@echo off
cscript.exe c:\CheckService\CheckService.vbs
cscript.exe c:\checkservice\sendmailHTML.vbs "[email protected]" "[email protected]" "Services Status Report" "c:\CheckService\status.htm" HTML
SendmailHTML.vbs is also a VBScript which sends email in HTML format using a SMTP server. Change YOUR_SMTP_SERVER_NAME with your smtp server name or IP. You can also send file attachment or plain text email with this script.
'**********************************************************************
' Usage: cscript.exe c:\checkservice\sendmailhtml.vbs "[email protected]" "[email protected]" "Subject" "HTMLfile" "HTML"
'***********************************************************************
const ForReading=1
Dim ObjArgs, MailTo, From, Subject, Message, wshshell, goFS, oFile, FileName, FileType
Set ObjArgs = WScript.Arguments
MailTo = ObjArgs(0)
From = ObjArgs(1)
Subject = ObjArgs(2)
FileName = ObjArgs(3)
if ObjArgs.Count = 5 then
FileType = ObjArgs(4)
end if
set wshshell=wscript.createobject("Wscript.Shell")
set goFs=createobject("Scripting.FileSystemObject")
'This code requires the file to exist to compile
set oFile = goFs.OpenTextFile(FileName, ForReading)
Message = oFile.ReadAll
Call SendMail(MailTo, From, Subject, Message)
oFile.Close
set wshshell = nothing
set goFs = nothing
set oFile = nothing
Function SendMail(MailTo, From, Subject, Message)
Dim iMsg
Set iMsg = CreateObject("CDO.Message")
With iMsg
.To = MailTo
.From = From
.Subject = Subject
If UCase(FileType) = "HTML" Then
.HTMLBody = Message
ElseIf UCase(FileType) = "ATTACHMENT" Then
.AddAttachment FileName
Else
.TextBody = Message
End If
End With
iMsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YOUR_SMTP_SERVER_NAME"
iMsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
iMsg.Configuration.Fields.Update
iMsg.Send
End Function
However, sometimes you may discover that database servers are not performing well and you may find that two or more virtual servers may be running on same node. This can happen if there was any error on one of the servers and virtual databases failed over to other node. Or you may have applied patches on Windows operating servers and rebooted them but forgot to failover the virtual servers to proper nodes.
You may have server monitoring system in place to alert you about any resource failovers but as an administrator you may get tons of alerts of different criticality everyday and may miss such email.
To overcome such situation, you can write a script to go after all your clustered databases and find where they are running. You can run this VBScript every morning or schedule it and send report on email, which you should not ignore.
The following script (save as c:\checkservice\checkservice.vbs) generates a report in HTML format. You can change the color scheme to your liking.
The script uses arrays to store server name and service names you want to report on. It can report on clustered or stand alone servers, the number of services on standalone will be half of clustered ones.
First entry in the array is physical server name, rest are service names.
You can add as many servers and services in the list, just make sure to change the array SQLServer and OracleServer, also change the number in WHILE LOOP as well.
This script calls WMI script to query each server in the list to query service status, so the account you login to run the script must have administrative rights on all the servers.
'Usage : Double click on the file if you are logged in with Admin account or
' Open a command window with Admin account and run cscript.exe
' checkservice.vbs
'
Const ForWriting = 2, ForReading = 1, RunningColor = "#339900", StoppedColor = "#CC0000", White = "#FFFFFF", LightGrey = "#C0C0C0"
Dim SQLServer(3,4), OracleServer(3,4), bgColor, sDisplayName
On Error Resume Next
' standalone server with MSSQL and Agent services only
SQLServer(0,0) = ("SKSQLSDB01")
SQLServer(0,1) = ("SQL Server (MSSQLSERVER)")
SQLServer(0,2) = ("SQL Server Agent (MSSQLSERVER)")
' clustered servers with named instances
SQLServer(1,0) = ("SKSQLCDB03")
SQLServer(1,1) = ("SQL Server (SKSQLCNDB03)")
SQLServer(1,2) = ("SQL Server Agent (SKSQLCNDB03)")
SQLServer(1,3) = ("SQL Server (SKSQLCNDB04)")
SQLServer(1,4) = ("SQL Server Agent (SKSQLCNDB04)")
SQLServer(2,0) = ("SKSQLCDB04")
SQLServer(2,1) = ("SQL Server (SKSQLCNDB04)")
SQLServer(2,2) = ("SQL Server Agent (SKSQLCNDB04)")
SQLServer(2,3) = ("SQL Server (SKSQLCNDB03)")
SQLServer(2,4) = ("SQL Server Agent (SKSQLCNDB03)")
' standalone server with Oracle database ORCL and listener services only
OracleServer(0,0) = ("SKORADB01")
OracleServer(0,1) = ("OracleServiceORCL")
OracleServer(0,2) = ("OracleOraDb10g_home1TNSListener")
' clustered servers Oracle FailSafe databases and listeners
OracleServer(1,0) = ("SKORACDB03")
OracleServer(1,1) = ("OracleServiceORCL03")
OracleServer(1,2) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB03")
OracleServer(1,3) = ("OracleServiceORCL04")
OracleServer(1,4) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB04")
OracleServer(2,0) = ("SKORACDB04")
OracleServer(2,1) = ("OracleServiceORCL03")
OracleServer(2,2) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB03")
OracleServer(2,3) = ("OracleServiceORCL04")
OracleServer(2,4) = ("OracleOraDb10g_home1TNSListenerFslSKORACVDB04")
Set fso = CreateObject("Scripting.FileSystemObject")
Set Logfile = fso.OpenTextFile("C:\CheckService\status.htm", ForWriting, True)
LogFile.WriteLine("<HTML>")
LogFile.WriteLine("<HEAD>")
LogFile.WriteLine("<META content=""text/html; charset=utf-8"" http-equiv=Content-Type>")
LogFile.WriteLine("<TITLE>Database Services Status</TITLE>")
LogFile.WriteLine("</HEAD>")
LogFile.WriteLine("<BODY>")
LogFile.WriteLine("<H3>SQL Server Services Status</H3>")
LogFile.WriteLine("<p style=""font-family:verdana"">")
LogFile.WriteLine("<P>")
LogFile.WriteLine("<P><TABLE BORDER=1>")
LogFile.WriteLine("<TR><TH ALIGN=LEFT>ServerName</TH><TH ALIGN=LEFT>ServiceName</TH><TH ALIGN=LEFT>Status</TH></TR>")
bgColor = White
i = 0
Do While i <= 3
strComputer = SQLServer(i,0)
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
If Err.Number <> 0 Then
LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">Error " & CStr(Err.Number) & " Occurred</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & Err.Description & "</FONT></TD></TR>")
Else
j = 0
Do While j <= 4
If IsEmpty(SQLServer(i,j)) Then
Exit Do
End If
sDisplayName = SQLServer(i,j)
Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service where DisplayName = '" & sDisplayName & "'")
For Each objService in colRunningServices
If objService.State = "Running" Then
LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & RunningColor & ">" & objService.State & "</FONT></TD></TR>")
Else
LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & objService.State & "</FONT></TD></TR>")
End If
Next
j = j + 1
Loop
End If
LogFile.WriteLine()
i = i + 1
If bgColor = White Then
bgColor = LightGrey
Else
bgColor = White
End If
Loop
LogFile.WriteLine("</TABLE>")
LogFile.WriteLine("<H3>Oracle Server Services Status</H3>")
LogFile.WriteLine("<p style=""font-family:verdana"">")
LogFile.WriteLine("<P>")
LogFile.WriteLine("<P><TABLE BORDER=1>")
LogFile.WriteLine("<TR><TH ALIGN=LEFT>ServerName</TH><TH ALIGN=LEFT>ServiceName</TH><TH ALIGN=LEFT>Status</TH></TR>")
bgColor = White
i = 0
Do While i <= 3
strComputer = OracleServer(i,0)
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
If Err.Number <> 0 Then
LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">Error " & CStr(Err.Number) & " Occurred</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & Err.Description & "</FONT></TD></TR>")
Else
j = 0
Do While j <= 4
If IsEmpty(OracleServer(i,j)) Then
Exit Do
End If
sDisplayName = OracleServer(i,j)
Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service where DisplayName = '" & sDisplayName & "'")
For Each objService in colRunningServices
If objService.State = "Running" Then
LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & RunningColor & ">" & objService.State & "</FONT></TD></TR>")
Else
LogFile.WriteLine("<TR><TD BGCOLOR=" & bgColor & ">" & strComputer & "</TD><TD BGCOLOR=" & bgColor & ">" & objService.DisplayName & "</TD><TD BGCOLOR=" & bgColor & "><FONT Color=" & StoppedColor & ">" & objService.State & "</FONT></TD></TR>")
End If
Next
j = j + 1
Loop
End If
LogFile.WriteLine()
i = i + 1
If bgColor = White Then
bgColor = LightGrey
Else
bgColor = White
End If
Loop
LogFile.WriteLine("</BODY>")
LogFile.WriteLine("</HTML>")
Logfile.close
Set oShell = WScript.CreateObject ("WSCript.shell")
oShell.run "iexplore C:\CheckService\status.htm"
Set oShell = Nothing
You can run this script by double clicking it and it generates a file status.htm and opens it with Internet Explorer.
Other option is to comment out the oShell.run line, write a batch file and schedule it to run every morning and send the report in email.
Here is the batch file checkservice.bat
@echo off
cscript.exe c:\CheckService\CheckService.vbs
cscript.exe c:\checkservice\sendmailHTML.vbs "[email protected]" "[email protected]" "Services Status Report" "c:\CheckService\status.htm" HTML
SendmailHTML.vbs is also a VBScript which sends email in HTML format using a SMTP server. Change YOUR_SMTP_SERVER_NAME with your smtp server name or IP. You can also send file attachment or plain text email with this script.
'**********************************************************************
' Usage: cscript.exe c:\checkservice\sendmailhtml.vbs "[email protected]" "[email protected]" "Subject" "HTMLfile" "HTML"
'***********************************************************************
const ForReading=1
Dim ObjArgs, MailTo, From, Subject, Message, wshshell, goFS, oFile, FileName, FileType
Set ObjArgs = WScript.Arguments
MailTo = ObjArgs(0)
From = ObjArgs(1)
Subject = ObjArgs(2)
FileName = ObjArgs(3)
if ObjArgs.Count = 5 then
FileType = ObjArgs(4)
end if
set wshshell=wscript.createobject("Wscript.Shell")
set goFs=createobject("Scripting.FileSystemObject")
'This code requires the file to exist to compile
set oFile = goFs.OpenTextFile(FileName, ForReading)
Message = oFile.ReadAll
Call SendMail(MailTo, From, Subject, Message)
oFile.Close
set wshshell = nothing
set goFs = nothing
set oFile = nothing
Function SendMail(MailTo, From, Subject, Message)
Dim iMsg
Set iMsg = CreateObject("CDO.Message")
With iMsg
.To = MailTo
.From = From
.Subject = Subject
If UCase(FileType) = "HTML" Then
.HTMLBody = Message
ElseIf UCase(FileType) = "ATTACHMENT" Then
.AddAttachment FileName
Else
.TextBody = Message
End If
End With
iMsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YOUR_SMTP_SERVER_NAME"
iMsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
iMsg.Configuration.Fields.Update
iMsg.Send
End Function