change Sql Server configuration on Powershell

Question

how to change Sql Server configuration on Powershell

say sp_configure 'max memory'

sp_configure 'remote access' ,1

Answer 1

Hello

For the 'max memory'

Using the SQLlps module: https://learn.microsoft.com/en-us/sql/powershell/sql-server-powershell?view=sql-server-ver15

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

Other server memory configuration options: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15

For sp_configure 'remote access' you will need to use DBATools modules which is not a Microsoft tool. It would be better to consult with DBATools forums as they may be better equipped and experienced to assist in the script.

------------------------------------------------------------------------------------------------------------------------------------

--If the reply is helpful, please Upvote and Accept as answer--

Answer 2

What you want to do is to download dbatools as soon as possible and start using this cmdlet I think...
https://docs.dbatools.io/#Set-DbaSpConfigure