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.

10 Great Features in SQL Server 2014

The latest version of SQL Server is targeted at becoming the leading database solution from Microsoft. Microsoft has focused on many of the OLTP performance problems from many angles: slow disk performance issues with in-memory tables, slow log performance issues with delayed durability, and maintenance concurrency issues with lock priority. This new SQL Server release stops excessive I/O with new Resource Governor I/O controls, addresses some memory issues with an SSD buffer pool extension, and availability issues with the Azure cloud integration.

1. In-Memory OLTP

For OLTP, the most exciting new feature in SQL Server 2014 is In-Memory OLTP, which allows you to move individual tables to special in-memory structures. The performance boost is reported as big as 30x. There are a number of limitations and special requirements for these tables, so they won’t work under every circumstance. This is better than other in-memory solutions that require the entire database to be placed in memory. You can get more performance by converting existing stored procedures into in-memory procedures, too. You can read a whitepaper from Microsoft Research here.

2. Backup to Azure

Quite often small shops without a full-time Database Administrator don’t discover that their backups aren’t being handled properly until it’s too late. The Managed Backup feature automatically backs up your database (or your instance) based on your defined recovery interval and workload patterns. When the system determines the data has changed significantly enough, it takes a backup to Azure. This feature only works with Azure blob storage. But since your backups are already offsite, there’s no need to worry about tapes. This may not solve all your incident recovery needs or be 100% effective, but for those small shops it is a better solution than doing nothing.

3. SQL Server Data Files in Azure

Data Files in Azure is just what it sounds like: Your database runs locally in your data center, while the database files themselves live in an Azure blob container. This can offer advantages in DR and migration. But depending on the size of the database and its workload, the potential performance cost of pushing the data for every transaction across the Internet could be prohibitive. A better use of this feature may be to store the data files in an Azure VM in the same data center. This can also get you around the current limitation of having only 16 mounted disks in an Azure VM.

4. Updateable columnstore indexes

Updatable columnstore indexes in SQL Server 2014 bring a dramatic boost to data warehouse performance.  The caveat is that you must have a clustered columnstore index on the table. Non-clustered columnstores aren’t supported.

5. Resource Governor for I/O

Disk I/O is typically the most constrained resource of a database system, and often a large or rogue query will take up more precious I/O resources than you can afford. Microsoft has finally given us some control over runaway I/O. With Resource Governor for I/O, you can now put queries into their own resource pool and limit the amount of I/O per volume they’re allowed.

MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME set the minimum and maximum reads or writes per second allowed by a process in a disk volume.

• MIN_IOPS_PER_VOLUME reserves a minimum number of I/O transactions per second
• MAX_IOPS_PER_VOLUME provides a maximum number of I/O transactions per second.

This maximum doesn’t limit the number of I/O operations a query can perform, but merely keeps it from monopolizing a disk. This way your large queries can still run, but other things will run as well.

A good use of I/O control is to reserve some IOPS for administrators to be able to investigate issues when the disks are overloaded.

6. Delayed durability

In SQL Server, changes to data are written to the log first. This is called write ahead logging (WAL). Control isn’t returned to the application until the log record has been written to disk (a process referred to as “hardening”). Delayed durability allows you to return control back to the application before the log is hardened. This can speed up transactions if you have issues with log performance. Nothing is free, though, and here you sacrifice recoverability. Should the database go down before the log is committed to disk, then you lose those transactions forever. It may be worth the risk if your log performance is severely degrading application response times.

7. SSD buffer pool extension

Creating a buffer pool extension for SQL Server 2014 is like being able to define a different page file in Windows. As data pages move into memory, they begin to fill up the buffer pool. If the buffer pool fills up, the less frequently used pages will be paged to disk. Then when they’re needed again, they’ll be swapped with something else in the buffer pool and moved back into memory. The buffer pool extension option allows you to define an SSD as a buffer file location. Because SSD is so much faster than spinning disk, the paging is considerably quicker, which increases performance dramatically in some cases. You can define a buffer pool extension file up to 32 times the size of your memory.

8. Backup Encryption

One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. 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 encryption for SQL Server 2014 backups.

9. Lock priority of online operations

You can now specify a lock priority for online re-indexing. In previous versions of SQL Server, long-running queries could block re-indexing operations, chewing up your maintenance window while your re-index sits waiting. In SQL Server 2014, you can specify how your re-index operation will handle being blocked. You specify how long it will wait and what to do when the wait is over.

10. AlwaysOn Enhancements

Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now also available for read workloads, even when the primary replica is unavailable. In addition, SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you create asynchronous secondary replicas in Windows Azure.