change Sql Server configuration on Powershell

Vinoth Kumar Annadurai 21 Reputation points

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
No comments
{count} votes

2 answers

Sort by: Oldest
  1. Nilsson Fredrik K 6 Reputation points

    What you want to do is to download dbatools as soon as possible and start using this cmdlet I think...

  2. Limitless Technology 37,611 Reputation points


    For the 'max memory'

    Using the SQLlps module:

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

    Other server memory configuration options:

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