Set SQL Server 'Maximum server memory in (MB)' using Powershell

Question

I'm currently automating a VM and using a Powershell script to install SQL Server 2017.

I need to be able to script the Maximum server memory as well. Is this possible through the current configuration settings or will I need to create a custom script? If a custom script is required what would I need to do.

Answer 1

You could use SQL (run from Powershell):

sp_configure 'show advanced options', 1;  
RECONFIGURE;  
sp_configure 'max server memory', 4096;  
RECONFIGURE;  

or use PowerShell cmdlet from dbatools.io:

Set-DbaMaxMemory

explicitly set the max memory to 2048 MB on just one server, “sqlserver1”

Set-DbaMaxMemory -SqlServer sqlserver1 -MaxMb 2048 

Answer 2

By using sqlps module it's possible to address this task the next way:

Import-Module Sqlps
$sql16 = ls 'SQLSERVER:\SQL\(local)'|? InstanceName -eq 'SQL16'
$sql16.Configuration.MaxServerMemory.RunValue
$sql16.Configuration.MaxServerMemory.ConfigValue = [Math]::Floor($sql16.Configuration.MaxServerMemory.RunValue * 1.5)
$sql16.Alter()
$sql16.Configuration.MaxServerMemory.RunValue