Microsoft SQL Server training: What you need to know
A comprehensive collection of articles, videos and more, hand-picked by our editors
This is the first of two parts taking an overview of Microsoft SQL Server 2014, due out in late 2013 or early 2014. This part focuses on in-memory and cloud. The second part focuses on SQL Server 2014 availability and scalability.
Microsoft released the first public Community Technology Preview (CTP) of SQL Server 2014 in June. The CTP let developers, administrators and other database types poke and prod many of the product's new and enhanced features. The final version will include the full SQL Server suite of database and business intelligence tools. Like any new release, SQL Server 2014 offers a number of improvements, including expanded in-memory capabilities, streamlined cloud integration, and enhanced availability and scalability.
No feature in SQL Server 2014 generated as much interest as the in-memory online transaction processing (OLTP) engine, previously code named "Hekaton." Integrated into SQL Server's core database management components, in-memory OLTP requires no specialized hardware or software and works seamlessly with existing transactional processes. Once you declare a table as memory-optimized, the in-memory OLTP engine manages the table and maintains the data in memory. Queries can access the data as they would any other table's data. In fact, a single query can reference both memory-optimized and regular tables.
SQL Server 2014 will support the memory-optimized tables in other ways, as well. For example, SQL Server will include a diagnostic tool to help you determine which databases and tables are best suited to run in memory. In addition, stored procedures that reference only Hekaton tables can be natively compiled; that is, the stored procedures can be automatically converted to native code that's executed by the CPU without the need for further compilation or interpretation, as is required for a regular stored procedure. Typically, native code runs much faster and requires less memory than a language such as SQL.
More on SQL Server 2014
What feature of SQL Server 2014 are you most interested in?
Yep, Denny Cherry predicted SQL Server 2014
Another way in which SQL Server 2014 enhances memory-related capabilities is by letting you extend the SQL Server in-memory buffer pool to a solid-state drive (SSD) or to an SSD array. Extending the buffer pool provides for much faster paging, but with minimal risk to the data because only clean pages are stored on the SSD. This can be especially beneficial to OLTP operations that support heavy read loads.
In SQL Server 2014, the columnstore indexing functionality has also been updated. Columnstore indexes were first introduced in SQL Server 2012 to support highly aggregated data warehouse queries. Based on xVelocity storage 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. SQL Server 2014 introduces a second type of columnstore index that is both clustered and updateable. It is also more efficient at compressing data, which allows additional data to fit into memory, helping to reduce expensive I/O operations.
Microsoft has been touting SQL Server 2014 as a hybrid cloud platform, the implication being that SQL Server databases can now be more easily integrated with Windows Azure. For example, starting with SQL Server 2012 Cumulative Update 2, you've been able to back up your databases to the Windows Azure Binary Large Object Storage Service. However, SQL Server 2014 has introduced the concept of Smart Backups, in which SQL Server automatically determines whether full or differential backups should be performed and when they should be performed. SQL Server 2014 also lets you store the data and log files associated with an on-premises database in Azure's storage. In addition, SQL Server Management Studio provides a deployment wizard that lets you easily migrate your existing on-premises databases to an Azure virtual machine (VM).
SQL Server 2014 has also added the ability to use an Azure VM as an AlwaysOn availability group replica. Introduced in SQL Server 2012, the Availability Groups feature provides failover services that can support highly available databases. The structure consists of a primary replica and one to four secondary replicas (up to eight in SQL Server 2014). The primary replica can host one or more databases; the secondary replicas contain copies of those databases. The Windows Azure Infrastructure Services now supports Availability Groups in Azure VMs when SQL Server is running. That means you can set up the VM as a secondary replica in order to support automatic failover.
About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at http://www.rhsheldon.com.