5 minutes
SQLServer, sp_configure and PowerShell

Introduction
If you’ve been responsible for an instance of SQL Server for any length of time you have probably dealt with sp_configure
to change configuration settings at the server level.
I have been using SQL Server since v6.5 and it was a thing then too.
This is not a post about what the settings are or what they should be set to.
There are plenty of resources out there for both.
This is about how to see and set these options.
sp_configure basics
The usual way to use sp_configure
is to open SSMS and exec it. The one setting I will cover in this post is “show advanced options”.
If this is set to 0 you see a limited set of options and if set to 1, you get to see everything.
Running sp_configure
results in something like this.
PowerShell SMO Object
If you use either the sqlserver or dbatools modules you can see the configurations in the SMO object.
$sqlconn = Get-SqlInstance -ServerInstance SQL01
<# or #>
$sqlconn = Connect-DbaInstance -SQLInstance SQL01
$sqlconn.configurations.Properties
DisplayName : Agent XPs
Number : 16384
Minimum : 0
Maximum : 1
IsDynamic : True
IsAdvanced : True
Description : Enable or disable Agent XPs
RunValue : 1
ConfigValue : 1
DisplayName : Database Mail XPs
Number : 16386
Minimum : 0
Maximum : 1
IsDynamic : True
IsAdvanced : True
Description : Enable or disable Database Mail XPs
RunValue : 1
ConfigValue : 1
...
Managing sp_configure with dbatools
If I am only changing one option on one server, I will usually just use SSMS. When dealing with more than one server I typically turn to PowerShell.
Set sp_configure
Using
Set-DbaSpConfigure
to change your settings is nice and straight forward.
Most of the dbatools commands take a custom DbaInstanceParameter
type for the SqlInstance
parameter so you can pass multiple instances at once.
For more on what all you can pass, check out
dbatools github
The Name
can also take multiple values at once, however the Value
can only take one.
This means you can set “XPCmdShellEnabled” and “ShowAdvancedOptions” to 1 on multiple servers.
If you want different values you will need to make multiple passes.
# Multiple instances, multiple configs
Set-DbaSpConfigure `
-SqlInstance Svr01,Svr2 `
-Name XpCmdShellEnabled,ShowAdvancedOptions `
-Value 1
# Different values require individual passes.
Set-DbaSpConfigure `
-SqlInstance Svr01,Svr2 `
-Name XpCmdShellEnabled `
-Value 0
Set-DbaSpConfigure `
-SqlInstance Svr01,Svr2 `
-Name ShowAdvancedOptions `
-Value 1
Get sp_configure
Getting the current sp_configure
values from any of the sources previously mentioned leave a couple of things out.
Is this the default value or has it been changed?
What was the default value?
The results from
Get-DbaSpConfigrure
have a couple of added fields, IsRunningDefaultValue
and DefaultValue
, that let us know the answers.
We can now filter for all of our settings that are different from the default and see what they are.
# IsRunningDefaultValue returns True/False
Get-DbaSpConfigure `
-SqlInstance Svr01 | Where-Object {!($_.IsRunningDefaultValue)}
ComputerName : Svr01
ConfiguredValue : 24000
DefaultValue : 2147483647
Description : Maximum size of server memory (MB)
DisplayName : max server memory (MB)
InstanceName : MSSQLSERVER
IsAdvanced : True
IsDynamic : True
IsRunningDefaultValue : False
MaxValue : 2147483647
MinValue : 128
Name : MaxServerMemory
RunningValue : 24000
SqlInstance : Svr01
ComputerName : Svr01
ConfiguredValue : 1
DefaultValue : 0
Description : Enable compression of backups by default
DisplayName : backup compression default
InstanceName : MSSQLSERVER
IsAdvanced : False
IsDynamic : True
IsRunningDefaultValue : False
MaxValue : 1
MinValue : 0
Name : DefaultBackupCompression
RunningValue : 1
SqlInstance : Svr01
...
At first glance this may not seem like a big deal until you realize that this information is not available anywhere in SQL.
Digging into the code, you can see that the author, Nic Cain1, wrote an internal helper function called Get-SqlDefaultSpConfigure. Looking through that, we can see that Nic went and found the Microsoft documentation for all versions fo SQL back to 2000 to find the defaults.
This is a great example of the SQL community going the extra mile to create useful tools for us to use.
Export sp_configure
Exporting your current sp_configure settings is easy with
Export-DbaSpConfigure
.
Adding this to your backup process can be useful.
This simple one liner
Export-DbaSpConfigure -SqlInstance Server01 -FilePath .\Server01-spconfig-$((get-date).ToString("yyyyMMdd")).sql
results in a file that contains this
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;
EXEC sp_configure 'locks' , 0;
EXEC sp_configure 'open objects' , 0;
EXEC sp_configure 'fill factor (%)' , 0;
EXEC sp_configure 'disallow results from triggers' , 0;
EXEC sp_configure 'nested triggers' , 1;
EXEC sp_configure 'server trigger recursion' , 1;
EXEC sp_configure 'remote access' , 1;
...
Conclusion
So as you can see, once again, putting SQL Server and PowerShell together can make your life easier.
Thanks for reading.
-
Pulled from the notes section of
Get-DbaSpConfigure
↩︎

Comments powered by Talkyard.