Below are the new or enhanced features of SQL Server 2014
1. Memory Optimized Objects
In SQL Server 2014, memory-optimized Engine is basically integrated in SQL Server engine. You can define SQL Server objects such as Tables and Store procedures to run in the memory. In previous versions, almost all the operations on sql server objects contained I/O process that means it involved disk back and forth during sql server operations such as select, insert, update and delete. With Memory Optimized Engine, these operations will take place in memory instead of SQL Server going back and forth to Disk drives.
Memory Optimized OLTP is completely transactional based, all the processing take place in the form of transactions, however it is being done in memory if you declare those objects to take advantage of memory-optimized feature. There are some prerequisites that need to be considered before you can use this feature. I will be covering all that in a separate post.
2. SQL Server Data Files in Windows Azure
This features is mainly to move or have your database files on windows Azure Blob storage, Azure Blob storage basically is huge storage system that’s chunked in 1TB of Blobs, you can use this storage to keep your database files, it provides benefits of migrating database from one location to another, replicating database files from different location within the Azure cluster or you can copy it back to on-premises system in order to restore the database using attach/detach method.
3. Hosting SQL Server in Windows VM
This feature enables you to deploy your existing on-premises database to Windows Azure SQL Server VM, in other words you can migrate your existing on-premises SQL Server to Azure using migration Wizard.
4. Backup and Restore
If you are familiar with SQL Server 2012 SP1, There is new option introduced in 2012 SP1 was to backup SQL Server Database to URL, however this option can only be utilized using either T-SQL , PowerShell and SMO. But in SQL Server 2014, you can use management studio to accomplish backing and restoring your databases to URL.
Keep in mind when scheduling SQL Server Database backups, the only URL supported in this operation is Windows Azure URL, you can’t use your existing URL’s i.e. Sharepoint file system URL. May be Microsoft next releases of SQL Server 2014 include this feature to use other URLs than Windows Azure.
5. Redesign of Query Plans and Performance boost
Query Plans use cardinality estimator to provide query execution plans in SQL Server, in SQL Server 2014; estimator is redesigned to improve accuracy as well as quality of query plans which helps to improve the performance of your query. Again this is just an enhancement to provide a quality query plans. Personally, I didn’t see much of a change using it, there might be some queries which can take advantage of this enhancement while others remain the same.
6. Reduced Latency – Delayed Durability
I found this feature quite beneficial, however it totally depends on organizational structure for example, Finance oriented organization, it is very important to NOT lose any data, while other organizations, it is tolerable to lose some data as long as they can recover most of the data. So in SQL Server 2014, there is a concept introduced called delayed durability, which actually means that client application will not wait from SQL Server that its transactions are written on the disk, transaction on sql server side will be stored in memory and will be written in batches so it will reduce lots of I/O operations hence reduce the latency. But again you have to be very careful making your transactions delayed durable or not.
7. Always On – Availability Group (AG)
a) Always on concept was introduced in SQL Server 2012. Purpose of this concept was high availability of the database and isolates some of the heavy read such as reporting from the database. Always on was accomplished using feature called Availability group, basically you can provide replica of your production database as read only database onto another SQL Server instance so that reporting or other heavy read of database can run on secondary replica without causing lots of load on actual production database. In SQL Server 2012, secondary copy of your database were limited to 4 replicas, however in SQL Server 2014, it is increased to 8 secondary replicas.
b) If primary replica goes down, secondary replica will remain online for read operations in SQL Server 2014.
c) Failover Clustered instances can share clustered shared volumes as clustered shared disks
8. Table Partitioning and Indexing
In SQL Server 2014, you can rebuild individual partition of a partitioned table
9. Columnstore Indexes
This feature is one of my favorite, columnstore indexes were introduced in 2012, however in 2012, you can only create non-clustered columns store indexes, but in SQL Server 2014, you can create Clustered Columnstore indexes and they are updatable. It greatly enhanced the data warehouse performance.
10. Buffer Pool Extension
Buffer Pool Extension is a great feature of SQL Server 2014, it enables buffer pool to extend buffer pool size between RAM and SSD (Solid State Drive). Just to give you a little background about buffer pool if you already don’t know it, buffer pool is primary memory allocation source of SQL Server. It is a key component in achieving efficiency. It serves two purposes.
1. Access and update database pages
2. Buffer pool to reduce I/O
SQL Server 2014 more efficient in its operations.
11. Resource Governor Enhancement
Resource Governor helps to control your resources such as CPU, IO etc. In SQL Server 2014 you can assign physical I/O resource of a particular resource pool, depending upon priority and urgency of the operation, this value can be set to manipulate the resources in SQL Server 2014
Resource Governor helps to control your resources such as CPU, IO etc. In SQL Server 2014 you can assign physical I/O resource of a particular resource pool, depending upon priority and urgency of the operation, this value can be set to manipulate the resources in SQL Server 2014
12. Incremental Statistics
This feature will allow you to create or update statistics of a particular partition if your table is partitioned. Before that, you had to update statistics of the whole table
This feature will allow you to create or update statistics of a particular partition if your table is partitioned. Before that, you had to update statistics of the whole table
13. Online Index Operation
Online index operation progress can be monitored real time as partitionID and PartitionNumber column is added in SQL Server 2014 and you can query the progress in order to get real time status of online index rebuild Job
Online index operation progress can be monitored real time as partitionID and PartitionNumber column is added in SQL Server 2014 and you can query the progress in order to get real time status of online index rebuild Job
14. Database compatibility Level
Compatibility 90 (SQL Server 2005) is not valid in SQL Server 2014
Compatibility 90 (SQL Server 2005) is not valid in SQL Server 2014
