Friday, April 11, 2014

SQL SERVER :- New features in SQL Server 2014


New features in SQL Server 2014


The new release of the SQL Server Database Engine have  new features and enhancements that increase the power and productivity of architects, developers, and administrators who design, develop, and maintain data storage systems. These are the areas in which the Database Engine has been enhanced.
SQL Server 2014 is jockeying for King of OLTP. While completely bypassing BI enhancements, Microsoft has attacked a host of OLTP performance problems from many angles: slow disk performance with in-memory tables, slow log performance with delayed durability, and maintenance concurrency with lock priority. It didn't stop there. The new SQL Server release puts the kibosh on excessive I/O with new Resource Governor I/O control, addresses memory issues with an SSD buffer pool extension, and increases availability through integration with the Azure cloud.

      In-Memory OLTP :-    The most important  new feature in SQL Server 2014 is In-Memory OLTP (aka "Hekaton"), which allows you to move individual tables to special in-memory structures. The performance boost can be as huge as 30x. There are a number of limitations and special requirements for these tables, so they won't work under every circumstance. But when they do, your OLTP performance will go through the roof. This is better than other in-memory solutions that require the entire database to be placed in memory. You can get more performance by converting existing stored procedures into in-memory procedures, too. You'll need to test to make sure your tables are compatible, but if they are, you'll love this feature

 
Ø In-Memory OLTP achieves significant performance and scalability gains by using:

  1. ·                 Algorithms that are optimized for accessing memory-resident data.
  2. .          Optimistic concurrency control that eliminates logical locks
  3. ·                Lock free objects that eliminate all physical locks and latches. Threads that perform transactional work don’t use locks or latches for concurrency control.
  4. ·        Natively compiled stored procedures, which have significantly better performance than interpreted stored procedures, when accessing a memory-optimized table.
 



          Managed Backup to Azure :- There are plenty of small and midsize shops without qualified DBAs on staff. Quite often these shops don't discover that their backups aren't being handled properly until it's too late. True to its name, Managed Backup automatically backs up your database (or your instance) based on your defined recovery interval and workload patterns. When the system determines the data has changed significantly enough, it takes a backup to Azure. This feature only works with Azure blob storage. But since your backups are already offsite, there's no need to worry about tapes.


         Backup Encryption :-   One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. SQL Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backups.



          Resource Governor for I/O :-     Disk I/O is typically the most constrained resource of a database system, and often a large or rogue query will take up more precious I/O resources than you can afford. Microsoft has finally given us some control over runaway I/O. With Resource Governor for I/O, you can now put queries into their own resource pool and limit the amount of I/O per volume they're allowed. MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME set the minimum and maximum reads or writes per second allowed by a process in a disk volume.



        Delayed durability :-    In SQL Server , changes to data are written to the log first. This is called write ahead logging (WAL). Control isn't returned to the application until the log record has been written to disk (a process referred to as "hardening"). Delayed durability allows you to return control back to the application before the log is hardened. This can speed up transactions if you have issues with log performance. Nothing is free, though, and here you sacrifice recoverability. Should the database go down before the log is committed to disk, then you lose those transactions forever. It may be worth the risk if your log performance is severely degrading application response times.



         Buffer pool extension :- Creating a buffer pool extension for SQL Server 2014 is like being able to define a different page file in Windows. As data pages move into memory, they begin to fill up the buffer pool. If the buffer pool fills up, the less frequently used pages will be paged to disk. Then when they're needed again, they'll be swapped with something else in the buffer pool and moved back into memory. The buffer pool extension option allows you to define an SSD as a buffer file location. Because SSD is so much faster than spinning disk, the paging is considerably quicker, which increases performance dramatically in some cases. You can define a buffer pool extension file up to 32 times the size of your memory.



         Managing the Lock Priority of Online Operations  :-     We can now specify a lock priority for online re-indexing. In the SQL Server 2012, long-running queries could block re-indexing operations, chewing up your maintenance window while your re-index op sits waiting, doing nothing. In SQL Server 2014, you can specify how your re-index operation will handle being blocked. You specify how long it will wait and what to do when the wait is over. Will you have it follow traditional behavior and wait indefinitely? Will you have it terminate and move to the next table? Or will you kill the blocking query, so your re-indexing can complete? It's your choice.
 


          SQL Server Data Tools for Business Intelligence  :-   The new SQL Server Data Tools for BI (SSDT-BI) is used to create SQL Server Analysis Services (SSAS) models, SSRS reports, and SSIS packages. The new SSDT-BI supports SSAS and SSRS for SQL Server 2014 and earlier, but SSIS projects are limited to SQL Server 2014. In the pre-release version of SQL Server 2014, SQL Server Setup doesn’t install SSDT-BI. Instead, you must download SSDT-BI separately from the Microsoft Download Center.


       May this post will help you for know about new feature of SQL SEVER 2014.