With the release of SQL Server 2014 on Aril 1 2014, there are some new things you need to know about this version of SQL Server. In this article I am going to introduce these new features.
Changes or new features to the database engine seem to have been the big winner for this new version of SQL Server. In comparison SQL Server 2012 did not release many new features in the database engine. Apart from a couple of changes of enhancements in T-SQL (Inline specification of Cluster and Non Clustered Indexes is now supported), some new DMV’s or some modifications to some DMVs to provide further information and some security enhancements (Connect Any Database, Impersonate Any Login and Select All user Securables) the following are the main new features available in this release:
AlwaysOn Enhancements cover both AlwaysOn Failover Clusters and AlwaysOn Availability Groups. AlwaysOn Availability Groups were the big enhancement with the release of SQL Server 2012 for your High Availability and Disaster Recovery requirements. The following six improvements are available:
- Hybrid Solutions made easier with Add Azure Replica Wizard
- Maximum number of secondary replicas has increased from 4 to 8
- Improvements to Readable Secondary availability during a quorum loss or disconnected from the primary replica
- Clustered Shared Volume (CSV) support with FCI’s
- A new DMV (sys.dm_io_cluster_valid_path_names) & DMF (sys.fn_hadr_is_primary_replica) supporting AGs
- Enhancements to the following three DMV’s
Backup and Restore enhancements are available with SQL Server 2014. With this new version you now have the ability to perform backups to a URL location from within Management Studio. This ability to perform backups to a URL location was introduced with the release of SQL Server 2012 SP1 CU2, but was only available from T-SQL, Powershell or SMO. Taking the backup to URL functionality, SQL Server 2014 now has a feature that you can schedule backups to go to Windows Azure Blob storage. This managed backup feature can be configured at the instance or database level to provide you greater flexibility in your environment. For those of you who are going to look at backing up to an Azure blob storage you may also want to look at this new security feature allowing you to encrypt your data while you are creating your backup. Just like performing encryption on your database you need to ensure you design this process so as to not lock yourself out of your backups.
Buffer Pool Extension is a new feature that allows you to utilise a hybrid solution to memory utilisation as long as you have access to an SSD. The Buffer Pool Extension holds clean data pages only providing you guarantee of no data loss in the event of a system crass or disk failure. Your buffer pool extension needs to be larger than the existing instance memory utilisation and requires an outage to change. The Buffer Pool Extension is supported with Failover clustered instances to improve your performance in you clustered environment. To read on how to configure this feature and to monitor your environment once it is configured have a look at the Buffer Pool Extension article.
Delayed Durability has been introduced with this release. This feature gives you the ability to reduce your latency by allowing control back to the client prior to the transaction being completed. Before making any changes and utilising this new feature you need to Test, Test and then Test again to ensure that you are happy with the results and risk mitigation of the potential for data loss. Have a read of “Full Vs Delayed Transaction Durability” in the Control Transaction Durability article to fully understand this feature to see if this works in your environment.
Improved Query Plan estimation is achieved with some changes to the Cardinality estimation logic. This new estimator is available for all databases that are created in SQL Server 2014, however if you are going to undertake an upgrade process you will need to include some extra steps in your process and testing to ensure your environment utilises this new feature and you get the improved performance. To have a look at the steps required in the upgrade process have a look at this Cardinality Estimation article.
In-Memory DW (Columnstore Indexes) were first introduced with the release of SQL Server 2012 to help improve the performance in your data warehouse. However it has a big draw back in that it is not updateable. With the release of SQL Server 2014 this has changed with the introduction of Updateable Clustered Columnstore Indexes. These have a new compression that greatly reduces the storage requirements and helps with the performance gains by allowing more data to be stored in memory. To get a better understanding of the change in table and index structures used have a read of Columnstore Indexes Described.
In-Memory OLTP (Hekaton) is a new feature that has the ability to significantly improve your OLTP performance. This is achieved with the introduction of this memory-optimized table structures and associated natively compiled stored procedures to access your data in the most optimal fashion. You can read more on how In-Memory OLTP can help you improve your performance from In-Memory OLTP article.
To help you identify potential tables or stored procedures that could be appropriate to be ported to memory optimized objects, there are two new Advisor wizards available in SQL Server Management Studio:
Both of these wizards look at potential blockers that would prevent the table or stored procedure from being ported into memory. Depending on the results that come out these will help you identify the amount of work that is required to get the tables or stored procedures to a point of being suitable for transition into memory.
Managing lock Priority of Online Operations gives you the ability to place your DDL operation into a Wait_At_Low_Priority queue and decide if you want to kill the DDL process or the blocking process. This is a pretty cool option however the use of it needs to be fully understood and tested to ensure this functionality is used appropriately. Read more about the change to the Alter Table or Alter Index options looking specifically for the low_priority_lock_wait option to understand the new syntax and how you can apply this in your environment.
Resource Governor has been around since SQL Server 2008 was released but with the release of SQL Server 2014 we now have the ability to control the maximum and minimum limits for I/O in our SQL Server environment to be able to provide a consistent level of performance or guarantee for specific resource pool. To see how to configure your I/O requirements have a look at the Create Resource Pool article.
Single Partition Online Index Rebuilds are now available to help improve the performance in your environment. Now having the ability to perform a single partition index rebuild online this can assist in reducing your resource requirements and utilisation as well as help you meet your uptime requirements. Using this in conjunction with Managing lock Priority of Online Operations can give you greater control of your database maintenance and ensure the operations complete.
SQL Server Data Files in Windows Azure provides you the ability to create your SQL Server databases either on-premise or on Azure virtual servers and have your database files located on Windows Azure Blob storage. One of the big benefits of this feature is the simplification of moving databases between servers via the Detach and Attach DB operations. Your data file does not move, you attach the database on the second server pointing to the existing location of the data and log files.
To read more of the benefits of this feature are available from the SQL Server Data Files in Windows Azure link.
If you are thinking of utilising this new feature make sure that you investigate the costs associated with the Windows Azure Storage. Pricing details are available.
Analysis Services and Business Intelligence features do not get the same amount of love this time round as the database engine. Analysis services has some added functionality supporting Power View Reports against Multidimensional models this is the same as is currently available with SQL Server 2012.
Some of these new features are pretty cool and can give you some pretty big performance gains or an even better approach to meeting your SLA’s, however if you are looking at upgrading to SQL Server 2014, if you have not done so already have a read of my previous blog post on SQL Server 2014 : Licensing you need to know.
Leave a Reply