Keeping Track of Virtual Databases

by

Shailesh Khanal

If you have SQL Server or Oracle databases in a Microsoft
Windows clustered environment, then you’ll 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. On the other hand, you
may have applied patches on Windows operating servers and rebooted them but
forgot to failover the virtual servers to the proper nodes.

You may have a 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 the email.

To overcome this situation, you can write a script to go
after all of your clustered databases and find where they are running. You can
run this VBScript every morning or schedule it and send report via email, which
you should not ignore.

The following script (save as c:checkservicecheckservice.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 stand-alone will be half of clustered ones.

The first entry in the array is the physical server name, the
rest are service names.

You can add as many servers and services as you need to the
list, just make sure to change the array SQLServer and OracleServer. Also
change the number in the WHILE LOOP as well.

This script calls the WMI script to query each server in the
list to query service status, so the account you log in to run the script must
have administrative rights on all of 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:CheckServicestatus.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 & “rootcimv2”)

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 & “rootcimv2”)

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:CheckServicestatus.htm”
Set oShell = Nothing

You can run this script by double clicking it; it generates
a file status.htm and opens it with Internet Explorer.

Another 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:CheckServiceCheckService.vbs

cscript.exe c:checkservicesendmailHTML.vbs “[email protected]
[email protected]” “Services Status Report” “c:CheckServicestatus.htm” HTML

SendmailHTML.vbs is also a VBScript, which sends email in
HTML format using an SMTP server. Replace YOUR_SMTP_SERVER_NAME with your SMTP
server name or IP. You can also send file attachments or plain text email with
this script.


‘**********************************************************************
‘ Usage: cscript.exe c:checkservicesendmailhtml.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

The code listed above generates a status
report for one Oracle and one SQL Server, so as it is, it provides a lot of value.
However, you may have many more servers and services in your environment that
you need to keep track of. Just edit the array and increase the loop counter
and enjoy your daily report; no more logging onto each machine to check the
status.

Latest Articles