Now when I needed to run these queries, I would open up Kevin’s website, fire up SQL Management Studio, connect to the database, open a query window and then start pasting and executing. In order to put all that functionality into a single resource, I quickly threw together a executable that contains most of the queries on Kevin’s website as well as the ability to connect to the SCOM backends and dump the information into a DataGridView on a windows form. I also included basic functionality for the queries that were parameterized to take input from the form rather than modifying the queries in the SQL editor window.

First and foremost, I would like to thank Kevin (and give him full credit)  for posting his queries that have proven helpful time and time again. I have included the compiled standalone executable in the links below as well as the source code. Feel free to modify the code in any way to suit your needs as you see fit. The SQL statements are simply embedded into a basic XML resource which can be easily modified to add new queries.

OpsMgrQueries.zip            OpsMgrQueries_Source.zip

For this scenario I have a single domain controller (DC01), two cluster nodes (SERVER01 and SERVER02), and the SCCM Server (SCCM01). Each cluster node has two NICS. The two cluster nodes and the SCCM01 have been joined to the domain on DC01.

Configure Networking

1.       Rename the network interfaces on the cluster nodes for easier identification.

a.       Right click and rename Local Area Connection to Public.

b.      Right click and rename Local Area Connection 2 to Private.

2.       Change the binding order for the NICS on both nodes by selecting the Advanced menu and then Advanced Settings.

3.       In the Adapters and Bindings tab, verify the Connections are listed in the following order: Public, Private, Remote Access Connections. If not, move the connections using the ordering buttons on the right until they are in the proper order.

4.       You should use static IP addressing for all cluster interfaces.

Configure the Private Network Adapter

1.       Go to the Properties of the Private NIC and deselect everything except TCP/IP.

2.       Microsoft also recommends you change the speed and duplex of your adapters to 10Mbps and Half speed. I personally set my heartbeat interfaces to 100/Full, but I also use crossover cables between the interfaces of my cluster nodes or they are on a dedicated small switch. Either way, just be sure you don’t leave the selection on Auto/Auto.

3.       Be sure there are no IP entries for the DNS servers.

4.       Click on the Advanced button.

5.       On the DNS tab, be sure there are no IP addresses listed and remove the checkbox selections for Append parent suffixes of the primary DNS suffix and Register this connection’s addresses in DNS.

6.       Click on the WINS tab and deselect the option to Enable LMHOSTS lookup. Also, disable the NetBIOS functionality.

7.       Click OK to save the changes and repeat on the other node.

Setting up Shared Storage

The following steps assume you already have your storage prepared on the SAN or whatever medium of shared storage you choose and you have the LUN’s presented to the HBA’s on each cluster node. You will need to have two storage resources available to both servers; one for the quorum drive and one for the database.

1.       Turn off both nodes of the cluster.

2.       Turn on the first node only.

3.       Select Disk Management from within Computer Management.

4.       Right click each Unallocated disk and format it as a Primary partition. Do NOT convert the disk to Dynamic disk, only use Primary disks. If you use the wizard to write a disk signature it may automatically convert the disk to dynamic. If this happens, right click the disk and select Revert to Basic.

a.       Note – If you are using 64 bit, be sure to format the disks as MBR, not GPT.

5.       Verify that you can create and delete a text file on each drive from each node of the cluster. When testing this, ensure only one node is online at a time. When completed, be sure all nodes are off except the first.

Configure the First Node

1.       Open Cluster Administrator. When prompted with the Open Connection to Cluster window, select Create a new cluster. Click OK.

2.       Select a unique NetBIOS name for the cluster following the usual 15 character NetBIOS limit.

3.       Select the name of the first node to be added to the cluster, click Next.

a.       You will be prompted to enter credentials if you are not logged on with a domain account that also has local Adminstrator privileges. This is simply to complete the install and is NOT the account that will be used to start the Cluster services.

4.       The Advanced button is used when you have complex storage solutions in place and the setup wizard detects disks that you do not want to be shared in the cluster. For the sake of this document, we will simply select the Typical (full) configuration option at the Advanced Configuration Options window. Click OK.

5.       The wizard will now run a series of checks and verifications on your environment. When it’s completed, address any critical issues preventing the cluster from being created and continue by clicking Next.

a.       I have never built a cluster that did not return at least some warnings during this phase. Simply review these warning if they occur and address them as needed.

6.       At the IP Address screen, enter in the IP address that will be used to manage the cluster resources. Click Next.

7.       At the Cluster Service Account Wizard screen enter in the credentials of a domain user account that has Local Administrator rights on each node. Click Next.

8.       Review the summary and select the Quorum button. Select the shared storage drive you allocated for the Quorum drive in the drop down.

a.       Note – If you only see Local Quorum listed in the dropdown, continue on through the steps and I will show you how to correct this after the cluster node is up.

9.       When tasks have completed, click Next.

10.       You should now have the single node resource up and running.

(If you’re Quorum disk is listed as a Quorum Resource under the Resource Type column, proceed to the next section. If your console looks like the example above, you may skip the next section.)

Change the Local Quorum Resource

1.       Open the Cluster Administrator and select Cluster Group under Groups.

2.       Right click and select New/Resource.

3.       Select the Resource Type as Physical Disk. Enter a descriptive name for the disk (something like Quorum since this will be the shared drive for the quorum). Click Next.

4.       Make sure that SERVER01 is listed under Possible owners. Click Next.

5.       Leave the Resource dependencies window empty and click Next.

6.       Select the appropriate disk from the drop down and click Finish.

7.       Select the cluster name in the console (in this example, it would be SCCMCLUSTER).Right click and choose Properties.

8.       Select the Quorum tab and change the resource to the new disk resource created in the previous section.

9.       Finally, verify that the disk that will be used as the database storage drive is listed under the Group 0 folder which is automatically created. If it is missing, create the resource following Steps 3 through 6 above.

Configure the Second Node

1.       Verify that all resources are online for the first node in Cluster Administrator.

2.       Turn on the second node (leave node one online).

3.       From the Cluster Administrator on SERVER01, select File – > New -> Node.

4.       Enter in the name of the computer to be added as the second node and click Next.

5.       Review the analysis summary when completed and address any errors.

Post Installation Configuration

1.       Go to the Properties of the Private network by right clicking on the Private network interface.

2.       On the General tab, select the radial next to Internal cluster communications only (private network). Click OK.

3.       Right click on the Public network and select Properties.

4.       Select the radial next to All communications (mixed mode). Click OK.

5.       Right click the cluster name and select Properties.

6.       Select the Network Priority tab and ensure that Private is listed at the top of the list.

7.       Verify the failover functionality by right clicking on Cluster Group in the Cluster Administrator console. Select Move Group to begin the failover.

a.       Before failover test

b       Successful failover

In the next post, I will detail installing SQL Server 2005 on the cluster and creating the SCCM database on the remote cluster as well as the caveats along the way such as configuring SPN’s.

You can download the PDF version of the entire tutorial here.

The architecture is basically a primary parent site server that maintains no clients and is used mainly for reporting and collecting the SCCM hierarchical data. Next, we have three primary child sites and each of these sites will in turn have secondary sites for the branch offices. We already had an existing WSUS implementation outside of the SCCM deployment so I planned on mimicking that layout when configuring the WSUS update synchronization hierarchy.

In order to maintain segregated functionality of the primary parent site, I configured one of the primary child sites as the top level upstream synchronization server with Microsoft. Next, I configured the remaining two primary children as downstream servers for the WSUS updates. Here was the mistake.

On the two primary children servers configured as downstream servers, I continuously received Sync failed: WSUS server not configured in the wsyncmgr.log. I referred back to the sync settings in both the SCCM console and the WSUS console and everything was configured correctly from what I could tell. The problem wasn’t with my settings, it was with my architecture. When WSUS is integrated into SCCM, the WSUS hierarchy is rendered invalid in favor of the SCCM replication hierarchy. Basically, the upstream/downstream relationship for WSUS is now directly related to the parent/child relationship in SCCM. In order to fix the problem, I had to move the upstream synchronization server to the primary parent server and configure all three primary children as downstream servers.

Once, I made this change, I re-ran the WSUS configuration wizard from the WSUS console and restarted the SMSEXEC service on each primary site server and everything started synchronizing as it should.

I also noticed that after making the change on the primary child server that was previously configured as the upstream server, I was receiving a substantial number of alerts in the smsdbmon.log with the entry: Warning: Could not write to the named pipe \\<SERVERNAME>\PIPE\SMSPROVIDER_<SITENAME>, Error=2 where <servername> is the name of the local server and <sitename> is the name of the primary site for that server. I reviewed all of the SQL configuration settings and permissions; however, the final fix was to simply re-run the WSUS configuration utility from the WSUS console again and the errors stopped.

Enter SNMP. Since we are working with a Linux platform, I figured SNMP would be the best bet so I simply needed to find a way to connect. Net-SNMP to the rescue. The first order of business was to find the proper node in the SNMP tree where the serial number was stored. To do this, I used the snmpwalk.exe utility from Net-SNMP. The quickest way I found is to simply walk the entire tree; however now that I’ve done that and dumped a LOT of information, I’ll save you some time and target the proper object node with the following syntax:

snmpwalk.exe -v 2c -c public servername enterprises > C:\snmpoutput.txt

In this example, -v 2c tells snmpwalk to use SNMP version 2 and -c public stipulates the read community string to use when walking the OID tree. The enterprises OID is the starting point OID to begin walking all subtrees rooted underneath. Just for reference, the OID defined for enterprises by HP is .1.3.6.1.4.1; however, the MIB file has definitions that allow us to simply use the friendly name of enterprise.

Once you have obtained the output file, search through the results for 2.2.2.5 which is the OID for the string that maintains the serial number of the server. Now that we have the entire OID for the node, we can use the snmpget.exe utility to directly target the OID tree node we want instead of enumerating everything under the enterprise node such as:

snmpget.exe -v 2c -c public servername .1.3.6.1.4.1.232.2.2.2.5.0

Another syntax that is a little easier on the eyes would be:

snmpget.exe -v 2c -c public servername SNMPv2-SMI::enterprises.232.2.2.2.5.0

which is the same as the previous command only using the defined enterprises name rather than the full OID. Now, this is all good and well for a single ESX host, but what about several? I wanted to use PowerShell to wrap up the automation functionality, but there were no .NET classes available for SNMP and using a custom assembly from CodePlex or CodeProject was a little bit of overkill just for a simple automation task so I decided to wrap it up in good old VBScript as shown below:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
Set infile = objFSO.OpenTextFile("C:\Scripts\VBScript\ESXSerials\ESXHosts.txt")
Set outfile = objFSO.OpenTextFile("C:\Scripts\VBScript\ESXSerials\ESXSerials.csv", 2, True)
 
Do While Not infile.AtEndOfStream
	str = infile.ReadLine()
	WScript.Echo "Querying " & str
	output = str
	Set objExecObject = objShell.Exec("cmd /c D:\Net-SNMP\bin\snmpget.exe -v 2c -c public " & str & " SNMPv2-SMI::enterprises.232.2.2.2.5.0")
	Do While Not objExecObject.StdOut.AtEndOfStream
	    strText = objExecObject.StdOut.ReadLine()
	    If Instr(strText, "232.2.2.2.5.0") > 0 Then
	        output = output & ", " & Right(strText, 12)
	        i=1
	        Exit Do
	    End If
	Loop
	outfile.WriteLine output
Loop

