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:
- · Algorithms that are optimized for accessing memory-resident data.
- . Optimistic concurrency control that eliminates logical locks
- · Lock free objects that eliminate all physical locks and latches. Threads that perform transactional work don’t use locks or latches for concurrency control.
- · 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.
