Dovetailing onto my previous post SCCM 1511 Command-Line Site Server installation via PowerShell, I left out the step which configures the SQL Server instance memory allocation

This example uses PowerShell to read a “template” file, replace a variable string value, and output a new SQL script file.  Then it turns around and executes the SQL script using sqlcmd.exe.

Not rocket science obviously, so if you already have a solution in place that works (or works better), keep using yours.  Otherwise, I hope it’s of some help.

Notes

  1. This sets the minimum and maximum memory allocation to 80% of the total physical memory of the SQL Server host machine.  If you’d rather set the max at 80% and the min at something lower, just modify the line for @min where it calculates (7.0 / 8.0) to something like (1.0 / 8.0) or whatever you prefer.
  2. The variable %TOTALMEMORY% is intended to be replaced by the PowerShell script prior to being executed.  If you try to run the template code (below) as-is, it will fail.
  3. The script must be executed under the context of a user with sysadmin role membership in the SQL instance.  I use a domain account “sccmadmin” which is a member of Domain Admins, and SQL Admins, and SQL Admins is a member of the SQL instance sysadmin role.
  4. The script is intended to be executed ON the machine which hosts the intended SQL Server.  If run from another machine, you may need to invoke the .sql script using  PS-Remoting, or PsExec.exe, or invoke the entire mess using WinRM/winrs.  I’ll leave that up to you.

The Template

(saved as “sql_mem_template.sql” in the same folder as the PowerShell script)

DECLARE @logical_cpus SMALLINT,
 @total_mem decimal (19,4),
 @min int,
 @max int
SELECT 
 @logical_cpus = cpu_count,
 @total_mem = %TOTALMEMORY%
FROM sys.dm_os_sys_info
SELECT * FROM sys.dm_os_sys_info

SELECT @min = (@total_mem *(7.0 /8.0))/(1024 * 1024),
       @max = (@total_mem *(7.0 /8.0))/(1024 * 1024)

IF @logical_cpus = 1
  BEGIN
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'min server memory',@min;
  EXEC sp_configure 'max server memory',@max;
  RECONFIGURE;
END

The Script

$ScriptPath = Split-Path -Parent $MyInvocation.MyCommand.Definition

function Configure-SQLMemory {
  param (
    [parameter(Mandatory=$True)] [string] $SQLServerFQDN
  )
  $out = $True
  $TmpScript = "$ScriptPath\sql_mem_template.sql"
  $NewScript = "$ScriptPath\sql_memory.sql"
  $Temp = Get-Content "$TmpScript"
  $TotalMem = Get-WmiObject -Class Win32_ComputerSystem | 
    Select-Object -ExpandProperty TotalPhysicalMemory
  $Temp.Replace("%TOTALMEMORY%", $TotalMem) | Out-File $NewScript
  $SqlLog = "$ScriptPath\sql_memory_update.txt"
  $CmdArgs = "-S $SQLServerFQDN -i $NewScript -o $SqlLog"
  $proc = Start-Process "sqlcmd.exe" -ArgumentList $CmdArgs -NoNewWindow -PassThru
  $proc.WaitForExit()
  $exit = $proc.ExitCode
  if ($exit -eq 0) {
    Write-Host "memory settings have been updated."
  }
  else {
    $out = $False
    Write-Error "failed to update memory settings. Exit code is: $exit"
  }
  return $out
}

Enjoy!

Advertisements

One thought on “PowerShell + SQL Server Memory Allocation

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s