dbatools.io = command-line SQL Server Management Studio - SQL Configuration
This post is part of the series showing practical usage examples. The main post covering links to all posts can be found here: dbatools.io = command-line SQL Server Management Studio: Table of contents.
SQL Server configuration / sp_configure
SQL Server allows its users to configure a lot of things. From ‘Ad Hoc Distributed Queries’ through ‘max degree of parallelism’ to ‘xp_cmdshell’. Some of those can be done via SQL instance properties or the famous sp_configure. With dbatools, there are a few more options to manage the SQL Server configuration.
Get-DbaSpConfigure
# Get the config options - present as separate windows
Get-DbaSpConfigure -SqlInstance $server | Out-GridView
# or
# define options you want to check (use DisplayName)
$configOptions = 'cost threshold for parallelism','max degree of parallelism'
# see the options
Get-DbaSpConfigure -SqlInstance $server -Name $configOptions | Format-Table
<#
ComputerName ConfiguredValue DefaultValue Description DisplayName InstanceName IsAdvanced IsDynamic IsRunningDefault
Value
------------ --------------- ------------ ----------- ----------- ------------ ---------- --------- ----------------
localhost 5 5 cost threshold for parallelism cost threshold for parallelism MSSQLSERVER True True True
localhost 0 0 maximum degree of parallelism max degree of parallelism MSSQLSERVER True True True
#>
Export-DbaSpConfigure
# Export/backup all the config to file as T-SQL script
Export-DbaSpConfigure -SqlInstance $server -FilePath SqlConfig.txt
# See the content
Invoke-Item SqlConfig.txt
<#
EXEC sp_configure 'show advanced options' , 1; RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'recovery interval (min)' , 0;
EXEC sp_configure 'allow updates' , 0;
EXEC sp_configure 'user connections' , 0;
#>
Set-DbaSpConfigure
# set config value
Set-DbaSpConfigure -SqlInstance $server -Name 'max degree of parallelism' -Value 4
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
ConfigName : MaxDegreeOfParallelism
PreviousValue : 0
NewValue : 4
#>
Import-DbaSpConfigure
# import config from file
Import-DbaSpConfigure -SqlInstance $server -Path SqlConfig.txt
<#
...
[04:17:58][Import-DbaSpConfigure] Successfully executed EXEC sp_configure 'allow polybase export' , 0;.
[04:17:58][Import-DbaSpConfigure] Successfully executed EXEC sp_configure 'show advanced options' , 0;.
[04:17:58][Import-DbaSpConfigure] Successfully executed RECONFIGURE WITH OVERRIDE.
WARNING: [14:17:58][Import-DbaSpConfigure] Some configuration options will be updated once SQL Server is restarted.
[04:17:58][Import-DbaSpConfigure] SQL Server configuration options migration finished.
#>
# see the config that was changed after export
Get-DbaSpConfigure -SqlInstance $server -Name 'max degree of parallelism' | SELECT ComputerName, DisplayName, ConfiguredValue
<#
ComputerName DisplayName ConfiguredValue
------------ ----------- ---------------
localhost max degree of parallelism 0
#>
Suggested max memory and MAXDOP configuration
dbatools even offer help with deciding what your max memory and MAXDOP should be. Use below functions to set the values based on industry recommendations that could be considered as a starting point.
Set-DbaMaxDop
Set-DbaMaxDop -SqlInstance $server
Set-DbaMaxMemory
Set-DbaMaxMemory -SqlInstance $server
With only few dbatools commands it is possible to manage SQL configuration efficiently.
Thank you,
Mikey
