| title | What's New (Database Engine) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 06/22/2016 | ||
| ms.prod | sql-server-2014 | ||
| ms.reviewer | |||
| ms.technology | release-landing | ||
| ms.topic | conceptual | ||
| helpviewer_keywords |
|
||
| ms.assetid | 8f625d5a-763c-4440-97b8-4b823a6e2439 | ||
| author | CarlRabeler | ||
| ms.author | carlrab | ||
| manager | craigg |
This latest release of the [!INCLUDEssDEnoversion] introduces new features and enhancements that increase the power and productivity of architects, developers, and administrators who design, develop, and maintain data storage systems. These are the areas in which the [!INCLUDEssDE] has been enhanced.
In-Memory OLTP is a memory-optimized database engine integrated into the [!INCLUDEssNoVersion] engine. In-Memory OLTP is optimized for OLTP. For more information, see In-Memory OLTP (In-Memory Optimization).
SQL Server Data Files in Azure enables native support for [!INCLUDEssNoVersion] database files stored as Azure Blobs. This feature allows you to create a database in [!INCLUDEssNoVersion] running in on-premises or in a virtual machine in Azure with a dedicated storage location for your data in Azure Blob Storage.
Use the Deploy a SQL Server Database to an Azure Virtual Machine Wizard to host a database from an instance of [!INCLUDEssNoVersion] in an Azure Virtual Machine.
[!INCLUDEssSQL14] contains the following enhancements for [!INCLUDEssNoVersion] Backup and Restore:
-
SQL Server Backup to URL
[!INCLUDEssNoVersion] Backup to URL was introduced in [!INCLUDEssSQL11] SP1 CU2 supported only by [!INCLUDEtsql], PowerShell and SMO. In [!INCLUDEssSQL14] you can use [!INCLUDEssManStudioFull] to backup to or restore from Azure Blob storage service. The new option is available both for the Backup task, and maintenance plans. For more information, see Using Backup Task in SQL Server Management Studio, SQL Server Backup to URL Using Maintenance Plan Wizard, and Restoring from Azure storage Using SQL Server Management Studio.
-
SQL Server Managed Backup to Azure
Built on [!INCLUDEssNoVersion] Backup to URL, [!INCLUDEss_smartbackup] is a service that [!INCLUDEssNoVersion] provides to manage and schedule database and log backups. In this release only backup to Azure storage is supported. [!INCLUDEss_smartbackup] can be configured both at the database and at instance level allowing for both granular control at the database level and automating at the instance level. [!INCLUDEss_smartbackup] can be configured on [!INCLUDEssNoVersion] instances running on-premises and [!INCLUDEssNoVersion] instances running on Azure virtual machines. It is recommended for [!INCLUDEssNoVersion] instances running on Azure virtual machines. For more information, see SQL Server Managed Backup to Azure.
-
Encryption for Backups
You can now choose to encrypt the backup file during a backup operation. It supports several encryption algorithms including AES 128, AES 192, AES 256, and Triple DES. You must use either a certificate or an asymmetric key to perform encryption during backup. For more information, see Backup Encryption.
The cardinality estimation logic, called the cardinality estimator, is re-designed in [!INCLUDEssSQL14] to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. It is based on in-depth cardinality estimation research on modern workloads, and our learnings over the past 15 years of improving the SQL Server cardinality estimator. Feedback from customers shows that while most queries will benefit from the change or remain unchanged, a small number might show regressions compared to the previous cardinality estimator. For performance tuning and testing recommendations, see Cardinality Estimation (SQL Server).
[!INCLUDEssSQL14] introduces the ability to reduce latency by designating some or all transactions as delayed durable. A delayed durable transaction returns control to the client before the transaction log record is written to disk. Durability can be controlled at the database level, COMMIT level, or ATOMIC block level.
For more information see the topic Control Transaction Durability.
[!INCLUDEssSQL14] contains the following enhancements for AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups:
-
An Add Azure Replica Wizard simplifies creating hybrid solutions for AlwaysOn availability groups. For more information, see Use the Add Azure Replica Wizard (SQL Server).
-
The maximum number of secondary replicas is increased from 4 to 8.
-
When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads.
-
Failover cluster instances (FCIs) can now use Cluster Shared Volumes (CSVs) as cluster shared disks. For more information, see Always On Failover Cluster Instances.
-
A new system function, sys.fn_hadr_is_primary_replica, and a new DMV, sys.dm_io_cluster_valid_path_names, is available.
-
The following DMVs were enhanced and now return FCI information: sys.dm_hadr_cluster, sys.dm_hadr_cluster_members, and sys.dm_hadr_cluster_networks.
The individual partitions of partitioned tables can now be rebuilt. For more information, see ALTER INDEX (Transact-SQL).
The ONLINE = ON option now contains a WAIT_AT_LOW_PRIORITY option which permits you to specify how long the rebuild process should wait for the necessary locks. The WAIT_AT_LOW_PRIORITY option also allows you to configure the termination of blocking processes related to that rebuild statement. For more information, see ALTER TABLE (Transact-SQL) and ALTER INDEX (Transact-SQL). Troubleshooting information about new types of lock states is available in sys.dm_tran_locks (Transact-SQL) and sys.dm_os_wait_stats (Transact-SQL).
These new features are available for columnstore indexes:
-
Clustered columnstore indexes
Use a clustered columnstore index to improve data compression and query performance for data warehousing workloads that primarily perform bulk loads and read-only queries. Since the clustered columnstore index is updateable, the workload can perform many insert, update, and delete operations. For more information, see Columnstore Indexes Described and Using Clustered Columnstore Indexes.
-
SHOWPLAN
SHOWPLAN displays information about columnstore indexes. The EstimatedExecutionMode and ActualExecutionMode properties have two possible values: Batch or Row. The Storage property has two possible values: RowStore and ColumnStore.
-
Archival data compression
ALTER INDEX ... REBUILD has a new COLUMNSTORE_ARCHIVE data compression option that further compresses the specified partitions of a columnstore index. Use this for archival, or for other situations that require a smaller data storage size and can afford more time for storage and retrieval. For more information, see ALTER INDEX (Transact-SQL).
The Buffer Pool Extension provides the seamless integration of solid-state drives (SSD) as a nonvolatile random access memory (NvRAM) extension to the [!INCLUDEssDE] buffer pool to significantly improve I/O throughput.
CREATE STATISTICS and related statistic statements now permits per partition statistics to be created by using the INCREMENTAL option. Related statements allow or report incremental statistics. Affected syntax includes UPDATE STATISTICS, sp_createstats, CREATE INDEX, ALTER INDEX, ALTER DATABASE SET options, DATABASEPROPERTYEX, sys.databases, and sys.stats. For more information, see CREATE STATISTICS (Transact-SQL).
The Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within a resource pool. In [!INCLUDEssSQL14], you can use the new MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings to control the physical IOs issued for user threads for a given resource pool. For more information, see Resource Governor Resource Pool and CREATE RESOURCE POOL (Transact-SQL).
The MAX_OUTSTANDING_IO_PER_VOLUME setting of the ALTER RESOURCE GOVENOR sets the maximum outstanding I/O operations per disk volume. You can use this setting to tune IO resource governance to the IO characteristics of a disk volume and can be used to limit the number of IOs issued at the SQL Server instance boundary. For more information, see ALTER RESOURCE GOVERNOR (Transact-SQL).
The progress report for the online index operation event class now has two new data columns: PartitionId and PartitionNumber. For more information, see Progress Report: Online Index Operation Event Class.
The 90 compatibility level is not valid in [!INCLUDEssSQL14]. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL)
Inline specification of CLUSTERED and NONCLUSTERED indexes is now allowed for disk-based tables. Creating a table with inline indexes is equivalent to issuing a create table followed by corresponding CREATE INDEX statements. Included columns and filter conditions are not supported with inline indexes.
The SELECT ... INTO statement is improved and can now operate in parallel. The database compatibility level must be at least 110.
[!INCLUDEtsql] Enhancements for In-Memory OLTP
For information about the [!INCLUDEtsql] changes to support In-Memory OLTP, see Transact-SQL Support for In-Memory OLTP.
sys.xml_indexes (Transact-SQL) has 3 new columns: xml_index_type, xml_index_type_description, and path_id.
sys.dm_exec_query_profiles (Transact-SQL) monitors real time query progress while a query is in execution.
sys.column_store_row_groups (Transact-SQL) provides clustered columnstore index information on a per-segment basis to help the administrator make system management decisions.
sys.databases (Transact-SQL) has 3 new columns: is_auto_create_stats_incremental_on, is_query_store_on, and resource_pool_id.
For information about system view enhancements to support In-Memory OLTP, see System Views, Stored Procedures, DMVs and Wait Types for In-Memory OLTP.
A new server level permission. Grant CONNECT ANY DATABASE to a login that must connect to all databases that currently exist and to any new databases that might be created in future. Does not grant any permission in any database beyond connect. Combine with SELECT ALL USER SECURABLES or VIEW SERVER STATE to allow an auditing process to view all data or all database states on the instance of [!INCLUDEssNoVersion].
A new server level permission. When granted, allows a middle-tier process to impersonate the account of clients connecting to it, as it connects to databases. When denied, a high privileged login can be blocked from impersonating other logins. For example, a login with CONTROL SERVER permission can be blocked from impersonating other logins.
A new server level permission. When granted, a login such as an auditor can view data in all databases that the user can connect to.
Deploy a SQL Server Database to a Microsoft Azure Virtual Machine enables deployment of a [!INCLUDEssNoVersion] database to an Azure VM.
Deployment of databases on ReFS is now supported.