Continuing on from the previous post on sys.databases, DBAs may/may not have noticed the adds/removes/tweaks occurring behind the scenes as each version changes.
A nice one of note is the allowing the backup checksums by default from SQL Server 2014.
Below is a breakdown of sys.configurations changes since SQL Server 2005.
SQL Server 2008
| Setting | Change |
| Web Assistant Procedures | Removed |
| access check cache bucket count | Added |
| access check cache quota | Added |
| affinity64 mask | Added |
| affinity64 I/O mask | Added |
| backup compression default | Added |
| EKM provider enabled | Added |
| filestream access level | Added |
| optimize for ad hoc workloads | Added |
| max text repl size (B) | Maximum value changed from 0 to -1 |
| min server memory (MB) | Default value changed from 8 to 16 |
SQL Server 2008 R2
| Setting | Change |
| max degree of parallelism | Maximum value changed from 64 to 1024 |
SQL Server 2012
| Setting | Change |
| awe enabled | Removed |
| SQL Mail XPs | Removed |
| contained database authentication | Added |
| EKM provider enabled | Is dynamic changed from 0 to 1 |
| max degree of parallelism | Maximum value changed from 1024 to 32767 |
| max server memory (MB) | Minimum value changed from 16 to 128 |
| max worker threads | Is dynamic changed from 0 to 1 |
| max worker threads | Maximum value changed from 32767 to 65535 |
| remote login timeout (s) | Default value changed from 20 to 10 |
SQL Server 2014
| Setting | Change |
| backup checksum default | Added |
SQL Server 2016
| Setting | Change |
| allow polybase export | Added |
| automatic soft-NUMA disabled | Added |
| external scripts enabled | Added |
| hadoop connectivity | Added |
| polybase network encryption | Added |
| remote data archive | Added |