Keeping Track of Virtual Databases

November 24, 2009

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:\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 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:\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; 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:\CheckService\CheckService.vbs

cscript.exe c:\checkservice\sendmailHTML.vbs "dba@mycompany.com" 
	"serverstatus@mycompany.com" "Services Status Report" "c:\CheckService\status.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:\checkservice\sendmailhtml.vbs "to@company.com" "from@company.com" "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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers