Tag Archives: Vmware

Last day at #SQLPass summit 2013

It’s been a very long week but now it comes to and end. I already looking forward to next year in Seattle and I have not even left Charlotte yet.

I’m getting a bit tiered, all this new knowledge everyday and then all the social activities at night is beginning to take its toll.

But this last day at SQLPass was also a great one. I started out with a session with Tim Chapman called “SQL Server Index internals: A deep dive”, that was almost a bit to much at 8.00 in the morning, especially after last nights party at NASCAR Hall of fame. But Tim had  great presentation skills and he kept us all awake with great demos.

Then I went to a session about Central Management Server and Policy based management with Ryan Adams. I have never really gotten the hang on policy based management but Ryan sorted it out just fine and I know think it’s time to start using this feature to get better control that all servers adhere to a given standard.

Then I went to a great session with David Klee, “squeezing Top Performance from Your Virtualized SQL Server”, got out with some really useful tips on how to configure Vmware, Windows and SQL server to get top notch performance. A lot of this can be found on his website.

The for lunch there was a event called “Birds of a feather luncheon”, where all tables has a sign describing the topic to be discussed. I went for a table called AlwaysOn and got to talk to some great guys about difficulties about clustering and AlwaysOn.

The I continues on that track after lunch with a session called “CAT: AlwaysOn Customer panel – Lessons learned & Best practices”. four guys from different companies described their environment and the problems they had encountered while setting up various HA & DR solutions. very interesting.

The last session of this years summit was Paul White describing Parallel query executions in detail. Didn’t know there was something called a Parallel deadlock situation, but Paul managed to great such a scenario and then describe why this happened and how we may avoid it. He also went into detail about a lot of other parallel execution problems one might run into. Paul was also a very skilled presenter an managed to keep us awake even though it was late on friday afternoon.

To summarize this has been a fantastic week and there has been so much interesting sessions and so many magnificent speakers it even hard to imagine where one could get this elsewhere.

I’m so looking forward to next years SQL Pass summit in Seattle, se you all there.



Check numer of running and stopped Vm’s in each resource group

Here is another powershell PowerCLI script that will give you the number of running and stopped Vm’s grouped by Cluster and Resource group.

# Add in the VI Toolkit goodness
if ( (Get-PSSnapin -Name VMware.VimAutomation.Core -ErrorAction SilentlyContinue) -eq $null ){Add-PSsnapin VMware.VimAutomation.Core}

$VCs =@(“srv-vc-0001″,”srv-vc-0002”)
$date=Get-Date -format “yyMMd”
$file = “c:\temp\VmwareStats$date.csv”

function GetVmwareStats
$report = @()
foreach ($VC in $VCs)
Connect-VIServer $VC | Out-Null

foreach ($Cluster in Get-Cluster)
#write-host $Cluster
$allcount = @()
foreach ($resourcepool in (Get-ResourcePool -Location $Cluster | Where {$_.Name -ne “Resources“ }))
$count = “” | Select Cluster,Hostname,ResourcePool, RunningVms, StoppedVms
$count.HostName = $vmHost.name
$count.Cluster = $Cluster.Name
$RunningsVms = Get-VM -Location $Cluster,$ResourcePool -NoRecursion | Where {$_.PowerState -eq “PoweredOn”}
$StoppedVms =Get-VM -Location $Cluster,$ResourcePool -NoRecursion | Where {$_.PowerState -eq “PoweredOff”}
$count.RunningVMs = $RunningsVms.Count
$count.StoppedVms = $StoppedVms.Count
$count.ResourcePool = $ResourcePool.Name
$allcount += $count
$allcount | Select Cluster, ResourcePool, RunningVms, StoppedVms
$report += $allcount


GetVmwareStats |export-Csv $file -notypeinformation -Delimiter “;”

Check you Vmware load

Ever wanted to check the average load of your ESXi servers. Check out this PowerShell PowerCLI script to get average CPU and memory load for your different clusters.

Will give you an output file like this:

HostName ClusterName MemMax MemAvg MemMin CPUMax CPUAvg CPUMin
srv-vm-0001.sirius.local DRS1-Test 72.11 1.96 1.73 29.96 0.25 0
srv-vm-0002.sirius.local DRS1-Test 77.05 60.29 1.14 67.5 11.852 0

# Add in the VI Toolkit goodness
if ( (Get-PSSnapin -Name VMware.VimAutomation.Core -ErrorAction SilentlyContinue) -eq $null ){Add-PSsnapin VMware.VimAutomation.Core}

$VCs =@(“vc_server_1″,”vc_server_2”)
$date=Get-Date -format “yyMMd”
$file = “c:\temp\vmware_load_$date.csv”

Function GetVmwareLoad
$report = @()
foreach ($VC in $VCs)

Connect-VIServer $VC | Out-Null

$allhosts = @()
foreach ($Cluster in Get-Cluster)
foreach($vmHost in ($cluster | Get-VMHost))
$hoststat = “” | Select HostName, ClusterName, MemMax, MemAvg, MemMin, CPUMax, CPUAvg, CPUMin
$hoststat.HostName = $vmHost.name
$hoststat.ClusterName = $Cluster.Name

$statcpu = Get-Stat -Entity ($vmHost)-start (get-date).AddDays(-30) -Finish (Get-Date)-MaxSamples 10000 -stat cpu.usage.average
$statmem = Get-Stat -Entity ($vmHost)-start (get-date).AddDays(-30) -Finish (Get-Date)-MaxSamples 10000 -stat mem.usage.average

$cpu = $statcpu | Measure-Object -Property value -Average -Maximum -Minimum
$mem = $statmem | Measure-Object -Property value -Average -Maximum -Minimum

$hoststat.CPUMax = $cpu.Maximum
$hoststat.CPUAvg = $cpu.Average
$hoststat.CPUMin = $cpu.Minimum
$hoststat.MemMax = $mem.Maximum
$hoststat.MemAvg = $mem.Average
$hoststat.MemMin = $mem.Minimum
$allhosts += $hoststat

$report += $allhosts |Select HostName, ClusterName, MemMax, MemAvg, MemMin, CPUMax, CPUAvg, CPUMin

########## Main Program ###########

$output| export-Csv $file -notypeinformation -Delimiter “;”

MasterDataServices setup in a AlwaysOn environment

I was told to install MDS (Master Data Services) in AlwaysOn environment with HA (high availability) and DR(disaster recovery) protection for a customer. This sounded like an easy task with some withe papers and guidelines available on the net.

I started on with this one from Microsoft which looked very promising:


But I soon realized this was maybe not the best solution since it required me to install IIS on each node in the AlwaysON cluster and it also required that all IIS installations where kept in sync.

The AlwaysOn environment at this customer site is setup with 5 nodes, three on primary site and two on DR site. It includes 3 Availability Groups which can failover between at least 3 of the different hosts. It uses NonShared Storage.

Installing IIS and all MDS services in this environment didn’t sound very appealing so I decided to use a different approach.

I created a new virtual server in Vmware and added it to the SRM (SiteRecoveryManager) setup, which means that it is replicated to the DR site and can be started up there in case of DR situation. On the primary site HA is accomplished through multiple hosts in the Vmware cluster.

After Installing MDS on the new virtual server, I run the Master Data Services Configuration Manager and pointed to the AlwaysOn AG listener that I had previously created on the new AG for the MDS database.

I now have HA and DR solution for MDS without having to mess up the SQL servers with IIS and MDS services, that feels like a much cleaner solution to me.

Staffan Olofsson
MCSE: Data Platform