In the above example, I installed Net-SNMP to D:\Net-SNMP on my workstation and the tools extract into the bin folder by default. I also used a list of all the ESX hosts I was targeting in a text infile. I found the easiest way to get this info was to simply export a list of all hosts from VCenter and then mangle the CSV into a text file with only the server names. The rest should be fairly obvious in the script.

This was one of those weird requests that makes you do a little extra research to come up with a solution. I learned a bit more about SNMP during the process and hope this helps out some others who need to pull some data from ESX.

The MIB containing the OID information for this particular script was CPQSINFO.MIB; cpqSystemInfo is the top variable. ByteSphere has a great MIB resource area to use for research.

This script was implemented as a scheduled task so I simply hardcoded the values into the script rather than using the param statement since I didn’t plan on calling it from the console; however, it should be easy enough to modify if you wanted to use it interactively. Also, since it is required to connect to the VMServer prior to running any cmdlets, I used Secure-String to generate an encrypted password with a little bit of security since it would be in a text file on the server. To generate the password file, I used the following commands directly in a PowerShell console (second line wrapped):

$secureString = Read-Host -AsSecureString
ConvertFrom-SecureString $secureString | out-file C:\Scripts\PowerShell\VMPingTest\acctpass.txt

As you can see from the script below, it only takes one line of .NET to decrypt the encrypted password from the file so it’s not foolproof, but still better than plain text and NTFS only. I’ve commented the script fairly heavily, so it should be easy to customize for different environments.

#Load VMWare snap in:
Add-PSSnapin VMware.VimAutomation.Core -ErrorVariable errSnapIn -ErrorAction silentlycontinue
if ($errSnapIn.Count -ne 0) {
	Write-Host "VMWare snap in is required."
	exit
	}
#------------------------------------------------------------------------------
# Set up variables
#------------------------------------------------------------------------------
#VM Server hostname
$VMServer = "MYSERVER.MYDOMAIN"
#User account to connect to virtual infrastructure:
$username = "USERNAME"
#Directory path to secure string password:
$pass = "C:\Path\to\securestringpass.txt"
#Directory path to target list of VM's.
$servers = Get-Content "C:\Path\to\serverlist.txt"
#Directory path to list of mail recipients.
$mailrecipients = Get-Content "C:\Path\to\recipients.txt"
#SMTP relay server
$smtpserver = "smtp.mydomain.com"
#Decrypt SecureString password for account:
$pwd = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((gc $pass | ConvertTo-SecureString)))
 
#------------------------------------------------------------------------------
# Connect to VM Server
#------------------------------------------------------------------------------
$server = Connect-VIServer -Server $VMServer -User $username -Password $pwd
#Search for specific VM in the production cluster:
$folder = Get-Folder -Name "Financial Hosts" #Target the Finanancial Hosts folder in the VM Infrastructure Client console
$cluster = Get-Cluster -Name "Dallas HA Cluster" -Location $folder #Target the Dallas HA Cluster in the VM Infrastructure Client console
 
#------------------------------------------------------------------------------
# Methods
#------------------------------------------------------------------------------
function SendMail
{
	param($svr, $status)
	foreach ($recipient in $mailrecipients)
	{
		$mail = new-object System.Net.Mail.MailMessage
		$mail.Priority = 'High'
		$mail.From = new-object System.Net.Mail.MailAddress("PINGTEST@MYDOMAIN.COM");
		$mail.To.Add("$recipient");
 
		#set the content
		$mail.Subject = "$svr is not responding...";
		$mail.Body = "$svr failed to respond to ping tests with error: $status.`n" + 
		"This process automatically powers on virtual machines and restart virtual guests where the local network is unresponsive. `n" + 
		"This server has not responded to automated restart tasks and needs additional attention.`n`n"
 
		#send the message
		$smtp = new-object System.Net.Mail.SmtpClient($smtpserver); 
		$smtp.Send($mail);
	}
}
 
