Automatically configuring Min/Max Memory in a SQL Server Cluster

When we have a failover cluster environment, one of our main worries is to keep CPU and RAM levels balanced between the nodes and in a way that satisfies all the instances.

The problem is when there is a failover. Since we don’t have a way to automatically configure Min and Max RAM on the instances, we have to do it manually.

Thinking on this (and after analysing some solutions on the web) I have made this powershell script, with the main point being the possibility to give each instance a different weight, so they get RAM proportionally to their importance/needs.

This is still a first version that does not take in consideration SSRS, SSIS or SSAS, but this is something I have in mind and will be available in a new version soon!

Here is the script!

#based on the script found on the url below
#http://sqlblog.com/blogs/merrill_aldrich/archive/2010/01/22/auto-tuning-memory-configuration-on-a-cluster.aspx

#you can see a different approach on the url below
#http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/02/19/use-powershell-script-via-startup-agent-job-to-balance-memory-between-two-instances-on-a-cluster-on-a-failover.aspx

function sendMail([string] $subject) {

#SMTP server name
$smtpServer = "smtp.domain.com"

#Creating a Mail object
$msg = new-object Net.Mail.MailMessage

#Creating SMTP server object
$smtp = new-object Net.Mail.SmtpClient($smtpServer)

#Email structure
$msg.From = "sqlserver@domain.com"
$msg.ReplyTo = "reply@domain.com"
$msg.To.Add("dbas@domain.com")
$msg.subject = "$Subject"
$msg.isBodyHTML = $false;
$msg.Body = "Min/Max Memory has been changed!"

#Sending email
$smtp.Send($msg)
}

function Get-LargePageInformation([string]$SQLInstance, [ref]$sum) {
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$svr = new-object ('Microsoft.SqlServer.Management.SMO.Server') $SQLInstance
$err = $svr.ReadErrorLog() #search inside the current log

$err | Select-String -inputobject {$_.Text} -pattern 'Using large pages for buffer pool' -context 0,0 | % { $sum += $_Matches.count};
}

function Get-SQLInstanceConfig( [string]$SQLInstance, [ref]$hostName, [ref]$maxServerMemory, [ref]$ServerMemoryMB ) {

# Function to establish a connection to a clustered SQL Server instance,
# read max server memory configuration value and current physical host name
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$srv = new-object Microsoft.SQLServer.Management.Smo.Server($SQLInstance)
#connect using windows authentication
$srv.ConnectionContext.LoginSecure = $true

#To connect using windows authentication using a different user uncomment the next 5 lines
# $varDBUser = "USER"
# $varDBPassword = "PASSWORD"
# $srv.ConnectionContext.ConnectAsUser = $true
# $srv.ConnectionContext.ConnectAsUserName = $varDBUser
# $srv.ConnectionContext.ConnectAsUserPassword = $varDBPassword

$maxServerMemory.Value = $srv.Configuration.MaxServerMemory.RunValue # gets the current value

$hostName.Value = $srv.ComputerNamePhysicalNetBIOS
$ServerMemoryMB.value = $srv.PhysicalMemory
}

function Set-SQLInstanceMemory( [string]$SQLInstanceName, [int]$maxMemSetting, [int]$minMemSetting) {

# Function to set min/max server memory on a given SQL instance

#write-host "Reconfiguring" [$SQLInstanceName] Maximum Memory to: $maxMemSetting
#write-host "Reconfiguring" [$SQLInstanceName] Minimum Memory to: $minMemSetting

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$srv = new-object Microsoft.SQLServer.Management.Smo.Server($SQLInstanceName)
#connect using windows authentication
$srv.ConnectionContext.LoginSecure = $true

#To connect using windows authentication using a different user uncomment the next 5 lines
# $varDBUser = "USER"
# $varDBPassword = "PASSWORD"
# $srv.ConnectionContext.ConnectAsUser = $true
# $srv.ConnectionContext.ConnectAsUserName = $varDBUser
# $srv.ConnectionContext.ConnectAsUserPassword = $varDBPassword

$srv.Configuration.MaxServerMemory.ConfigValue = $maxMemSetting
$srv.Configuration.MinServerMemory.ConfigValue = $minMemSetting

$srv.Configuration.Alter() # applies the ConfigValue change

sendMail -subject ("Configurando RAM da instância: " + $SQLInstanceName + " - Máxima(MB): " + $maxMemSetting + "| Mínima(MB): " + $minMemSetting)
}

 

######################## List the instances on the cluster:

$SQLInstances = @( "VIRTUAL_SERVER1\INSTANCE","VIRTUAL_SERVER2\INSTANCE",...,"VIRTUAL_SERVERN\INSTANCE")
# Make a hashtable to sort out which SQL instance is running on which host,
# with how much memory. One table entry for each host, with each entry containing
# an empty hash table we will load with SQL instance configuration info later:
# here you may choose to only include the name of the host where the script will run, so the routine will only
# configure the memory on this node, or may include the name of all nodes, if you want memory to be reconfigured
# on all of them

$hostsTable = @{ "NODE1" = @{}; "NODE2" = @{}; "NODEN" = @{}; }

