SQL Server 2016摘要中的新增功能-优快云博客

SQL Server 2016 became publically available on the 1^st of June 2016. Some might feel that a new SQL Server release every 2 years might be too frequent with many organization still lagging behind by running SQL Server 2012 or earlier.

SQL Server 2016于2016年6月1 ^日公开发布。有些人可能会觉得每两年发布一次新SQL Server可能过于频繁，因为许多组织仍然无法运行SQL Server 2012或更早版本。

So what is the fuss all about? Microsoft proudly mentions the following 5 topics as the pinnacle of SQL Server 2016’s offerings.

那么到底有什么大惊小怪的呢？ Microsoft自豪地提到以下5个主题作为SQL Server 2016产品的巅峰之作。

OLTP with increased performance, scalability and high availability
OLTP具有增强的性能，可伸缩性和高可用性
Enhanced data security with Always Encrypted
始终加密，增强了数据安全性
Mobile Business intelligence
移动商务智能
Advanced analytics with R Services
借助R Services进行高级分析
Consistency between your database servers and your database as a service (Azure)
数据库服务器与数据库即服务之间的一致性（Azure）

So what’s new? In this article I will do a quick summary of the new or improved features which will certainly open up a whole new world of excitement for the data professional.

那么有什么新消息？在本文中，我将简要介绍新功能或改进的功能，这些功能无疑将为数据专业人员打开一个全新的世界。

更快的结果 (Faster results)

In-memory OLTP

内存中OLTP

Memory optimized tables can now have foreign keys, check and unique constraints, triggers (WITH NATIVE_COMPILATION), large object type columns and can use any collation. Statistics for memory-optimized tables are now also auto updated.

内存优化表现在可以具有外键，检查和唯一约束，触发器（WITH NATIVE_COMPILATION），大对象类型列，并且可以使用任何排序规则。内存优化表的统计信息现在也将自动更新。

A couple of enhancements were also made to indexes from memory-optimized tables which now allows Unique indexes, columns using any collation and Nullable index key columns.

对内存优化表的索引也进行了一些增强，现在允许唯一索引，使用任何排序规则的列和可空索引键列。

In-memory analytics

内存分析

Column store indexes can be added to memory-optimized OLTP tables which allows real time analytics to be performed on your OLTP database without the risk of resource contention between your OLTP workload and your analytics workload. In order to minimize the overhead caused by maintaining clustered column store indexes, filtered column store indexes can be used.

可以将列存储索引添加到内存优化的OLTP表中，从而可以在OLTP数据库上执行实时分析，而不会在OLTP工作负载和分析工作负载之间存在资源争用的风险。为了最小化维护群集列存储索引所导致的开销，可以使用过滤后的列存储索引。

Analysis Services

分析服务

Analysis Services continues to offer 2 modes of data storage both of which have been optimized for performance in 2016.

Analysis Services继续提供2种数据存储模式，两种模式均已针对2016年的性能进行了优化。

Multi-dimensional mode

多维模式

The multi-dimensional engine has been improved to return data faster, these enhancements do not need any configuration in order to be taken advantage of but will only really be evident if you compare the baseline of your current server to that of SQL Server Analysis Services 2016.

多维引擎已得到改进，可以更快地返回数据，这些增强不需要任何配置即可利用，但只有将当前服务器的基准与SQL Server Analysis Services 2016的基准进行比较时，这些增强才会真正显现出来。

Tabular mode

表格模式

Tabular mode which is based on the same technology as column store indexes has been improved by enhancements to DAX, storage engine caching and T-SQL generation of DirectQuery mode.

通过增强DAX，存储引擎缓存和DirectQuery模式的T-SQL生成，改进了基于与列存储索引相同技术的表格模式。

数据库引擎增强 (Database engine enhancements)

TempDB

临时数据库

Trace flags for improved performance

跟踪标志可提高性能

Trace flags 1117 and 1118 which had to be enabled specifically in the past, has now been included in the database engine and has such no longer need to be activated manually.

过去必须专门启用的跟踪标志1117和1118现在已包含在数据库引擎中，因此不再需要手动激活。

The Query Store

查询存储

The Query Store is a completely new feature which will help DBA’s to troubleshoot performance issues resulting from changes in the query plan. Historical data is captured to allow for comparisons to be made between queries, plans and statistics. They Query Store is not enabled by default, so in order to use it you can enable it with this command:

