Published on December 15th, 2014 | by Alejandro Cordero0
SQL Server 2014 Top 8 New Features
Next I will cover the top SQL Server New Features in SQL 2014 version, specifics on each feature will be covered in the future, and some of them already have a great article online, hope you like this:
1- In-Memory OLTP
For the SQL PASS 2013 held at Charlotte, this was one of the most exciting features explained at the Keynote, with SQL Server 2014 In-Memory OLTP you will be able to allow individual tables to specially reside in a memory structure. The performance boost can be huge, at the keynote they showed real case scenarios with people going up to 30X. This really sounds like a turbo button doesn´t it?
By the way you can get more performance by converting existing stored procedures into in-memory procedures, too. However there are some restrictions and also best practices set around this. Check this article on use cases for In-Memory OLTP.
2- SQL Server Data Files in Azure
The idea behind this new feature, is to run your instance locally, but place your data files into the Azure blob container. This can offer advantages for certain projects such as migrations, however there is a performance impact, every transaction will be pushed across the internet(yes your transactions will flow into the magic cloud) and this will impact your application. If you also have sudden data growth this could impact your wallet. I think of this option for archives that you have to keep because of some audit or government restriction, and you just want your data live and available for compliance. Probably I am not creative enough, and there could be more uses of this, feel free to comment
3- Updateable columnstore indexes
When columnstore indexes appeared in SQL Server 2012, they were great, an improvement up to 10X could be experienced on data warehouse queries, some could describe this as a index running from a memory structure… but they had a limitation, they were not updateable, every time you wanted to do a data load you needed to plan and rebuild your index as well. There are some restrictions on clustered and nonclustered indexes, but now the index will be updatable and easier to maintain.
4- Managed Backup to Azure
This is the Ultimate backup tool for dummies or a very smart backup tool? make your choice, the idea behind this feature is to avoid all the scripting, automation and human error that is mixed in the backup solutions. When setting up backup strategies companies often realize too late that their setup was not the correct one for them, resulting on data loss, lack of recovery power, impact on revenue, so on. So with Managed Backups to Azure you only need to specify 2 variables:
1-Retention Period: how long you want to keep your backups
2-Backup location in Azure(normally in the form of a URL)
The rest is completely automatic. This feature will be smart enough to tell if you need full backups, differential or transaction log backups and how often you need them, based on your data load and database configuration. Backups will be stored in the cloud and you will have guarantee of point in time recovery.(again depending on your setup)
5- Azure VMs for Availability replicas
SQL Server 2014 will allow you to define an Availability Group replica that lives inside Azure. For this scenario fail over needs to be manual, but MS guarantees fast recovery. As long as your primary is up and running, you cans still run your reports from the Azure replica, to gain some performance by running complex queries outside production environment. This will give folks an easy to setup HA solution without the complexity of cost of ownership, and also avoid the stress of setting up an on premise physical solution.
6- Resource Governor for I/O
This one is actually very simple and also very awesome, if you are a DBA you know the I/O plays a huge role on your system and on your paycheck. Well you can now put queries into their own resource pool and limit the amount of I/O per volume they’re allowed. You need to define
MAX_IOPS_PER_VOLUME and just like that you will be able to encapsulate I/O loads. It does have a lot to cover, but on this article I will just focus on mentioning the fact that you don´t want to overlook this feature. Check more details about Resource governor here.
7- SSD buffer pool extension
SSD hard drives arrived and established a new trend for everyone. Hard drives are now performing extremely well with this technology, Fusion IO drives, SSD drives, even SSD SAN appeared in the market. With so much flavors out there, MS had to add something else to SQL Server 2014, and is called SSD buffer pool extension. It works just like adding a new page file to your windows machine. You can specify a new buffer so less used pages will be moved from memory to your SSD/fast drive(when memory is maxed out). It means if you ever reach your max memory, then you can decide to start paging into a fast performing device instead of paging into old spinning disks. Check detail about Buffer Pool Extensions here
8- Delayed durability
This is a very overlooked feature, and I personally really liked it. With delayed durable transactions you are able to send a transaction and regain control of the application execution thread faster. The delayed durable transactions will basically skip the write ahead logging algorithm which basically makes sure that every transaction that is going to be stored will go trough the memory, down to the transaction log and finally stored in the datafile. With delayed transactions it will reside in memory and consider it done. SQL Server later on will kick a process that will do an async save and store your data in the datafile, that is why is called delayed, the same process happens, but not on real time….. just with a delay. It does have some restrictions and limitations, some data loss can happen so you need to read the small print for this one. We cover specifics on this great article.
So why just 8 features, not 10? I am not sure, I just wanted to play with your inner OCD . Btw I also hate this picture: