MS SQL 2014 News
Here's a small summary of what's new in SQL 2014, which will be officially released on 1 April 2014. It feels like Microsoft in this release has focused on quite a lot on performance, in 2012 there was a big focus on high availability (AlwaysOn).
The biggest news is probably "Memory Optimized Tables", which in short means that you can have entire tables stored in memory, to optimize performance. However, there are some "ifs and buts" with this and we will certainly come back to these in a future post.
There are other news items that are equally interesting, such as.
* The Cardinality Estimator, used to create query plans, has been rewritten to improve performance (in most cases), including a redesign to better handle today's data sizes, both for OLTP and OLAP, note that this is only active on databases with compatibilitylevel 120. (see below). However, this can be disabled with Traceflag 2312 if you don't want to use it. (and then use version 70, i.e. the same as sql 2012)
* Encrypted backups are another novelty, for those who use SQL's own backup, these can be encrypted with various algorithms, such as AES256 and Triple DES. This functionality has been available for longer in third-party backup tools.
* Delayed transaction durability, as opposed to full transaction durability (which is and has been the default since inception), waits to write to the log file, and returns ok to the client before anything is stored on disk, the transaction is instead stored in a memory buffer, and is written down in larger blocks to increase performance, however one should keep in mind that one may lose data if the server goes down before the write has occurred. Writing to disk happens partly if a full durability transaction occurs, sp_flush_log is executed, or the memory buffer is filled.
A fun detail is that as of SQL 2014, you can allocate 128Gb of memory in SQL Server Standard, as opposed to 64 as it was in 2012 and earlier.
Keep in mind that DATABASE COMPATIBILITYLEVEL for SQL 2014 is at least 100, i.e. SQL 2008, if you have databases from SQL 2005 or older (god forbid) that need to be converted, you have to do it in two steps, first to e.g. SQL 2008 and then to SQL 2014.
How can you find out the current level of compatibility of your databases?
Simple;
SELECT name, compatibility_level FROM sys.databases
It is easy to change the compatibility level of a database, but you should contact your software vendor before doing so, as it may cause the application using SQL to stop working, or to work differently.
It is not unlikely that we will go into more detail about some of the news in a future post.
//Pontus
