What's new in SQLSERVER2014

What's new in SQLSERVER2014

Reprinted from: http://blog.csdn.net/maco_wang/article/details/22701087

Blog people: Maco_wang SQL Server Direction MVP, MSDN forum Moderator

Red marked for I think the key function

1. Memory-Optimized Tables
In-memory OLTP is a memory-optimized database engine that is integrated into the SQL Server engine.
In-memory OLTP has been optimized for OLTP. There are a number of limitations for Transact-SQL perimeter applications that are supported in-memory OLTP.

2.Windows SQL Server data files in Azure
The SQL Server data file in Windows Azure provides native support for SQL Server database files that are stored as Windows Azure Blob storage.
This feature enables you to create databases in a SQL Server that is running on-premises or on virtual machines in Windows Azure, and to store data in a dedicated storage location in Windows Azure Blob storage.

3. Hosting a SQL Server database in a Windows Azure virtual machine
You can host a database from an instance of SQL Server to a Windows Azure virtual machine by using the Deploy SQL Server database to the Windows Azure Virtual Machine Wizard.

4. Backup and Restore Enhancements
SQL Server 2014 includes the following enhancements for SQL Server backup and restore:
1) SQL Server Backup to URL
The SQL Server backup-to-URL feature was introduced in SQL Server SP1 CU2, and only Transact-SQL, PowerShell, and SMO support this feature.
In SQL Server 2014, you can use SQL Server Management Studio to back up or restore from the Windows Azure Blob storage service. This new option is available for both the backup task and the maintenance plan.
2) SQL Server hosted Backup to Windows Azure
SQL Server hosted Backup to Windows Azure is a service built on SQL Server Backup-to-URL functionality that SQL Server provides to manage and schedule backups of databases and logs. In SQL SERVER 2014, only backup to Windows Azure storage is supported. SQL Server hosted backups to Windows Azure can be configured at the same time at the database and instance level, enabling granular control at the database level and automation at the instance level. SQL Server hosted backups to Windows Azure can be configured on a SQL Server instance that is running locally, or on an instance of SQL Server running on a Windows Azure virtual machine. It is recommended that you use this service for instances of SQL Server running on Windows Azure virtual machines.
3) Backup encryption
You can choose to encrypt the backup file during the backup process. The currently supported cryptographic algorithms includeAES 128, AES 192, AES 256, and Triple DES。 To perform encryption during the backup process, you must use a certificate or asymmetric key.

5. New design for cardinality estimation
Cardinality estimation logic, called the Cardinality estimator, has been redesigned in SQL Server 2014 to improve the quality of query plans and therefore improve query performance. The new cardinality estimator incorporates the assumptions and algorithms that perform well in the new OLTP and Data Warehouse workloads.

6. Delayed Durability
SQL Server 2014 can shorten the delay by specifying some or all of the transactions as deferred durable transactions. Deferred durable transactions return control to the client before the transaction log records are written to disk. Persistence can be controlled at the database level, at the commit level, or at the atomic block level.

7.AlwaysOn Enhanced Features
SQL Server 2014 contains the following enhancements for AlwaysOn failover cluster instances and AlwaysOn availability groups:
1) The Add Azure Replica Wizard simplifies the creation of hybrid solutions for AlwaysOn availability groups.
2) The maximum number of secondary replicas increased from 4 to 8.
3) A readable secondary replica remains available for reading workloads when it disconnects from the primary replica or during a lack of cluster quorum.
4) A Failover cluster instance (FCI) can now use cluster Shared volumes (CSV) as a cluster shared disk.
5) provides a new system function Sys.fn_hadr_is_primary_replica and a new DMV sys.dm_io_cluster_valid_path_names.
6) The following DMV has been enhanced and now returns FCI information: Sys.dm_hadr_cluster, Sys.dm_hadr_cluster_members, and Sys.dm_hadr_cluster_networks.

8. Partition switching and index generation
SQL SERVER 2014 canRegenerateof a partitioned tableSeparate Partitions。

9. Managing lock priority for online operations
The ONLINE = On option now contains the wait_at_low_priority option, which allows you to specify how long the rebuild process should wait for the required locks.

The Wait_at_low_priority option also allows you to configure the termination of the blocking process associated with the Rebuild statement. Troubleshooting information about the new lock state type is provided in sys.dm_tran_locks (Transact-SQL) and sys.dm_os_wait_stats (Transact-SQL).