查询存储是一项全新功能，它将帮助DBA解决由于查询计划的更改而导致的性能问题。捕获历史数据以允许在查询，计划和统计数据之间进行比较。默认情况下，它们的查询存储未启用。因此，可以使用以下命令启用它们：

 
 ALTER DATABASE AdventureWorks SET QUERY_STORE = ON;

Once enabled the following 4 dashboards are immediately available:

启用后，以下4个仪表板将立即可用：

Regressed Queries
回归查询
Overall Resource Consumption
整体资源消耗
Top Resource Consuming Queries
顶级资源消耗查询
Tracked Queries
跟踪查询

All of which will allow you to see the data captured in the Query Store.

所有这些都可以让您查看在查询存储中捕获的数据。

Stretch Database

拉伸数据库

The purpose of the new Stretch Database feature is to essentially split hot, warm and cold data and seamlessly move the warm data to Azure, thereby improving the performance of your hot data, reduces your on-site hardware requirements while at the same time allowing you to keep all of your data indefinitely.

新的Stretch Database功能的目的是实质上拆分热数据，热数据和冷数据，并将热数据无缝地移动到Azure，从而提高热数据的性能，降低现场硬件要求，同时允许您无限期保留所有数据。

Data access still occurs through your on-site database, and no changes to applications are required and the security model of the tables involved remains the same.

数据访问仍然通过您的现场数据库进行，不需要更改应用程序，并且所涉及表的安全模型保持不变。

Stretch Database supports Transparent Data Encryption which can be enabled to address any concerns about the security of the data in the remote data center as well as data in transit between the local and the remote data centers.

Stretch Database支持透明数据加密，可以启用它来解决有关远程数据中心中的数据以及本地和远程数据中心之间传输的数据的安全性的任何问题。

增强安全性 (Increased security)

There are 3 new security features introduces in SQL Server 2016.

SQL Server 2016中引入了3个新的安全功能。

Always Encrypted

始终加密

With Always Encrypted, the data is, well… always encrypted. The encryption takes place on the client side when it is written, and requires a special driver and an encryption key in order to be able to read the encrypted data.

使用Always Encrypted，数据将始终被加密。加密在编写时在客户端进行，并且需要特殊的驱动程序和加密密钥才能读取加密的数据。

Row Level Security

行级安全

Dynamic Data Masking

动态数据屏蔽

SQL Server 2016 contains 4 new masking functions which can prevent users from viewing sensitive data unless they have been explicitly granted permission to view it. The masking is applied to the query results and as such do not require any changes to existing applications. A DBA running a query in SSMS for instance will also only see the obfuscated data.

SQL Server 2016包含4个新的屏蔽功能，这些功能可以防止用户查看敏感数据，除非已明确授予他们查看权限。屏蔽应用于查询结果，因此不需要对现有应用程序进行任何更改。例如，在SSMS中运行查询的DBA也只会看到混淆的数据。

更好的高可用性 (Better High Availability)

AlwaysOn availability groups

AlwaysOn可用性组

Although not new to SQL Server 2016 there has been some significant improvements to availability groups in this version.

尽管对于SQL Server 2016来说不是新功能，但此版本中的可用性组已有一些重大改进。

AlwaysOn Basic Availability Groups

AlwaysOn基本可用性组

This is similar to database mirroring and will be available in the SQL Server 2016 standard. Just like mirroring this is configured on the database level, and only one secondary is allowed which is not readable.
这类似于数据库镜像，将在SQL Server 2016标准中可用。就像镜像一样，它是在数据库级别配置的，并且只允许一个不可读的辅助节点。
Group Managed Service Accounts

组托管服务帐户

The purpose of this is to facilitate the management of service accounts in large organizations. This has actually been around since SQL 2012, but in SQL Server 2016 gMSAs can be used with availability groups and failover clusters.
这样做的目的是促进大型组织中服务帐户的管理。自SQL 2012以来，这实际上已经存在，但是在SQL Server 2016中，gMSA可以与可用性组和故障转移群集一起使用。
Database failure failover

数据库故障故障转移

