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 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 (A)"=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 } } |