SQL Server 2014

SQL Server 2014

SQL Server 2014 is comprehensive in-memory technologies for OLTP (code-named “Hekaton”), data warehousing, and analytics built directly into SQL Server.  It also provides new hybrid solutions for cloud backup and disaster recovery as well as takes advantage of new capabilities in Windows Server 2012 R2 to provide enterprise-class availability and scalability with predictable performance and reduced infrastructure costs.

Benefits

• In-Memory OLTP: Provides in-memory OLTP capabilities built into core SQL Server database to significantly improve the transactional speed and throughput of your database application. Microsoft claims SQL Server 2014 can make database operations as much as 30 times faster. To use it, a database must have certain tables (actually, the file groups used to store tables) declared as memory-optimized. The resulting table can be used as a conventional database table or as a substitute for a temporary table.
• In-Memory Updateable ColumnStore: 
  Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size. It provides faster load speed, query performance, concurrency, and even lower price per terabyte. Since the clustered columnstore index is updateable, your workload can perform a large number of insert, update, and delete operations. Columnstore indexes work well for mostly read-only queries that perform analysis on large data sets. 

 

Columnstore Index benefits:

• Columns often have similar data, which results in high compression rates.
• High compression rates improve query performance by using a smaller in-memory footprint. In turn, query performance can improve because SQL Server can perform more query and data operations in-memory.
• A new query execution mechanism called batch-mode execution has been added to SQL Server that reduces CPU usage by a large amount. Batch-mode execution is closely integrated with, and optimized around, the columnstore storage format. Batch-mode execution is sometimes known as vector-based or vectorized execution.
• Queries often select only a few columns from a table, which reduces total I/O from the physical media.
• Extending Memory to SSDs: Seamlessly and transparently integrates solid-state storage into SQL Server by using SSDs as an extension to the database buffer pool, allowing more in-memory processing and reducing disk IO.

The Buffer Pool allows you to extend your available memory by providing a hybrid buffer pool to help reduce I/O bottlenecks as long as you have access to an SSD.

The hybrid buffer pool now consists of the existing buffer pool plus an extension that resides on nonvolatile storage or an SSD. The buffer manager still deals with identifying those pages in the buffer pool that can be expunged, when required pages for a request are not residing in memory but on disk. This practice is still handled and undertaken by the buffer manager. In addition to the buffer manager deciding on which pages should be in the buffer pool or not, it is now identifying those pages which are considered as “clean pages” and migrating those pages out of the buffer pool onto the BPE to allow for even more data to reside in the buffer pool. This practice is providing the following benefits to your SQL Server environment 

o    Increased random I/O throughput

o    Reduced I/O latency

o    Increased transactional throughput

o    Improved read performance with a larger hybrid buffer pool

o    A caching architecture that can take advantage of present and future low-cost memory drives

• Enhanced High Availability

                i) New AlwaysOn features: Availability Groups now support up to 8 secondary replicas that remain available for reads at all times, even in the presence of network failures. Failover Cluster Instances now support Windows Cluster Shared Volumes, improving the utilization of shared storage and increasing failover resiliency.

ii) Improved Online Database Operations: Includes single partition online index rebuild and managing lock priority for table partition switch, reducing maintenance downtime impact.

• Encrypted Backup: Provides encryption support for backups, on-premise and on Microsoft Azure.
• IO Resource Governance: Resource pools now support configuration of minimum and maximum IOPS per volume, enabling more comprehensive resource isolation controls.
• Hybrid Scenarios:

o    SQL Server Backup to Azure: Provides management and automation of SQL Server backups (from on-premise and Microsoft Azure) to Microsoft Azure storage.

o    AlwaysOn with Azure Secondaries: Easily add replicas in Microsoft Azure to on-premise Availability Groups.

o    SQL XI (XStore Integration): Supports SQL Server Database files (from on-premise and Microsoft Azure) on Microsoft Azure Storage blobs.

Deployment Wizard: Easily deploy on-premise SQL Server databases to Microsoft Azure.