In previous versions a failover of an Availability group was only triggered if the sp_server_diagnostics procedure returned and error, the SQL Server service has stopped or if the instance is not responsive. I did not take into account the state of any database. Enabling the Database Level Health Detection option allows for the availability group to also trigger a failover in case a database becomes inaccessible for whatever reason.
在以前的版本中，仅当sp_server_diagnostics过程返回且出错，SQL Server服务已停止或实例没有响应时，才触发可用性组的故障转移。我没有考虑任何数据库的状态。启用“数据库级别运行状况检测”选项可使可用性组在由于某种原因而无法访问数据库的情况下也触发故障转移。
Distributed Transaction Coordinator (DTC) support

分布式事务处理协调器（DTC）支持

DTC is now supported for AlwaysOn Availability groups provided that you are running either Windows 2016 or Windows 2012 R2 with KB3090973 and that your Availability Groups have been created with the WITH DTC_SUPPORT = PER_DB option.
如果您正在运行带有KB3090973的Windows 2016或Windows 2012 R2，并且已使用WITH DTC_SUPPORT = PER_DB选项创建了可用性组，则AlwaysOn可用性组现在支持DTC。
Load balancing for readable secondary replicas

可读二级副本的负载平衡

In previous version read traffic always got directed to the first readable replica. In SQL Server 2016 multiple replicas can be specified in the READ_ONLY_ROUTING_LIST and grouped for load balancing using parenthesis. Such as :

在以前的版本中，读取流量始终定向到第一个可读副本。在SQL Server 2016中，可以在READ_ONLY_ROUTING_LIST中指定多个副本并将其分组以使用括号进行负载平衡。如：
```
 
 READ_ONLY_ROUTING_LIST = (('SVR1','SVR2','SVR3'),'SVR4')
 
```
In this instance SVR1, SVR2 and SVR3 will be accessed as a load balanced set and SVR4 will only be accessed if none of the servers in the load balanced set is accessible.
在这种情况下，将以负载平衡集的形式访问SVR1，SVR2和SVR3，并且仅当负载平衡集中的所有服务器均不可访问时，才访问SVR4。
Multiple automatic failover targets

多个自动故障转移目标

In SQL Server 2016 you can now specify up to 3 automatic failover targets , as long as one of the targets are in sync failover can occur automatically with no data loss.
现在，在SQL Server 2016中，您最多可以指定3个自动故障转移目标，只要其中一个目标处于同步状态，就可以自动进行故障转移而不会丢失数据。
Improved log transport performance

改善日志传输性能

The log-capture step on the primary server and the redo step on the secondary used to be single threaded in previous versions of SQL. In SQL Server 2016 these two steps are multi-threaded which significantly increases performance.

在以前SQL版本中，主服务器上的日志捕获步骤和辅助服务器上的重做步骤以前是单线程的。在SQL Server 2016中，这两个步骤是多线程的，可显着提高性能。

无缝数据集成 (Seamless Data Integration)

SQL Server 2016 introduces some useful features which facilitates access to different types of data, including integration between relational data, unstructured and semi-structured data.

SQL Server 2016引入了一些有用的功能，这些功能有助于访问不同类型的数据，包括关系数据，非结构化和半结构化数据之间的集成。

Temporal data

时间数据

SQL Server 2016 introduces temporal tables for keeping track of the state of data at any specific point in time. Temporal tables actually consists out of 2 tables. The current or temporal table and the historical table which uses page compression for space considerations. As changes occur on the temporal table a copy of the data is stored in the historical table.

SQL Server 2016引入了临时表，用于跟踪任何特定时间点的数据状态。时态表实际上由2个表组成。当前或临时表以及历史表，出于空间考虑，它们使用页面压缩。当临时表上发生更改时，数据副本将存储在历史表中。

$C:\Users\minettes\AppData\Local\Temp\SNAGHTML35e05f3a.PNG$

JSON

JSON格式

The addition JavaScript Object Notation support in SQL Server 2016 allows you to import and export data to and from JSON format into your relational database. This means that both relational and non-relational data structures can live together in one environment.

SQL Server 2016中的附加JavaScript Object Notation支持使您可以将数据与JSON格式之间的数据导入和导出到关系数据库中。这意味着关系和非关系数据结构都可以一起生活在一个环境中。

PolyBase

PolyBase

