(I’m ‘standing in for Brad, who is busy ..er.. somewhere, and I found this unpublished piece by Brad and I. I have no idea what the diagram at the end means, but he seemed to feel it was important
Phil Factor)

Server Configuration Settings

SQL Server has about 70 instance-level settings, more with each version, that can have a significant effect on SQL Server’s behavior and performance. Most of these should be left at their default settings, but occasionally you will need to change them. As the DBA, you should be familiar with all of the settings and what they do. In addition, if any of the settings have been changed from their default values, you should know which settings have been changed, to what values, and why they were changed. This can save a great deal of detective work when troubleshooting SQL Server-related problems.

How to Collect This Data?

While not all of the configuration options can be viewed from various parts of SSMS. Because of this, the easiest way to see the options quickly is to run the following script.

SP_CONFIGURE ‘show advanced options’, 1

RECONFIGURE ;

GO

sp_configure ;
GO

Microsoft has somewhat arbitrarily divided instance-level configuration options into two categories: standard and advanced. In order to see all of the options, you must set an instance-level option to allow the advanced options to be seen, which is accomplished by SP_CONFIGURE ‘show advanced options’, 1 in the above script. Once this setting has run one time, and the setting changed, all you have to do to view the settings is to run sp_configure by itself.

After running the above script, you will notice that each option displays four columns, which have these meanings:

• minimum: The smallest legal value for the setting.
• maximum: The largest legal value for the setting.
• config_value: This is the value this option will take on once it is properly configured, assuming that it is different from the run_value. If the option has been properly configured, then it will be equal to the run_value. If you notice that the config_value and the run_value are not the same, and you have not changed this setting yourself, it is possible that someone tried to change this setting, but never fully completed all the necessary steps. These discrepancies should be investigated to find out why the option was never properly changed.
• run_value: This is the value of the option as it is running now, and is the key value you should look at during the health check.

Collecting and saving configuration information for a number of servers.

You can also get these values via SMO. This has a particular value because you can then save the settings in source control and this will tell you of all the changes. If you regularly schedule the collection of this information, then your source control system will inform you of any changes in your server configuration and, within reason, when it happened.

Here is as Powershell script that uses SMO to save the configuration information to a spreadsheet: well, actually, it used the old trick of saving it as an HTML table with the XLS filetype, which is read automatically into Excel and converted to Excel (you’d have to save it in native format, though).  Just to show off, we’ll show how you can do the server settings too (Audit Level, Backup Directory, Default File, Default Log, Login Mode etc.)

We decided for this blog-post not to save this information  as text and put in source control, because this technique has already been demonstrated with some brilliance by Grant Fritchey in his article ‘Auditing DDL Changes in SQL Server databases’ . Also, each Source Control system has a different way of saving files into source control. You’ll find it easy to save the information as a CSV file which will be treated by Source Control as if it were a script, and allow you to track changes.

# “Option Explicit”
set-psdebug -strict
$ErrorActionPreference = “stop”
# Give a list of the servers that you’d like to have configuration and settings information on.
$servernames=@(‘DaveDee, ‘Dozy’, ‘Beaky’, ‘MickAndTitch’)
#And the directory where you’d like this information stored (with the terminating backslash!)
$DirectoryToSaveTo=‘s:\MyWork\MyServerSettings\’
#Create the directory if it doesn’t exist already
if (!( Test-Path -path “$DirectoryToSaveTo“ )) #create it if not existing
{$progress =“attempting to create $DirectoryToSaveTo“

Try { New-Item “$DirectoryToSaveTo“ -type directory | out-null }

Catch [system.exception]{
Write-Error “error while $progress \n$_“
return
}
}
#Now we find and load SMO
$v = [System.Reflection.Assembly]::LoadWithPartialName( ‘Microsoft.SqlServer.SMO’)
if ((($v.FullName.Split(‘,’))[1].Split(‘=’))[1].Split(‘.’)[0] -ne ’9′) {
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMOExtended’) | out-null
}
# and for each server…
Foreach ($servername in $Servernames){ #for each server in the list
$s = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) $servername #create the server object
if ($s.ServerType -ne $null) #if it managed to find a server
{
$filename=“$servername“ -replace  ‘[\\\/\:\.]‘,‘ ‘ #remove characters that can cause problems
#and write out the configuration information
$s.configuration.properties | Sort-Object -Property DisplayName|ConvertTo-HTML >“$DirectoryToSaveTo$filename-Configuration.xls”
$s.settings.properties |Select name, value| ConvertTo-HTML >“$DirectoryToSaveTo$filename-Settings.xls”
}
$s.close
}
“Master, it is done”

Here is what the configuration settings look like in Excel,

…and here are the server settings.

You’ll notice that neither SMO nor sp_configure actually store the default settings. Assuming you have a new installation, then  the run_value is the default value. If you save this into Source Control  at this point, you can be sure of tracking changes from this default setting. If you have a server that has been around awhile, and could have had some of the options changed, how do you know what the default value should be, and even if the value has been changed? One option is to look up the default value in Books Online, under “Setting Server Configuration Options”.