change Sql Server configuration on Powershell

Vinoth Kumar Annadurai 21 Reputation points
2021-10-21T05:25:13.603+00:00

how to change Sql Server configuration on Powershell

say sp_configure 'max memory'

sp_configure 'remote access' ,1

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
4,756 questions

2 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Nilsson Fredrik K 6 Reputation points
    2021-10-21T07:35:59.117+00:00

    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

  2. Limitless Technology 37,611 Reputation points
    2021-10-21T09:30:02.347+00:00

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