SQL Server 2016 improves the 2014 implementation of PolyBase by now allowing you to directly query data in Hadoop or Azure blob storage, and have the computation performed on Hadoop for optimized performance. You can use transact SQL to communicate between the non-relational databases such as Hadoop or Azure blob storage and the relational SQL Server database.

SQL Server 2016现在允许您直接查询Hadoop或Azure blob存储中的数据，并在Hadoop上执行计算以优化性能，从而改进了PolyBase 2014的实现。您可以使用事务SQL在非关系数据库（例如Hadoop或Azure blob存储）与关系SQL Server数据库之间进行通信。

报告服务增强 (Reporting Services Enhancements)

There are 3 key things to mentions when it comes to the enhancements to SQL Server Reporting Services. 2016 has a better development environment with more data visualizations and new report content types. Mobile access to reports are now possible and a new development environment to support those mobile reports.

关于SQL Server Reporting Services的增强功能，有3个关键要提及的内容。 2016年拥有更好的开发环境，具有更多的数据可视化和新的报告内容类型。现在可以通过移动方式访问报告，并且可以使用新的开发环境来支持这些移动报告。

更深入的分析 (Deeper Analysis)

A vast array of improvements have been made to improve the analytics capabilities of SQL Server 2016.

为了改进SQL Server 2016的分析功能，已进行了大量改进。

R

