We mainly deal with big data, the performance requirement is higher, want to please grant instruction each master 2008 behind these versions of what are the major features, although I know something, feel is not comprehensive,
Thank you very much!

CodePudding user response:

See the website document directly
https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation? View=SQL server - 2017
Inside What 's new

CodePudding user response:

Hi Tim - 2009,

The first SQL server2008
Will not be supported by Microsoft, the future will not update any patches, from SQL server2008
After the appearance of many major update, I suggest you to look at the official documentation
https://docs.microsoft.com/zh-cn/sql/sql-server/what-s-new-in-sql-server-ver15? View=sqlallproducts - allversions

Best regards,
Dedmon Dai

CodePudding user response:

I check the related information, the Internet also copied
SQL SERVER 2008 R2


R2 logo said this is an intermediate version of SQL Server, rather than a major version. This version of my clients at present, using or very much, stable performance, although less new features:
New data center, maximum support 256 nuclear.
Unicode compression

(for Unicode storage provides a simple compression scheme, through the Unicode compression, can reduce the Unicode characters of space occupancy)


SQL SERVER 2012

AlwaysOn
Columnstore index
Strengthen the function of auditing
Big data support





SQL SERVER 2014

This version of the new special effects much more special, and very useful, you can know more about.


Memory tuning table
Backup encryption
In view of the new design of the cardinality estimate
AlwaysOn enhancements
Continuous delay (to some or all of the transaction specified as delay lasting transaction, so that they can shorten the delay)
Partition switch and index generated

(website written Partition Switching and Indexing, the feeling is problematic, is actually a single Partition of a partitioned table can be rebuilt)
Column storage index
Buffer pool extension is to use SSD extension buffer pool
Incremental statistical information
Resource governor enhancements
(before - only control CPU and memory, 2014 start can control the IO)

DBCC CHECK support maxdop tip





SQL SERVER 2016


The whole encryption (Always Encrypted)
JSON support
More TempDB for database file

(also support before, in 2014 began in the error log, in 2016, when the installation can be set up,)
Query Store
(a few days ago to go to Microsoft's training, also very good, can help solve the problem of parameter of sniffing, can also be used when the database upgrade it)
Support R language
Live Quer y St at ist ics
(can be more clearly see the execution plan of overhead (water flow))



SQL SERVER 2017

Recoverable online index rebuild

Allows you in the event of a failure (such as failure to switch to copy or insufficient disk space) back online after the index rebuild operation,

IDENTITY_CACHE option

When this option is set to OFF, it can avoid the accident to restart the server or failover to the secondary server, the clearance between the identity column values

The CLR in.net Framework used in the code access security (CAS), the Framework can no longer support security boundary,

Use PERMISSION_SET=SAFE to create the CLR assemblies may be able to access external system resources, calls to unmanaged code and obtain the sysadmin rights

Chart database functionality

For a many-to-many relationship modeling

The Read - scale the availability groups without cluster

Can not rely on cluster cases, structures, read the usability group, share the pressure reading, but can't achieve the high availability at this time,

R/PYTHON function of machine learning

CodePudding user response:

Advised to now test environment test performance, and upgrading in production environment

CodePudding user response:

See the website document directly
https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation? View=SQL server - 2017
Inside What 's new

CodePudding user response:

Hi Tim - 2009,

The first SQL server2008
Will not be supported by Microsoft, the future will not update any patches, from SQL server2008
After the appearance of many major update, I suggest you to look at the official documentation
https://docs.microsoft.com/zh-cn/sql/sql-server/what-s-new-in-sql-server-ver15? View=sqlallproducts - allversions

Best regards,
Dedmon Dai

CodePudding user response:

For high performance requirements, if concurrent high, it is recommended that the first test performance, under some scenarios version upgrades do not necessarily performance upgrade SQL Server 2016 ~ 2000 performance optimization, design QQ: 315054403 rural in jiaxing

CodePudding user response:

I check the related information, the Internet also copied
SQL SERVER 2008 R2


R2 logo said this is an intermediate version of SQL Server, rather than a major version. This version of my clients at present, using or very much, stable performance, although less new features:
New data center, maximum support 256 nuclear.
Unicode compression

(for Unicode storage provides a simple compression scheme, through the Unicode compression, can reduce the Unicode characters of space occupancy)


SQL SERVER 2012

AlwaysOn
Columnstore index
Strengthen the function of auditing
Big data support





SQL SERVER 2014

This version of the new special effects much more special, and very useful, you can know more about.


Memory tuning table
Backup encryption
In view of the new design of the cardinality estimate
AlwaysOn enhancements
Continuous delay (to some or all of the transaction specified as delay lasting transaction, so that they can shorten the delay)
Partition switch and index generated

(website written Partition Switching and Indexing, the feeling is problematic, is actually a single Partition of a partitioned table can be rebuilt)
Column storage index
Buffer pool extension is to use SSD extension buffer pool
Incremental statistical information
Resource governor enhancements
(before - only control CPU and memory, 2014 start can control the IO)

DBCC CHECK support maxdop tip





SQL SERVER 2016


The whole encryption (Always Encrypted)
JSON support
More TempDB for database file

(also support before, in 2014 began in the error log, in 2016, when the installation can be set up,)
Query Store
(a few days ago to go to Microsoft's training, also very good, can help solve the problem of parameter of sniffing, can also be used when the database upgrade it)
Support R language
Live Quer y St at ist ics
(can be more clearly see the execution plan of overhead (water flow))



SQL SERVER 2017

Recoverable online index rebuild

Allows you in the event of a failure (such as failure to switch to copy or insufficient disk space) back online after the index rebuild operation,

IDENTITY_CACHE option

When this option is set to OFF, it can avoid the accident to restart the server or failover to the secondary server, the clearance between the identity column values

The CLR in.net Framework used in the code access security (CAS), the Framework can no longer support security boundary,

Use PERMISSION_SET=SAFE to create the CLR assemblies may be able to access external system resources, calls to unmanaged code and obtain the sysadmin rights

Chart database functionality

For a many-to-many relationship modeling

The Read - scale the availability groups without cluster

Can not rely on cluster cases, structures, read the usability group, share the pressure reading, but can't achieve the high availability at this time,

R/PYTHON function of machine learning

CodePudding user response:

Advised to now test environment test performance, and upgrading in production environment

CodePudding user response:

To deal with large data, mainly has the following improvements:
1, column storage index
Basically, the big data standard column storage, generally speaking, there are more than 500 gb of OLTP data, can the above storage,
2, the partition
More than ten million lines of table, can consider to partition, partition of 2008 limit is small, as if 1000, too long don't remember, new version should be 15000,
nullnullnullnullnullnullnullnullnullnullnullnullnullnull