DBInsight’s Blogs

Reasons to Upgrade to SQL Server 2014

Posted by Rob Risetto on August 28, 2015

As you know SQL Server 2014 was released this month so the question is why would you consider upgrading to SQL Server 2014?  If you have a budget to implement the Enterprise Edition then you probably have more of a case to upgrade now, though items 3, 9 and 10 below may provide enough performance benefit to also consider

upgrading a Standard Edition implementation.

Here are some of the reasons for upgrading to SQL Server 2014, I have tagged the points that relate to the Enterprise Edition:-

  1. You have performance issues with a high end OLTP system or you want to scale up your OLTP system for a higher workload then the SQL Server 2014 OLTP In-Memory engine may be the answer. Alternatively, perhaps you want to improve the performance of Data Warehouse ETL staging or transformation processing, if so, then you can use the Schema Only memory optimised staging tables to also eliminate the overhead of writing to the transaction log  file.(Enterprise Edition)
  2. For Data Warehouse implementations where users need to query a DW database, then the new updateable columnstore index feature can increase query performance and provide flexibility in updating your DW database. i.e. The new updateable columnstore index enables updates to be performed to the underlying table without first needing to drop the columnstore index. (Enterprise Edition)
  3. If you need more memory for the SQL Server buffer pool but there are no more slots in the server, then you can extend the Buffer Pool to SSD storage using the Buffer Pool Extension option.
  4. Up to 8 readable secondaries are now possible in the second generation of Availability Groups, there is also a Replica Wizard to help you manage the secondaries.  Readable secondary replicas will now still be available for read-only access, even when the primary replica is unavailable (Enterprise Edition)
  5. If you plan to deploy to Azure, the integration of Azure features in SQL Server 2014 will make your transition easier. For example,
    • You can use an Azure’s subscription-based backup to backup directly to a Azure storage target, i.e you can specify a Azure storage URL as the backup destination.
    • You can use an Azure IaaS VM as an Availability Group partner for your on-premise SQL Server. The AG Wizard will assist you with the setup.
    • Use the new Cloud Migration Wizard launched from the SQL Server Management Studio 2014 to migrate databases from on-premise SQL Servers to either an Azure SQL database or to a Azure Windows IaaS SQL Server.
  6. The Resource Governor now provides an option to restrict (throttle) the number of physical IOs per second for a workload group. Consider a scenario where you just want to reduce the IO impact of your database maintenance tasks (eg. DBCC Check or Index Rebuild), you can use the Resource Governor to throttle the IO per second down to a reasonable level, yes the maintenance task will take longer but other user tasks will be less impacted by the IO generated per second by the maintenance. (Enterprise Edition)
  7. Configure the database backup encryption option in your backups to add another layer of security especially if you running your SQL Server in the cloud.
  8. Online Index Rebuild and Update Statistics enhancements to allow processing on a per index partition basis. (Enterprise Edition)
  9. Query optimisation improvements in SQL Server 2014 can make your current queries run faster. For example the cardinality estimation logic has been re-designed in SQL Server 2014 to improve the quality of query plans. See new white paper Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator for more information.
  10. You can increase the query performance of your Standard Edition SQL Server since SQL Server 2014 Standard Edition can now access 128 GB of memory up from 64 GB.

BTW Cumulative Update 1 has been released for SQL Server 2014, see the KB (here)

Spread the love

Leave a Reply

Notify of