Features Introduced in SQL Server 2014


With the impending release of SQL Server 2016, let’s not forget the great new features that were first released in SQL Server 2014.

1. Always On Enhancements

Microsoft enhanced Always On integration by expanding the number of secondary replicas from 4 to 8 maximum replicas. Readable secondary replicas is now available for read workloads, even when the primary replica is unavailable. Also, SQL Server 2014 provides the new Add Azure Replica Wizard that helps you create asynchronous secondary replicas using Azure.

2. In-Memory OLTP Engine

SQL Server 2014 enabled memory optimization of selected tables and stored procedures. The In-Memory OLTP engine uses a new optimistic concurrency control mechanism to eliminate locking delays.

3. Buffer Pool Extension

SQL Server 2014 provides a new Solid State Disk (SSD) integration capability that lets you use SSDs to expand the SQL Server 2014 Buffer Pool as non-volatile RAM (NvRAM), which allows you to use SSD drives to expand the buffer pool in systems that have maxed out memory. Buffer Pool Extensions can provide performance gains for read-heavy OLTP workloads.

4. Storage I/O control

The Resource Governor lets you limit the amount of CPU and memory that a given workload can consume, and this version extends the reach of the Resource Governor to manage storage I/O usage as well. You can use this feature to limit the physical I/O for user threads in a given resource pool.

5. Updateable Columnstore Indexes

 The SQL Server in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing. Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Microsoft sdays you can use the columnstore index to achieve up to 10 times query performance gains over traditional row-oriented storage, and up to 7 times data compression over the uncompressed data size.

6. SQL Server Data Tools for Business Intelligence

SQL Server Data Tools for Business Intelligence (SSDT BI), previously known as Business Intelligence Development Studio (BIDS), is used to create Analysis Services models, Reporting Services reports, and Integration Services packages.  The new SSDT-BI is used to create SQL Server Analysis Services (SSAS) models, SSRS reports and SSIS packages. It also supports SSAS and SSRS for SQL Server 2014 and earlier, but SSIS projects are limited to SQL Server 2014.

7. Power View for Multidimensional Models

Multidimensional models provide industry leading OLAP data modeling, storage, and analysis solutions. Multidimensional models in SQL Server 2014 support ad-hoc data analysis, exploration, and visualization by using Microsoft Power View. Power View is a thin web client that launches in the browser from a shared Report Data Source (.rsds) file in a SharePoint library. The Report Data Source acts as a bridge between the client and the back-end data source. The back-end data source can be a Power Pivot workbook in SharePoint, a Tabular model on an Analysis Services server running in Tabular mode, or a Multidimensional model on an Analysis Services server running in Multidimensional mode. Power View reports can then be saved to a SharePoint library or gallery and shared with other members in your organization.

With this release, multidimensional models support analysis and visualization by using Power View in SharePoint mode. No special configuration of your multidimensional models is necessary. There are however some differences in how multidimensional model objects are displayed in Power View compared to other client tools such as Microsoft Excel and Microsoft Performance Point. This release does not support analysis and visualization of multidimensional models by using Power View in Excel 2013.

8. Power BI for Office 365 Integration

Power BI for Office 365 is a cloud-based Business Intelligence (BI) solution that provides data navigation and visualization capabilities. Power BI for Office 365 includes Power Query (formerly code-named Data Explorer), Power Map (formerly code-named GeoFlow), Power Pivot, and Power View.

9. SQL Server Managed Backup to Windows Azure

This version of SQL Server allows native backup using Azure integration. Your on-premises SQL Server 2014 and the Azure Virtual Machine (VM) instances support backing up to Azure storage, and you can manage backups using the normal SQL Server Management Studio (SSMS) interface.

10. Backup Encryption

One addition to SQL Server 2014 is the feature to encrypt database backups for protection of at-rest data. SQL Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You must use a certificate or an asymmetric key to perform SQL Server 2014 backups using encryption.

You can get more information about SQL Server 2014 from Microsoft.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s