[R

SQL Server 2016 no has R Services which allows statisticians, analysts and scientists to execute R code directly on their SQL Server database. The Enterprise Edition of R Services support high speed computations by using multi processors, multicores and multithreading.

SQL Server 2016没有R服务，该服务允许统计人员，分析师和科学家直接在其SQL Server数据库上执行R代码。 R Services企业版通过使用多处理器，多核和多线程支持高速计算。

Tabular improvements

表格改进
- Access data using DirectQuery mode
  
  使用DirectQuery模式访问数据
  
  More data sources are now supported for Direct Query. These are:
  SQL Server 2008 or later, Azure, Analytics Platoform System, Oracle 9i and higher and Teradata V2R6, V2
  现在，直接查询支持更多数据源。这些是：
  SQL Server 2008或更高版本，Azure，Analytics Platoform系统，Oracle 9i和更高版本以及Teradata V2R6，V2
- All data is no longer required when modeling with a DirectQuery source
  
  使用DirectQuery源建模时，不再需要所有数据
  
  You now have the option to choose to model with no data, a subset of data or all data as in previous version.
  现在，您可以选择像先前版本中那样不使用数据，数据子集或所有数据进行建模。
- Calculated Tables
  
  计算表
  
  Calculated tables can be built with DAX in models that are not using DirectQuery mode, which will allow you to combine columns from different tables, apply logic to filter the data or apply calculations.
  可以在不使用DirectQuery模式的模型中使用DAX来构建计算表，该模型将允许您组合来自不同表的列，应用逻辑来过滤数据或应用计算。
- Bi-directional cross-filtering
  
  双向交叉过滤
  
  You are now able to choose the filter direction of your table relationships. Previously filters could only be applied in one direction which required complex DAX queries in order to avoid duplicates. A relationship can now be filtered in two ways, either single like before or both directions with.
  
  现在，您可以选择表关系的过滤器方向。以前，过滤器只能在需要复杂DAX查询的一个方向上应用，以避免重复。现在可以以两种方式过滤关系，既可以像以前一样单向，也可以像双向一样。
- Enhancements to DAX
  
  DAX的增强
  
  The formula bar has been improved to facilitate the writing of DAX formulas. Many new DAX functions have been added and variables can now be used in DAX expressions.
  
  公式栏已得到改进，以方便DAX公式的编写。添加了许多新的DAX函数，并且现在可以在DAX表达式中使用变量。
  
  SQL Server Azure (SQL Server Azure)
  
  Initially Azure only offered a subset of the features which are available in a regular SQL Server database engine. Most of the limitations of the initial Azure SQL Database has now been overcome leaving SQL Database close to par with on-premises servers. Size limitations has been increased, additional security has been added and many database features included. All Azure SQL Databases come standard with high availability, and a minimum of 3 replicas exist which can be promoted to primary in the event of a hardware failure.
  
  最初，Azure仅提供了常规SQL Server数据库引擎中可用功能的子集。最初的Azure SQL数据库的大多数限制现在都已克服，使SQL数据库与本地服务器几乎保持一致。大小限制已增加，附加安全性已添加，并且包括许多数据库功能。所有Azure SQL数据库都是具有高可用性的标准配置，并且至少存在3个副本，如果硬件出现故障，这些副本可以提升为主要副本。
  
  SQL Database Security
  
  SQL数据库安全
  
  Azure security has been enhanced built to be trustworthy with advanced strategies and policies to ensure that only authorized users are able to access your data. The policies are regularly audited by accredited third-parties.
  
  通过高级策略和策略，Azure安全性得到了增强，使其可信任，以确保只有授权用户才能访问您的数据。这些政策会定期由获得认可的第三方进行审核。
  
  SQL Database Auditing
  
  SQL数据库审核
  
  Auditing is included in Azure and logs are written to Azure storage. The following events can be audited.
  
  审核包括在Azure中，并且日志被写入Azure存储。可以审核以下事件。
  - T-SQL whether plain or parametrized
    T-SQL，无论是普通的还是参数化的
  - Schema modifications
    模式修改
  - Data modifications
    资料修改
  - Stored procedures
    储存程序
  - Logins
    登录名
  - Transaction management
    交易管理
  Data Protection
  
  数据保护
  
  SQL Database has various features implemented to help protect your data, some I’ve already mentioned in the on-site SQL Server 2016 version. These include:
  
  SQL Database具有实现帮助保护数据的各种功能，我已经在现场SQL Server 2016版本中提到了其中的一些功能。这些包括：
  - Transparent Data Encryption
    
    透明数据加密
    
    TDE takes place on file level. The data in the tables are not encrypted. This prevents unauthorized use of database files and backups, but does not prevent users who have access to the data from viewing it.
    TDE在文件级别进行。表中的数据未加密。这样可以防止未经授权使用数据库文件和备份，但不能阻止有权访问数据的用户查看数据。
  - Cell Level Encryption
    
    信元级加密
    
    The purpose of CLE is to hide certain sensitive data such as social security numbers by encrypting a column of data.
    CLE的目的是通过加密一列数据来隐藏某些敏感数据，例如社会保险号。
  - Always Encrypted
    始终加密
  - Row level security
    行级安全
  - Dynamic Data Masking
    动态数据屏蔽
  Elastic databases
  
  弹性数据库
  
  The elastic database feature is a cost saving mechanism to prevent over provisioning of resources which is common if resources are estimated on a per database basis. Instead Azure allows you to group databases into an Elastic Database Pool and provision resources for the pool instead of for each database individually. When databases are in a pool they can automatically adjust to utilize the amount of eDTU (Database Transaction Units) as required. Sometimes some databases will use less and sometime some databases will use more. So any surplus can be used by other databases in the pool when they encounter a heavier load.
  
  弹性数据库功能是一种节省成本的机制，可以防止资源过度配置（如果按每个数据库估算资源，这种情况很常见）。相反，Azure允许您将数据库分组到一个弹性数据库池中，并为该池而不是为每个数据库分别配置资源。当数据库处于池中时，它们可以根据需要自动调整以利用eDTU（数据库事务单位）的数量。有时某些数据库使用较少，有时某些数据库使用较多。因此，当池中的其他数据库遇到较重的负载时，它们可以使用任何剩余资源。
  
  Azure数据仓库 (Azure Data Warehouse)
  
  Azure data warehouse is basically the APS (Analytics Platform System) in the cloud. The aim is to provide a massively parallel computing capabilities to everyone. The SQL Data Warehouse is targeted at databases which encompass at least a Terabyte of data. It includes quite a few optimizations to increase the performance of data warehouse queries, such as a distributed query optimizer and clustered column store indexes.
  
  Azure数据仓库基本上是云中的APS（分析平台系统）。目的是为每个人提供大规模的并行计算功能。 SQL数据仓库针对包含至少1 TB数据的数据库。它包括许多用于提高数据仓库查询性能的优化，例如分布式查询优化器和集群列存储索引。
  
  结语 (Wrapping it up)
  
  This was a quick summary of the new features available in SQL Server 2016 and Azure. Please check out the references below for more in-depth details of each topic.
  
  这是SQL Server 2016和Azure中可用的新功能的快速摘要。请查看下面的参考资料，以获取每个主题的更深入的详细信息。