SQL 2014: Investigating Microsoft's latest database release
A comprehensive collection of articles, videos and more, hand-picked by our editors
SQL Server 2014, due out late this year, has an in-memory capability for online transaction processing (OLTP) with...
table-level granularity and doesn't require expensive hardware like other in-memory databases, according to Microsoft.
Microsoft announced SQL Server 2014 at its TechEd conference in New Orleans this week. A technology preview download will be available this month, and the database is expected to be generally available in late 2013. Redmond had already said last fall that in-memory would be a feature of the next version and previewed it during the Professional Association for SQL Server (PASS) Summit in Seattle under the codename "Hekaton." What's new this week is Microsoft's announcement of the release date.
"For those companies who have applications that are having specific locking and blocking issues on the super high I/O systems, the in-memory OLTP features that that we know as Hekaton are going to be an amazing feature to look at," said Denny Cherry, a senior database administrator and independent SQL Server consultant.
Cherry did say that some code changes will be necessary to get the most out of the in-memory feature of SQL Server 2014, but the greatest benefit is that it comes as part of the normal SQL Server database engine.
"Because we already know how to manage and program for SQL Server, we can handle working with Hekaton automatically," he said.
Cherry added that in the past Microsoft has announced database features years before users could actually get their hands on them. That isn't the case with SQL Server 2014, with a preview due out this month. He said that companies can immediately start targeting their development efforts "to where they need them the most."
Eron Kelly, Microsoft SQL Server general manager, said that tests in labs have shown a 50x improvement in some cases when moving transaction processing into memory. Kelly said the feature will be an optional setting for database administrators, who can choose which database instances or tables to put into memory. He added that there will be a diagnostic tool that will suggest which databases and tables are the best candidates to run in-memory. That granularity is something that Kelly said differentiates it from other in-memory databases.
"You can move those five tables in memory," he said. "Now they go in memory and others can run on disk. You don't have to buy high-end boxes. That's the biggest difference between this and something like SAP HANA. You can choose which tables go in-memory and run it all on the hardware you have today."
Kelly added that the "Hekaton" codename will fade, and that it will be referred to as the SQL Server In-Memory OLTP Engine.
Other features of SQL Server 2014
While the in-memory capability is the biggest feature of SQL Server 2014, a couple of others are worth noting.
First is that the column-store indexes feature of SQL Server, called xVelocity ColumnStore, will allow continuous loading of data. Column-store indexes can speed up query times by storing columns in a different way than traditional indexes, which basically allows SQL Server to compress the data in the index. But in SQL Server 2012, once you convert tables to column-based, the data in the indexes is static. With SQL Server 2014, DBAs will be able to load and delete data in existing column-store indexes.
Kelly cited another feature in SQL Server 2014 that will allow quick database backup to SQL Azure, which is now called Windows Azure SQL Database. He said that with SQL Server 2014, DBAs will be able to right-click in SQL Server Management Studio and create secondary and tertiary database backups in Azure for disaster recovery.
Finally, Kelly said that SQL Server 2014 will have a storage management feature that will allow DBAs to put some databases on solid state disks (SSDs) for active data, and then put others on traditional disks if they contain "cold" data.
OLTP limitations addressed in SQL Server 2016