Database Administration sql 2014

Published on December 15th, 2014 | by Alejandro Cordero

0

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 MIN_IOPS_PER_VOLUME and 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:

 

sql 2014

 

Tags: ,


About the Author

SQL Server DBA with over 8 years of experience, developer for .Net , Certified Scrum Master and Entrepreneur.



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Back to Top ↑
  • Sign Up For Our Newsletter

    Stay up to date on the latest from SQL Server products and Features.

    Sign Up Now

    Sign up for mailing list and receive new posts directly to your inbox.

    We promise never to share or sell any of your personal information.

  • Recent Posts

  • Categories

  • Archives

  • Tags

  • Topics

  • Subscribe to our RSS Feed


    sqlturbo.com A SQL Server Web Blog
    Canada, Ontario, Ottawa.
    http://sqlturbo.com

    The Authors