# Make a hashtable to sort out which SQL instance is from which category.
# One table entry for each host, with each entry containing an empty hash
# table we will load with SQL instance category that will determine its ammout of memory

$HostsCategories = @{ "NODE1" = @{}; "NODE2" = @{}; "NODEN" = @{}; }
# Store the hosts that have at least one instance using Large Page Allocation
$hostsUsingLP = New-Object System.Collections.ArrayList

#variables
[string] $Servidor, [string] $hostName | Out-Null
[int] $ServerMemoryMB, [int] $maxServerMemory, [int] $defaultOSMem, [int] $defaultMemPerInstance, [int] $SumOfWeights, [int] $maxMem, [int] $minMem,[int] $sum | Out-Null
$defaultOSMem = 4096 #amount of memory that will be left for the OS on the machine
$defaultMemPerInstance = 2048 #for each SQL instance this amount of memory will be added to the amount of memory to the OS

#defining category for each instance, the higher the category, more memory is configured for the instance
$InstanceCategory = @{ }
$InstanceCategory.Add("VIRTUAL_SERVER1\INSTANCE",4)
$InstanceCategory.Add("VIRUTAL_SERVER2\INSTANCE",3)
$InstanceCategory.Add("VIRTUAL_SERVERN\INSTANCE",2)

###################### Get the configs for each SQL Server instance

foreach( $SQLInstance in $SQLInstances ) {

$hostName = $null | Out-Null
$maxServerMemory = $null
$ServerMemoryMB = $null
$sum = 0

Get-SQLInstanceConfig $SQLInstance ([REF]$hostName) ([REF]$maxServerMemory) ([REF]$ServerMemoryMB)

# If we really can't see one of the instances, it's best to bail at this point,
# rather than reconfiguring memory settings without complete information

if( $hostName -eq $null ) {
throw ( "Could not connect to one of the SQL instances on the cluster." )
}

#get info about if the instance is using large page allocation or not, which would make it impossible to change its memory configurations before next startup
Get-LargePageInformation $SQLInstance ([REF] $sum)

# Put the current SQLInstance and its max memory value into the right slot
# in the hosts table, to classify the instance by host
if ($sum -eq 0) { #if the instance does not use LP allocation
$hostsTable[$hostName].Add( $SQLInstance, $maxServerMemory)

if($HostsCategories[$hostname][$InstanceCategory[$SQLInstance],1] -eq $null) {
$HostsCategories[$hostname].Add($InstanceCategory[$SQLInstance],1)
}
else {
$HostsCategories[$hostname][$InstanceCategory[$SQLInstance]] += 1
}
}
else { # save the host name to exclude it from the routine later
$hostsUsingLP.Add($hostname) | Out-Null
}

}

# Remove the hosts where there is at least one instance using Large Page Allocation, since memory can only be reallocated during next restart
if ($hostsUsingLP.count -gt 0) { # if the array is not null...
$hostsUsingLP = $hostsUsingLP | select -Unique
}

foreach( $hostEntry in $hostsUsingLP.getEnumerator() ) {
$hostsTable.Remove($hostname)
}

# For each physical cluster node, calculate a reasonable memory limit
# per SQL instance, then verify or correct the max memory value for
# each SQL Server instance running on that host
foreach( $hostEntry in $hostsTable.getEnumerator() ) {
#write-host
#write-host $hostEntry.Name
#write-host " Num SQL instances on this host:" $hostEntry.Value.count

$aggregateMemory = $ServerMemoryMB - $defaultOSMem - ($defaultMemPerInstance * $hostEntry.Value.count) +1 # +1 since it always count 1MB less, don't know why

#write-host " Max memory allowed for all SQL Server instances:" $aggregateMemory
#write-host

$SumOfWeights = $null
foreach( $SQLInstanceEntry in $hostEntry.Value.getEnumerator() ) {
$SumOfWeights += $InstanceCategory[$SQLInstanceEntry.Name]
}

foreach( $SQLInstanceEntry in $hostEntry.Value.getEnumerator() ) {
$maxMem = $aggregateMemory * 100/$SumOfWeights * $InstanceCategory[$SQLInstanceEntry.Name]/100

#using maxMen/2 as minMem value. You can choose a different approach
$minMem = ($aggregateMemory * 100/$SumOfWeights * $InstanceCategory[$SQLInstanceEntry.Name]/100)/2

#Write-Host ""
#write-host " " $SQLInstanceEntry.Name is set to $SQLInstanceEntry.Value

if ( $SQLInstanceEntry.Value -ne $maxMemSetting ) {
Set-SQLInstanceMemory $SQLInstanceEntry.Name $maxMem $minMem
}
}
}

So you should create a powershell file (.ps1) with this code, with the same name and location on all nodes and create a job with a powershell step that calls this file.

In my case I have decided to create another step, before the powershell step, that runs a WAITFOR DELAY of 1 minute (the time depends on your environment), to avoid that the change is made many times, every time one of the instances that are migrating from node goes online.

So this time for the WAITFOR should be enough time to have all the instances migrating back online. You can see details of this type of job in the second URL I provide at the end of this article.

And you, how you deal with this? Do you have a similar script for this kind of situation? Leave a comment!

Sources of information for this script:

Esse post foi publicado em Artigos, Powershell e marcado , , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s