#------------------------------------------------------------------------------
# Main()
#------------------------------------------------------------------------------
foreach ($svr in $servers)
{
	$result = gwmi -class Win32_PingStatus -filter "Address='$svr'"
	$results = $result.StatusCode
	$vm = Get-VM -Name $svr -Location $cluster #Search for specified VM in cluster
 
	if (($vm.PowerState -eq "PoweredOff") -and ($results -ne 0))
	{
		#If the VM is powered down
		Start-VM -VM $vm.Name #Start VM
		Start-Sleep -Seconds 60 #Allow time for VM to come online
	}
 
	#If the VM is powered on, but guest OS is not responding to pings
	if (($vm.PowerState -eq "PoweredOn") -and ($results -ne 0))
	{
		Restart-VMGuest -VM $vm.Name #Restart VM Guest OS
		Start-Sleep -Seconds 60 #Allow time for VM to come online
	}
 
	#Re-run ping test if server was originally unresponsive.
	if ($results -ne 0)
	{
		$secondresult = gwmi -class Win32_PingStatus -filter "Address='$svr'"
		$secondresults = $secondresult.StatusCode
		switch ($secondresults)
		{
			0 	  { $pingresults = "Success" }
			11001 { $pingresults = "Buffer Too Small" }
			11002 { $pingresults = "Destination Net Unreachable" }
			11003 { $pingresults = "Destination Host Unreachable" }
			11004 { $pingresults = "Destination Protocol Unreachable" }
			11005 { $pingresults = "Destination Port Unreachable" }
			11006 { $pingresults = "No Resources" }
			11007 { $pingresults = "Bad Option" }
			11008 { $pingresults = "Hardware Error" }
			11009 { $pingresults = "Packet Too Big" }
			11010 { $pingresults = "Request Timed Out" } 
			11011 { $pingresults = "Bad Request" }
			11012 { $pingresults = "Bad Route" }
			11013 { $pingresults = "TimeToLive Expired Transit" }
			11014 { $pingresults = "TimeToLive Expired Reassembly" }
			11015 { $pingresults = "Parameter Problem" }
			11016 { $pingresults = "Source Quench" }
			11017 { $pingresults = "Option Too Big" }
			11018 { $pingresults = "Bad Destination" }
			11032 { $pingresults = "Negotiating IPSEC" }
			11050 { $pingresults = "General Failure" }
		}
	}
 
	if ($secondresults -ne 0) 
	{
		SendMail $svr $pingresults
	}
}

To put a little perspective on the environment; my data warehouse server is a dedicated cluster with quad processors and 8 GB on each node. The DW database is approx. 80GB and there are no other sql instances or databases running on the platform which is why I could not figure out why the CPU continuously stayed pegged as there was more than enough hardware to handle that role. Since there were multiple hyperthreaded processors, the first thing we attempted was to disable hyperthreading in the BIOS. No luck. The next item on the agenda was to make sure that the MAXDOP (Max Degree of Parallelism) was set properly for the server. This was done by running the following command and making the appropriate changes from the returned results to the maxdop properties of the server in Management Studio:

select case when cpu_count / hyperthread_ratio > 8 then 8 else cpu_count / hyperthread_ratio end as optimal_maxdop_setting from sys.dm_os_sys_info.

After changing the setting to the output from the select statement, my CPU utilization dropped down to around a steady 40% to 50% utilization. Better, but not the 3% to 5% normal utilization the server used to run with.

After analyzing a few perfmon and SQL Profiler traces, PSS identified a stored procedure that was taking too long to run on the State dataset from “StandardDatasetMaintenance”. After some additional analysis, it turned out that there were several missing indexes which needed to be rebuilt. I ran the following two commands against the database:

EXEC sp_MSForEachTable  “UPDATE STATISTICS ? with fullscan”

EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 100)”

After these commands completed (took about 90 minutes), the CPU utilization was returned back to 5% or below. I have scheduled these as maintenance tasks to run every 30 days. It took nearly three weeks of headache to finally resolve the high CPU utilization so I hope this saves some time for others who admin SCOM, but are not DBA’s.