Keeping Track of Virtual Databases | Database Journal
MS SQL
SHARE
Facebook X Pinterest WhatsApp

Keeping Track of Virtual Databases

Written By
DatabaseJournal.com Staff
DatabaseJournal.com Staff
Nov 24, 2009
3 minute read

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 “dba@mycompany.com”
	”serverstatus@mycompany.com” “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 “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.
DatabaseJournal.com Staff
Database Journal Logo

DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.

facebook
linkedin
x
rss

Company

Contact us Advertise with us

Categories

Database News Features Scripts Mysql MS SQL Oracle MS Access DB2 PostgreSQL SAP PHP

Property of TechnologyAdvice. © 2026 TechnologyAdvice. All Rights Reserved

Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.

Terms of Service Privacy Policy California - Do Not Sell My Information