SQL 2014 Enhancements

2/11/2015
In SQL

The amount of data that we are required to query, transform and manipulate is exploding. What use to be considered gargantuan has become run-of-the-mill. This necessitates greater and greater performance and reliability out of our data tier. Three technologies available in SQL Server 2014 promise to give us that: columnstore indexes, in-memory OLTP tables and integration with Azure cloud based services.
Columnstore indexes are not completely new. They shipped with SQL Server 2012 but are now greatly enhanced in SQL Server 2014. Columnstore indexes were first introduced to support highly aggregated data warehouse queries. Based on xVelocity technologies, the indexes store data in a columnar format, while taking advantage of xVelocity’s memory management capabilities and advanced compression algorithms. However the columnstore index in SQL Server 2012 had to be non-clustered and it could not be updated, rendering the associated table read-only. SQL Server 2014 introduced a second type of columnstore index that is both clustered and updateable. It also features a more efficient algorithm that allows for greater data compression. This allows more data to fit into memory, helping to reduce expensive I/O operations.
In-memory online transaction processing has generated the most interest within the industry. In-memory online transaction processing (OLTP) requires no specialized hardware or software. It works with any traditional transactional load. Once the table is declared as memory optimized, a query can access the table as if it were a traditional table. Queries can span traditional and in-memory tables at the same time. Stored procedures that only reference memory optimized tables can be natively compiled into machine code for further performance enhancements. The resultant performance increase is variable but is expected to yield a 5 to 20 times improvement.

SQLServer2104OLTP

SQL Server 2014 has introduced the concept of Smart Backups. Smart Backups let SQL Server determine if full or differential backups should be performed and when they should be performed. Microsoft also provides the capability to backup on-premises databases to Azure storage.
SQL Server 2014 gives you the ability to use an Azure VM as an AlwaysOn Availabilty Group Replica. This allows you to setup your on-premise database(s) as the primary replica with the Azure replica hosting the secondary replicas. This provides for geographically dispersed disaster recovery.

AV71_SQL2014HC_PhilBlog_0413

These are just a few new features that make SQL Server 2014 so compelling. To learn about these and other features please plan on attending the MS20465: Designing Database Solutions for Microsoft SQL Server class with us.
For more information regarding LRS Education Services, including all of the services that we offer, please visit our website at: www.lrseducationservices.com.
Ron Smiley
MCT – 1195024