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.

These four columns give you the minimum amount of information. Some options require a restart of the sql engine while others are dynamic. As you can see from these results, there is no indication which options are which. If you query sys.configurations you can see additional information.

You get a better description of what the option does, a “is_dynamic” column to let you know which options can be set on the fly and a “is_advanced” column to show what options are hidden when “show advanced options” is set to 0. The “config_value” and “run_value” are shown as “value” and “value_in_use” which is an improvement in my opinion.

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 Cain¹, 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.