In a few days SQL Server 2014 will be made available, we’re not all lucky enough to be running Enterprise Edition even the largest of corporations still have a large number of Standard licences about their estate. The big question is; What can SQL Server 2014 do for you and your small – mid sized business?
In this post I’m not going to be looking at the Enterprise Edition because it’s not something the whole SQL Server community can benefit from. Instead I’m going to talk to you about a few features that I think are going to make a huge difference to your business and indirectly your own self happiness – who doesn’t like playing with new cool stuff!
So without further ado and in no particular order:
2 – Enhanced Memory Support
Microsoft wrote the following in Changes to CPU and memory limits for SQL Server Standard Edition
Beginning with SQL Server 2008 R2, SQL Server Standard edition supports a maximum of 64 gigabytes of system memory. In SQL Server versions earlier than SQL Server 2008 R2, SQL Server Standard supported the operating system maximum memory.
Whilst we’re not back to OS maximum with SQL Server 2014, standard edition does double the amount of memory previously allowed to 128GB. With the price of RAM a lot lower than it has been this can really help paper the cracks over some performance issues you might be having.Let’s not forget this is something your Finance Director can do to improve the performance of SQL Server. That’s why it goes right in at number 2.
3 – Encrypted Backup
After introducing native compression as standard in 2008 R2 it seems a little strange that it has taken so long to incorporate native encryption into the product. What is a relief to may is the fact that v1 of this feature is not Enterprise only and will appear in the BI edition too. This feature will make a huge addition to your security arsenal and help protect your data from prying eyes.
4 – Delayed Durability
This is subject is going to explode in the blogosphere. Bold claims I know, but let’s just think about those two words “Delayed Durability” now think about the term “ACID”. There are going to be lots of opinions flying about on the adoption of this as well as some very bad advice.
For now, just read through this article and think very carefully if this is something that you could and should use in your environment – Control Transaction Durability
To me, this line sums it up perfectly: “If you can tolerate some data loss, for example, where individual records are not critical as long as you have most of the data, then delayed durability may be worth considering. If you cannot tolerate any data loss, do not use delayed transaction durability.”
5 – Managed Backups
In a bizarre twist you would have already read number four which was also about backups. I guess I could have put them all together however that would have meant “4 Top Features” which doesn’t sound as impressive. The feature itself is pretty cool though. It’s a smart way of managing your full and transaction log backups. Sounds good right? Bad news is it backs the data up to Windows Azure, not cool if your company isn’t ready to accept “the cloud”. Still it’s V1 and you can bet enough people will scream and shout that there will be an on-prem version in the offing in the next few releases.
This blog Backup and Restore Enhancements in SQL Server 2014 CTP2 provides a good overview of all the new backup enhancements for 2014.
1 – Buffer Pool Extension
At the 2013 PASS Summit I was lucky enough to co-present a session on SSD technology and a chunk of this session was on BPE, I was fortunate to spend some time with the then Program Manager for BPE Evgeny Kirvosheev as well as Sir Connor of Cunningham talking about how this feature would work. In CTP1 this was marked as an Enterprise only feature, so it really is a revelation and a testament to the aggressive nature of Microsoft to cut into market share and steal business from the likes of Oracle et al. that the decision has been made to turn this into a feature for Standard edition.
OK, a whole paragraph done without saying what it actually is! So, what is it?
Buffer Pool Extension is a change to the architecture of SQL Server. By now you are probably aware that there is a Buffer Pool (sometimes called buffer cache) which SQL Server stores data pages in for quick retrieval as trips to traditional storage layers are very expensive in terms of latency. Despite my claims of memory being a lot cheaper in point 2 it is significantly more expensive than both traditional and NAND storage. So how does an RDBMS manufacturer reduce the latency of requests with such a budgetary constraint for the majority of it’s customers? Simple, it uses an extended cache which sits as an intermediary between the memory and traditional storage this cache would typically reside on an SSD device.
The addition of this extra layer means that the latency typically associated with the trip to a traditional spindle is drastically reduced by reading from the intermediary SSD. This is a low cost approach to reducing the latency of your requests for data, especially if you have large databases where it is not cost efficient to buy NAND technology for the entire storage layer.
There is a good blog post covering all the core concepts of BPE written by Evgeny here – Buffer Pool Extension to SSDs in SQL Server 2014