A week late and more than a few dollars short, this blog post is focusing on a few of the features announced for SQL Sever 2014. I’ve watched the Keynote and a few of the videos from the sessions at TechEd North America 2013, and read the whitepapers and datasheets. Information is still limited though, like which versions the features will be added to, so it’s certainly not a deep dive into the features. Nor is it an exhaustive list of the features; moreover, it’s a look at those ones that immediately piqued my interest in the Mission Critical Performance category of improvements.
(Update: I’ve written about SQL Server CTP1 here)
SQL Server 2014 was announced during the keynote at TechEd in New Orleans. Makes me wish I went all the more now. The features in this release are categorized into 3 areas:
- Mission Critical Performance
- Platform for Hybrid Cloud
- Faster Insights from Any Data
Sysprep at the cluster levelA little bit about sysprepping SQL: SQL Server Sysprep allows you to prepare a stand-alone instance of SQL Server on a computer and to complete the configuration at a later time.SQL Server Sysprep currently supports SQL Server Database Engine, SQL Server Reporting Services, SQL Server Analysis Services, SQL Server Integration Services, as well as shared features. You can now configure Sysprep for SQL Server failover cluster installations. Not much more to say here other than good stuff!
Columnstore indexes were a big feature of SQL Server 2012, limited by the fact that they were static, meaning that you had to disable or drop and then enable/rebuild once updated. With SQL Server 2014, Columnstore Indexes are now updateable: Prior to SQL Server 2014, if you ever wanted to update a table without dropping/disabling the Columnstore index, the fastest process was to leverage partitions by switching in tables that used the same columnstore index design.
Compression for Columnstore indexes as also been improved: we can apply a new compression called COLUMNSTORE_ARCHIVE for greater compression and storage space savings of (reportedly) as much as 90 percent.
HekatonHekaton is one of the features I was interested in hearing more about, having followed it from last years announcements. They’ve not mentioned which version of SQL Server it will be in (e.g. Standard or Enterprise) and what with the 64GB limit on standard, maybe they won’t add it as a feature to standard. Conjectures aside, It seems that though it is still built into SQL Server, there are a few caveats. Tables can be declared as ‘memory optimized’ to take advantage of In-Memory OLTP’s capabilities. There are some limitations on these tables, which is to be expected:
- None of the LOB data types are allowed; there can be no columns of type XML, CLR or the max data types, and all rows will be limited to 8060 bytes with no off-row data.
- No DML triggers
- No FOREIGN KEY or CHECK constraints
- No IDENTITY columns
- No UNIQUE indexes other than for the PRIMARY KEY
- A maximum of 8 indexes, including the index supporting the PRIMARY KEY
- Database mirroring and replication are not supported
- One of the reports generated will contain recommendations on which tables might provide the biggest performance gain when converted to memory-optimized tables.The report will also describe how much effort would be required to carry out the conversion based on how many unsupported features the table concurrently uses.
- Another report will contain recommendations on which procedures might benefit from being converted to natively compiled procedures for use with memory-optimized tables.
IO Control via Resource GovernorDatabase Administrators can now define the workloads which can take a percentage of performance on any given CPU, memory, and now also I/O. Through Resource Governor, administrators can control I/O resources by adding a setting for maximum and minimum IOPS per volume to Resource Governor resource pools.
So other than the exciting new features above, there was also information around some of the features that were introduced SQL Server 2012: