internal/Get-SqlDefaultSpConfigure.ps1

function Get-SqlDefaultSpConfigure {
    <#
        .SYNOPSIS
        Internal function. Returns the default sp_configure options for a given version of SQL Server.
 
        .NOTES
        Server Configuration Options BOL (links subject to change):
        SQL Server 2017 - https://technet.microsoft.com/en-us/library/ms189631(v=sql.140).aspx
        SQL Server 2016 - https://technet.microsoft.com/en-us/library/ms189631(v=sql.130).aspx
        SQL Server 2014 - http://technet.microsoft.com/en-us/library/ms189631(v=sql.120).aspx
        SQL Server 2012 - http://technet.microsoft.com/en-us/library/ms189631(v=sql.110).aspx
        SQL Server 2008 R2 - http://technet.microsoft.com/en-us/library/ms189631(v=sql.105).aspx
        SQL Server 2008 - http://technet.microsoft.com/en-us/library/ms189631(v=sql.100).aspx
        SQL Server 2005 - http://technet.microsoft.com/en-us/library/ms189631(v=sql.90).aspx
        SQL Server 2000 - http://technet.microsoft.com/en-us/library/aa196706(v=sql.80).aspx (requires PDF download)
 
        .EXAMPLE
        Get-SqlDefaultSpConfigure -SqlVersion 11
        Returns a list of sp_configure (sys.configurations) items for SQL 2012.
 
#>

    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [Alias("Version")]
        [object]$SqlVersion
    )

    switch ($SqlVersion) {

        #region SQL2000
        8 {
            [pscustomobject]@{
                "affinity mask"                  = 0
                "allow updates"                  = 0
                "aweenabled"                     = 0
                "c2 audit mode"                  = 0
                "cost threshold for parallelism" = 5
                "Cross DB Ownership Chaining"    = 0
                "cursor threshold"               = -1
                "default full-text language"     = 1033
                "default language"               = 0
                "fill factor (%)"                = 0
                "index create memory (KB)"       = 0
                "lightweight pooling"            = 0
                "locks"                          = 0
                "max degree of parallelism"      = 0
                "max server memory (MB)"         = 2147483647
                "max text repl size (B)"         = 65536
                "max worker threads"             = 255
                "media retention"                = 0
                "min memory per query (KB)"      = 1024
                "min server memory (MB)"         = 0
                "Using Nested Triggers"          = 1
                "network packet size (B)"        = 4096
                "open objects"                   = 0
                "priority boost"                 = 0
                "query governor cost limit"      = 0
                "query wait (s)"                 = -1
                "recovery interval (min)"        = 0
                "remoteaccess"                   = 1
                "remotelogin timeout"            = 20
                "remote proc trans"              = 0
                "remote query timeout (s)"       = 600
                "scan for startup procs"         = 0
                "set working set size"           = 0
                "show advanced options"          = 0
                "two digityear cutoff"           = 2049
                "user connections"               = 0
                "user options"                   = 0
            }
        }
        #endregion SQL2000

        #region SQL2005
        9 {
            [pscustomobject]@{
                "Ad Hoc Distributed Queries"         = 0
                "affinity I/O mask"                  = 0
                "affinity64 I/O mask"                = 0
                "affinity mask"                      = 0
                "affinity64 mask"                    = 0
                "Agent XPs"                          = 0
                "allow updates"                      = 0
                "awe enabled"                        = 0
                "blocked process threshold (s)"      = 0
                "c2 audit mode"                      = 0
                "clr enabled"                        = 0
                "common criteria compliance enabled" = 0
                "cost threshold for parallelism"     = 5
                "cross db ownership chaining"        = 0
                "cursor threshold"                   = -1
                "Database Mail XPs"                  = 0
                "default full-text language"         = 1033
                "default language"                   = 0
                "default trace enabled"              = 1
                "disallow results from triggers"     = 0
                "fill factor (%)"                    = 0
                "ft crawl bandwidth (max)"           = 100
                "ft crawl bandwidth (min)"           = 0
                "ft notify bandwidth (max)"          = 100
                "ft notify bandwidth (min)"          = 0
                "index create memory (KB)"           = 0
                "in-doubt xact resolution"           = 0
                "lightweight pooling"                = 0
                "locks"                              = 0
                "max degree of parallelism"          = 0
                "max full-text crawl range"          = 4
                "max server memory (MB)"             = 2147483647
                "max text repl size (B)"             = 65536
                "max worker threads"                 = 0
                "media retention"                    = 0
                "min memory per query (KB)"          = 1024
                "min server memory (MB)"             = 8
                "nested triggers"                    = 1
                "network packet size (B)"            = 4096
                "Ole Automation Procedures"          = 0
                "open objects"                       = 0
                "PH timeout (s)"                     = 60
                "precompute rank"                    = 0
                "priority boost"                     = 0
                "query governor cost limit"          = 0
                "query wait (s)"                     = -1
                "recovery interval (min)"            = 0
                "remote access"                      = 1
                "remote admin connections"           = 0
                "remote login timeout (s)"           = 20
                "remote proc trans"                  = 0
                "remote query timeout (s)"           = 600
                "Replication XPs"                    = 0
                "scan for startup procs"             = 0
                "server trigger recursion"           = 1
                "set working set size"               = 0
                "show advanced options"              = 0
                "SMO and DMO XPs"                    = 1
                "SQL Mail XPs"                       = 0
                "transform noise words"              = 0
                "two digit year cutoff"              = 2049
                "user connections"                   = 0
                "User Instance Timeout"              = 60
                "user instances enabled"             = 0
                "user options"                       = 0
                "Web Assistant Procedures"           = 0
                "xp_cmdshell"                        = 0
            }
        }

        #endregion SQL2005

        #region SQL2008&2008R2
        10 {
            [pscustomobject]@{
                "access check cache bucket count"    = 0
                "access check cache quota"           = 0
                "ad hoc distributed queries"         = 0
                "affinity I/O mask"                  = 0
                "affinity64 I/O mask"                = 0
                "affinity mask"                      = 0
                "affinity64 mask"                    = 0
                "Agent XPs"                          = 0
                "allow updates"                      = 0
                "awe enabled"                        = 0
                "backup compression default"         = 0
                "blocked process threshold (s)"      = 0
                "c2 audit mode"                      = 0
                "clr enabled"                        = 0
                "common criteria compliance enabled" = 0
                "cost threshold for parallelism"     = 5
                "cross db ownership chaining"        = 0
                "cursor threshold"                   = -1
                "Database Mail XPs"                  = 0
                "default full-text language"         = 1033
                "default language"                   = 0
                "default trace enabled"              = 1
                "disallow results from triggers"     = 0
                "EKM provider enabled"               = 0
                "filestream access level"            = 0
                "fill factor (%)"                    = 0
                "ft crawl bandwidth (max)"           = 100
                "ft crawl bandwidth (min)"           = 0
                "ft notify bandwidth (max)"          = 100
                "ft notify bandwidth (min)"          = 0
                "index create memory (KB)"           = 0
                "in-doubt xact resolution"           = 0
                "lightweight pooling"                = 0
                "locks"                              = 0
                "max degree of parallelism"          = 0
                "max full-text crawl range"          = 4
                "max server memory (MB)"             = 2147483647
                "max text repl size (B)"             = 65536
                "max worker threads"                 = 0
                "media retention"                    = 0
                "min memory per query (KB)"          = 1024
                "min server memory (MB)"             = 0
                "nested triggers"                    = 1
                "network packet size (B)"            = 4096
                "Ole Automation Procedures"          = 0
                "open objects"                       = 0
                "optimize for ad hoc workloads"      = 0
                "PH timeout (s)"                     = 60
                "precompute rank"                    = 0
                "priority boost"                     = 0
                "query governor cost limit"          = 0
                "query wait (s)"                     = -1
                "recovery interval (min)"            = 0
                "remote access"                      = 1
                "remote admin connections"           = 0
                "remote login timeout (s)"           = 20
                "remote proc trans"                  = 0
                "remote query timeout (s)"           = 600
                "Replication XPs"                    = 0
                "scan for startup procs"             = 0
                "server trigger recursion"           = 1
                "set working set size"               = 0
                "show advanced options"              = 0
                "SMO and DMO XPs"                    = 1
                "SQL Mail XPs"                       = 0
                "transform noise words"              = 0
                "two digit year cutoff"              = 2049
                "user connections"                   = 0
                "User Instance Timeout"              = 60
                "user instances enabled"             = 0
                "user options"                       = 0
                "xp_cmdshell"                        = 0
            }
        }
        #endregion SQL2008&2008R2

        #region SQL2012
        11 {
            [pscustomobject]@{
                "access check cache bucket count"    = 0
                "access check cache quota"           = 0
                "ad hoc distributed queries"         = 0
                "affinity I/O mask"                  = 0
                "affinity64 I/O mask"                = 0
                "affinity mask"                      = 0
                "affinity64 mask"                    = 0
                "Agent XPs"                          = 0
                "allow updates"                      = 0
                "backup compression default"         = 0
                "blocked process threshold (s)"      = 0
                "c2 audit mode"                      = 0
                "clr enabled"                        = 0
                "common criteria compliance enabled" = 0
                "contained database authentication"  = 0
                "cost threshold for parallelism"     = 5
                "cross db ownership chaining"        = 0
                "cursor threshold"                   = -1
                "Database Mail XPs"                  = 0
                "default full-text language"         = 1033
                "default language"                   = 0
                "default trace enabled"              = 1
                "disallow results from triggers"     = 0
                "EKM provider enabled"               = 0
                "filestream access level"            = 0
                "fill factor (%)"                    = 0
                "ft crawl bandwidth (max)"           = 100
                "ft crawl bandwidth (min)"           = 0
                "ft notify bandwidth (max)"          = 100
                "ft notify bandwidth (min)"          = 0
                "index create memory (KB)"           = 0
                "in-doubt xact resolution"           = 0
                "lightweight pooling"                = 0
                "locks"                              = 0
                "max degree of parallelism"          = 0
                "max full-text crawl range"          = 4
                "max server memory (MB)"             = 2147483647
                "max text repl size (B)"             = 65536
                "max worker threads"                 = 0
                "media retention"                    = 0
                "min memory per query (KB)"          = 1024
                "min server memory (MB)"             = 0
                "nested triggers"                    = 1
                "network packet size (B)"            = 4096
                "Ole Automation Procedures"          = 0
                "open objects"                       = 0
                "optimize for ad hoc workloads"      = 0
                "PH_timeou"                          = 60
                "precompute rank"                    = 0
                "priority boost"                     = 0
                "query governor cost limit"          = 0
                "query wait (s)"                     = -1
                "recovery interval (min)"            = 0
                "remote access"                      = 1
                "remote admin connections"           = 0
                "remote login timeout (s)"           = 10
                "remote proc trans"                  = 0
                "remote query timeout (s)"           = 600
                "Replication XPs"                    = 0
                "scan for startup procs"             = 0
                "server trigger recursion"           = 1
                "set working set size"               = 0
                "show advanced options"              = 0
                "SMO and DMO XPs"                    = 1
                "transform noise words"              = 0
                "two digit year cutoff"              = 2049
                "user connections"                   = 0
                "user options"                       = 0
                "xp_cmdshell"                        = 0
            }
        }
        #endregion SQL2012

        #region SQL2014
        12 {
            [pscustomobject]@{
                "access check cache bucket count"    = 0
                "access check cache quota"           = 0
                "ad hoc distributed queries"         = 0
                "affinity I/O mask"                  = 0
                "affinity64 I/O mask"                = 0
                "affinity mask"                      = 0
                "affinity64 mask"                    = 0
                "Agent XPs"                          = 0
                "allow updates"                      = 0
                "backup checksum default"            = 0
                "backup compression default"         = 0
                "blocked process threshold (s)"      = 0
                "c2 audit mode"                      = 0
                "clr enabled"                        = 0
                "common criteria compliance enabled" = 0
                "contained database authentication"  = 0
                "cost threshold for parallelism"     = 5
                "cross db ownership chaining"        = 0
                "cursor threshold"                   = -1
                "Database Mail XPs"                  = 0
                "default full-text language"         = 1033
                "default language"                   = 0
                "default trace enabled"              = 1
                "disallow results from triggers"     = 0
                "EKM provider enabled"               = 0
                "filestream access level"            = 0
                "fill factor (%)"                    = 0
                "ft crawl bandwidth (max)"           = 100
                "ft crawl bandwidth (min)"           = 0
                "ft notify bandwidth (max)"          = 100
                "ft notify bandwidth (min)"          = 0
                "index create memory (KB)"           = 0
                "in-doubt xact resolution"           = 0
                "lightweight pooling"                = 0
                "locks"                              = 0
                "max degree of parallelism"          = 0
                "max full-text crawl range"          = 4
                "max server memory (MB)"             = 2147483647
                "max text repl size (B)"             = 65536
                "max worker threads"                 = 0
                "media retention"                    = 0
                "min memory per query (KB)"          = 1024
                "min server memory (MB)"             = 0
                "nested triggers"                    = 1
                "network packet size (B)"            = 4096
                "Ole Automation Procedures"          = 0
                "open objects"                       = 0
                "optimize for ad hoc workloads"      = 0
                "PH timeout (s)"                     = 60
                "precompute rank"                    = 0
                "priority boost"                     = 0
                "query governor cost limit"          = 0
                "query wait (s)"                     = -1
                "recovery interval (min)"            = 0
                "remote access"                      = 1
                "remote admin connections"           = 0
                "remote login timeout (s)"           = 10
                "remote proc trans"                  = 0
                "remote query timeout (s)"           = 600
                "Replication XPs"                    = 0
                "scan for startup procs"             = 0
                "server trigger recursion"           = 1
                "set working set size"               = 0
                "show advanced options"              = 0
                "SMO and DMO XPs"                    = 1
                "transform noise words"              = 0
                "two digit year cutoff"              = 2049
                "user connections"                   = 0
                "user options"                       = 0
                "xp_cmdshell"                        = 0
            }
        }
        #endregion SQL2014

        #region SQL2016
        13 {
            [pscustomobject]@{
                "access check cache bucket count"        = 0
                "access check cache quota"               = 0
                "ad hoc distributed queries"             = 0
                "affinity I/O mask"                      = 0
                "affinity64 I/O mask"                    = 0
                "affinity mask"                          = 0
                "affinity64 mask"                        = 0
                "Agent XPs"                              = 0
                "allow updates"                          = 0
                "automatic soft-NUMA disabled"           = 0
                "backup checksum default"                = 0
                "backup compression default"             = 0
                "blocked process threshold (s)"          = 0
                "c2 audit mode"                          = 0
                "clr enabled"                            = 0
                "common criteria compliance enabled"     = 0
                "contained database authentication"      = 0
                "cost threshold for parallelism"         = 5
                "cross db ownership chaining"            = 0
                "cursor threshold"                       = -1
                "Database Mail XPs"                      = 0
                "default full-text language"             = 1033
                "default language"                       = 0
                "default trace enabled"                  = 1
                "disallow results from triggers"         = 0
                "EKM provider enabled"                   = 0
                "external scripts enabled"               = 0
                "filestream access level"                = 0
                "fill factor (%)"                        = 0
                "ft crawl bandwidth (max)"               = 100
                "ft crawl bandwidth (min)"               = 0
                "ft notify bandwidth (max)"              = 100
                "ft notify bandwidth (min)"              = 0
                "index create memory (KB)"               = 0
                "in-doubt xact resolution"               = 0
                "lightweight pooling"                    = 0
                "locks"                                  = 0
                "max degree of parallelism"              = 0
                "max full-text crawl range"              = 4
                "max server memory (MB)"                 = 2147483647
                "max text repl size (B)"                 = 65536
                "max worker threads"                     = 0
                "media retention"                        = 0
                "min memory per query (KB)"              = 1024
                "min server memory (MB)"                 = 0
                "nested triggers"                        = 1
                "network packet size (B)"                = 4096
                "Ole Automation Procedures"              = 0
                "open objects"                           = 0
                "optimize for ad hoc workloads"          = 0
                "PH timeout (s)"                         = 60
                "PolyBase Hadoop and Azure blob storage" = 0
                "precompute rank"                        = 0
                "priority boost"                         = 0
                "query governor cost limit"              = 0
                "query wait (s)"                         = -1
                "recovery interval (min)"                = 0
                "remote access"                          = 1
                "remote admin connections"               = 0
                "remote data archive"                    = 0
                "remote login timeout (s)"               = 10
                "remote proc trans"                      = 0
                "remote query timeout (s)"               = 0
                "Replication XPs"                        = 0
                "scan for startup procs"                 = 0
                "server trigger recursion"               = 1
                "set working set size"                   = 0
                "show advanced options"                  = 0
                "SMO and DMO XPs"                        = 1
                "transform noise words"                  = 0
                "two digit year cutoff"                  = 2049
                "user connections"                       = 0
                "user options"                           = 0
                "xp_cmdshell"                            = 0
            }
        }
        #endregion SQL2016

        #region SQL2017
        14 {
            [pscustomobject]@{
                "access check cache bucket count"    =    0
                "access check cache quota"           =    0
                "Ad Hoc Distributed Queries"         =    0
                "affinity I / O mask"                =    0
                "affinity mask"                      =    0
                "affinity64 I / O mask"              =    0
                "affinity64 mask"                    =    0
                "Agent XPs"                          =    0
                "allow polybase export"              =    0
                "allow updates"                      =    0
                "automatic soft-NUMA disabled"       =    0
                "backup checksum default"            =    0
                "backup compression default"         =    0
                "blocked process threshold (s)"      =    0
                "c2 audit mode"                      =    0
                "clr enabled"                        =    0
                "clr strict security"                =    1
                "common criteria compliance enabled" =    0
                "contained database authentication"  =    0
                "cost threshold for parallelism"     =    5
                "cross db ownership chaining"        =    0
                "cursor threshold"                   =    -1
                "Database Mail XPs"                  =    0
                "default full-text language"         =    1033
                "default language"                   =    0
                "default trace enabled"              =    1
                "disallow results from triggers"     =    0
                "EKM provider enabled"               =    0
                "external scripts enabled"           =    0
                "filestream access level"            =    0
                "fill factor ( % )"                  =    0
                "ft crawl bandwidth (max)"           =    100
                "ft crawl bandwidth (min)"           =    0
                "ft notify bandwidth (max)"          =    100
                "ft notify bandwidth (min)"          =    0
                "hadoop connectivity"                =    0
                "index create memory (KB)"           =    0
                "in-doubt xact resolution"           =    0
                "lightweight pooling"                =    0
                "locks"                              =    0
                "max degree of parallelism"          =    0
                "max full-text crawl range"          =    4
                "max server memory (MB)"             =    2147483647
                "max text repl size (B)"             =    65536
                "max worker threads"                 =    0
                "media retention"                    =    0
                "min memory per query (KB)"          =    1024
                "min server memory (MB)"             =    0
                "nested triggers"                    =    1
                "network packet size (B)"            =    4096
                "Ole Automation Procedures"          =    0
                "open objects"                       =    0
                "optimize for ad hoc workloads"      =    0
                "PH timeout (s)"                     =    60
                "polybase network encryption"        =    1
                "precompute rank"                    =    0
                "priority boost"                     =    0
                "query governor cost limit"          =    0
                "query wait (s)"                     =    -1
                "recovery interval (min)"            =    0
                "remote access"                      =    1
                "remote admin connections"           =    0
                "remote data archive"                =    0
                "remote login timeout (s)"           =    10
                "remote proc trans"                  =    0
                "remote query timeout (s)"           =    600
                "Replication XPs"                    =    0
                "scan for startup procs"             =    0
                "server trigger recursion"           =    1
                "set working set size"               =    0
                "show advanced options"              =    0
                "SMO and DMO XPs"                    =    1
                "transform noise words"              =    0
                "two digit year cutoff"              =    2049
                "user connections"                   =    0
                "user options"                       =    0
                "xp_cmdshell"                        =    0

            }
        }
        #endregion SQL2017


    }

}