Last month this newsletter described some of the most important benefits in SQL Server 2014 for Enterprise Edition users, notably Memory-Optimized OLTP, but complained once more about the dearth of new features for Standard Edition Users.
However, the April 1st rollout of SQL Server’s newest version came with one very pleasant surprise. At the last moment, Microsoft decided to double the maximum amount of memory that can be used by a Standard Edition instance of SQL 2014.
This change may have been driven by the considerable number of negative comments being blogged by prominent SQL Server experts on this subject. Who knows, this little newsletter, full of my constant griping, may have been some small part of it. But Brent Ozar’s withering critique of Microsofts policy toward Standard Edition users might have been the main stimulus for change.
At any rate, doubling the maximum amount of memory to 128 gb is welcome news for all of you Standard Edition users who have been struggling to extract better performance despite a 64gb limit on memory.
Besides this considerable benefit, here are a couple of other useful new features available to Standard Edition users in SQL 2014.
Buffer Pool Extensions is one. This feature allows you to extend SQL’s data buffer onto a solid state disk or memory card. As you probably know, the buffer pool is a memory space where SQL stores data that it has retrieved from disk so that it does not have to load it from disk into memory again and again.
Using Buffer Pool Extensions, you can dramatically increase the amount of data you can buffer, which in turn reduces the number of times SQL has to go to the disk. Since disk access is orders of magnitude slower than memory access, this feature has the potential to improve performance significantly because it can store a much, much larger portion of your active data in memory. It is even possible to buffer all the data in a moderately sized database.
If you are facing compliance issues for Healthcare (HIPAA) or Credit Card (PCI) standards you may be struggling with decisions about encryption requirements. SQL Server 2014 has at least a partial solution available in Standard Edition for that, Encrypted Backup.
As you probably know, encryption only protects data at rest, i.e. the database files and backup files themselves. With Backup Encryption, you can make an awfully strong case to your standards agency that you have done due diligence in this area because backup files are far more likely to be stolen than the data and log files.
To steal the data and log files, the thief would have to stop the sql server in order to put the data and log file into a consistent state then copy the files. It is unlikely that the necessary shutdown of sql server would go unnoticed. Whereas anyone, employee or hacker, who has access to a server where database backup files are stored can easily steal all the data in your database in a very portable format.
Encrypting backup files will cause backups to use more cpu, but there is less overall impact than using full encryption technologies like Transparent Data Encryption. And, you do not have to buy Enterprise Edition to use Backup Encryption.
All in all, I think that 2014 is an important release, the most important of the last few releases, especially in regard to useful features for Standard Edition. Of course I wouldn’t recommend upgrading a production instance until at least one service pack has been released to clean up the inevitable bugs. However, I would recommend that you take a look at a trial version to familiarize your DBA with the new features available.