10. Columnstore Index
These new features are available for use with Columnstore indexes:
1) Clustered Columnstore index
Using clustered Columnstore indexes improves data compression and query performance for data warehouse workloads that primarily perform bulk load and read-only queries. Because the clustered Columnstore index is updatable, the workload can perform many insert, update, and delete operations.
2) SHOWPLAN
SHOWPLAN displays information about the Columnstore index. The Estimatedexecutionmode and Actualexecutionmode properties have two possible values: Batch or Row. The Storage property has two possible values: Rowstore and ColumnStore.
3) archived data compression
ALTER INDEX ... REBUILD provides a new columnstore_archive data compression option to further compress the specified partition of the Columnstore index. This can be used for archiving, or for other scenarios that require smaller data storage sizes and can devote more time to storage and retrieval.


11. Buffer Pool Expansion
Buffer pool expansion provides a seamless integration of solid state drives (SSDs) as a non-volatile random access memory (NvRAM) extension of the database engine buffer pool, which significantly improves I/O throughput. The price of solid-state drives is really a bit expensive.

12. Incremental statistical information
The Create STATISTICS and related statistics statements now allow statistics by partition to be created by using the INCREMENTAL option.

13. Resource Governor enhancements for physical IO control
Resource Governor allows you to specify limits on the amount of CPU, physical IO, and memory used by incoming application requests that can be used within a resource pool. In SQL Server 2014, you can use the new Min_iops_per_volume and Max_iops_per_volume settings to control the number of physical IO that a given resource pool emits to a user thread. For more information, see Resource Governor resource pool and create RESOURCE pool (Transact-SQL).
ALTER RESOURCE Govenor's max_outstanding_io_per_volume setting sets the maximum number of pending I/O operations (IOPS) per disk volume. You can use this setting to adjust IO resource control based on the IO characteristics of a disk volume, and can be used to limit the number of IO emitted at the SQL Server instance boundary.

14.Online Index Operation event class
Progress reports for the online index operations event class now have two new data columns: PartitionID and PartitionNumber.

15. Database compatibility level
90 The compatibility level is not valid in SQL Server 2014.

Reprinted from: http://blog.csdn.net/maco_wang/article/details/22699795

Microsoft SQL Server 2014 has the latest security, feature updates built in. Microsoft will open the SQL Server 2014 resource Download service during April Fool's Day, the Build2014 developer conference.
Microsoft SQL Server 2014 provides the market with new memory features deployed to the core database, including memory OLTP, which complements the existing in-memory data warehousing and BI capabilities of most integrated memory database solutions in the market.
SQL Server 2014 also provides new cloud capabilities to simplify the adoption of cloud technologies for SQL databases and to help you create new hybrid scenarios.

Main functions:
1. Memory OLTP:

Provides memory OLTP capabilities deployed to the core SQL Server database to significantly improve database application performance.
Memory OLTP is installed with SQL Server Engine without any additional action, and you do not have to rewrite the database application or update the hardware to improve memory performance. SQL Server CTP2 enhancements include AlwaysOn support, increased TSQL perimeter applications, and the ability to migrate existing objects into memory OLTP.

2. Memory-Updatable ColumnStore:

Provides higher compression rates, richer query support, and updates for existing ColumnStore Data Warehouse workloads, providing you with even faster load speeds, query performance, concurrency, and even lower unit TB prices.

3. Extend the memory to the SSD:

Increase memory processing power and reduce disk IO by seamlessly and transparently integrating SSD as a database buffer pool extension into SQL Server

4. Enhanced High Availability

1) New AlwaysOn feature: The availability group now supports up to 8 secondary replicas , which can be read at any time, even if a network failure occurs. Failover cluster instances now support Windows cluster shared volumes, which improves shared storage utilization and failover resiliency.

2) Improved online database operations: includes a single partition for online index reconstruction and management of the lock priority of table partition switching, thus reducing the maintenance downtime impact .

5. Encrypted backup : Provides backup encryption support in on-premises and in Windows Azure.

6.IO Resource governance: resource pools now support the configuration of minimum and maximum IOPS per volume for more comprehensive resource isolation control.

7. Hybrid Solution:

1) Smart Backup: Manage and automate the backup of SQL Server to Windows Azure Storage (from on-premises and in Windows Azure).

2) Add Azure Replica Wizard: Easily add replicas from Windows Azure to your on-premises availability group.

3) SQL XI (Xstore integration): Supports SQL Server database files on Windows Azure storage blobs (from on-premises and in Windows Azure)

4) Deployment Wizard: Easily deploy an on-premises SQL Server database to Windows Azure.

Note: Microsoft March 26, 2014 officially announced that the cloud operating system Windows Azure renamed Microsoft Azure, the new brand since April 3 enabled.

What's new in SQLSERVER2014