Configurando dinamicamente Max/Min Memory de seus clusters SQL Server

Quando temos um ambiente em cluster, uma das principais preocupações é manter o ambiente devidamente balanceado, de modo que o consumo de CPU e memória RAM fique parecido entre os nós do cluster.

O problema é que no momento de um failover, apesar de podermos realizar algumas configurações para definir para onde irão as instâncias, não temos uma maneira de automaticamente configurar o consumo de memória RAM das instâncias (Min e Max Memory), de forma a tornar este consumo condizente com a relação memória do servidor x número de instâncias atualmente no nó.

Pensando nisso criei um script em powershell que visa fazer essa configuração de forma automática, levando em consideração alguns parâmetros previamente definidos, como uma “classificação” de prioridade de cada instância.

Essa é uma primeira versão, que ainda não leva em conta questões como instâncias do SSRS, SSIS ou SSAS, algo que devo incluir na próxima versão do script.

Segue abaixo o 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 = "Quantidades Mínima e Máxima de memória RAM foram reconfiguradas!"

#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 = "USUARIO"
# $varDBPassword = "SENHA"
# $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 = "USUARIO"
# $varDBPassword = "SENHA"
# $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 #o +1 porque sempre vem com 1MB a menos, nao sei o motivo

#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
}
}
}

Você deve criar um arquivo powershell (.ps1) contendo esse código, com o mesmo nome e no mesmo caminho em cada um dos nós do seu cluster e criar um job com um step do tipo Powershell que chame esse arquivo.

No meu caso, preferi criar um um step antes que executa um WAITFOR DELAY de 1 minuto (esse tempo vai variar em cada ambiente), para evitar que, enquanto as instâncias ainda estejam subindo, o script faça a alteração na configuração de memória, o que faria que essa configuração fosse alterada várias vezes, a medida que cada instância ficasse online. O tempo do WAITFOR deve ser suficiente para que todas as instâncias fiquem online. Há uma explicação para esse procedimento no segundo link que listo abaixo.

E você, como lida com isso? Tem algum tipo de script para esse tipo de situação? Deixe seu comentário!

Fontes de informação para esse script:

Esse post foi publicado em Artigos, Powershell, Virtual PASS BR 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