question

VinothKumarAnnadurai-4871 avatar image
0 Votes"
VinothKumarAnnadurai-4871 asked LimitlessTechnology-2700 answered

change Sql Server configuration on Powershell

how to change Sql Server configuration on Powershell

say sp_configure 'max memory'

sp_configure 'remote access' ,1

windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NilssonFredrikK-3135 avatar image
0 Votes"
NilssonFredrikK-3135 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered

Hello

For the 'max memory'

Using the SQLlps module: https://docs.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://docs.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--

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.