Have you been planning on installing or upgrading to a new SQL Server? Do you know which version is right for you? Read this article to find out which version of SQL Server (2008/2008 R2/2012/2014/2016/2017/2019) you should consider installing.
Note: As extended support for SQL Server 2008 and 2008 R2 versions ended in July 9, 2019, let’s skip and move on to discuss the reasons for installing other SQL Server versions. In case you’re hosting 2008/2008 R2 versions of SQL Server in an Azure VM, you can still get three years of free extended support (i.e., until 2022).
When Should You Install SQL Server Version 2012?
The reasons you should install 2012 version of SQL Server are as follows:
- Application with support for 2012 – You’re running an application that supports only SQL Server 2012 version, but does not support any newer version.
- Only need security updates – You can do without installing service packs and cumulative updates, as long as Microsoft provides security updates (because mainstream support for 2012 has ended but the extended support ends in July 2022).
- Can wait for extended support to end – Building another SQL Server in the next 2 years (because 2012 version extended support ends in July 2022) doesn’t seem a problem.
- Can buy a third-party tool to encrypt backup – You don’t want to encrypt backup data or you’re ready to purchase a third-party tool for database backup encryption.
The 2012 release rolled out several new features, including ‘AlwaysOn Availability Groups (AAGs)’, ‘FileTable Storage’, and ‘Columnstore Indexes’, to name a few. However, considering the High Availability (HA) and Disaster Recovery (DR) enhancements and several performance enhancements in SQL Server 2014, it’s good to consider planning to move to the 2014 version.
When Should You Install SQL Server Version 2014?
Reasons why you should install the 2014 version as listed below:
- Application with support for 2014 – You’re running on application that supports only the SQL Server 2014 version, but does not support 2016 or higher version.
- Higher availability – Because SQL Server 2014 introduced enhancements to an AlwaysOn Availability Group, allowing secondary replica to be readable when the primary production server is down. This is a big advantage over SQL Server 2012 version, where if the primary becomes unavailable, even the secondary replica fails. But, keep in mind, AAGs have continued to enhance in subsequent versions.
- Native support for backup encryption – You don’t want to buy a third-party tool to encrypt database backup, but need support for native backup encryption.
- New user-defined server roles – Starting with SQL Server 2012, new user-defined server roles are added in this version that make it easy to assign permission.
Several performance enhancements were introduced with the release of SQL Server 2014; these include ‘Buffer Pool Extensions’, ‘In-Memory OLTP’, ‘New Resource Governor Settings’, ‘Delayed Durability’, and ‘Dynamic Memory Object Scaling’, etc. But, the newer SQL Server versions brings in more enhancements, so continue reading about the new feature set and benefits you may get after installing the 2016 or higher version.
When You Should Install SQL Server Version 2016?
- You don’t plan on upgrading to newer SQL Server version until 2025-2026 – Compared to the SQL Server 2012/2014, the 2016 version has more support life. So, you can stick with this version for a little bit longer.
- You want a well-documented product – SQL Server 2016 version has been around for quite a few years now. So you may find it to be a well-documented product.
- You are an independent vendor – With the availability of SQL Server 2016 Service Pack1 (SP1), you can try the Enterprise features in the Standard edition.
- Increased security – The Always Encrypted feature introduced with SQL Server 2016 makes data encryption much more easy and secure. Also, other features like ‘Row Level Security’ and ‘Dynamic Data Masking’ offers better data security.
- Intelligent query processing – The Query store feature in 2016 provides some cool querying capabilities. It shows the history of query executions, which is useful for DBAs to find the root cause behind performance issues. While it’s a useful feature, many people aren’t using Query Store.
- Columnstore indexes for real-time analytics – The ‘Columnstore Indexes’ is not a new feature, and is available in each version of SQL Server. It also offers real-life analytics. Microsoft provides a comparison of what has changed in columnstore indexes with every SQL Server version.
When Should You Install SQL Server Version 2017?
- You’re interested in applying patches more quickly – With newer releases like SQL Server 2017 version, the patches are deployed even more quickly every 30-60 days.
- Better query processing capabilities – With SQL Server 2017, you will find some pretty cool query processing improvements. These include, “batch mode adaptive joins”, “batch mode memory grant feedback”, and “interleaved execution.”
- You need high performance columnstore queries – 2017 has added some stuff for batch mode execution plans.
- Supportability on Linux – If you’re determined to run SQL Server on a Linux-based system, check out the Release notes for SQL Server 2017 on Linux and fixes & improvements introduced in it.
- You want to perform machine learning within SQL Server – Because Python support is integrated in SQL Server 2017 version, you can leverage Python for machine learning.
Besides the above-listed features, cloud-focused changes, graph database capabilities, adaptive query optimization are other benefits of installing the 2017 version of SQL Server.
When You Should Install SQL Server Version 2019?
Here are the reasons why you should install the 2019 version:
- Long shelf life – Support for SQL Server 2019 isn’t going to end anytime soon. In fact, it’s supported until 2030. This gives you enough time to keep working with the newer version.
- You’re comfortable with frequently installing the patches – With newer SQL Server version, you can expect to get software fixes every 30-60 days. This is a big benefit, as it can help you resolve some pretty significant issues.
- Accelerated database recovery – One of the biggest pain for database administrators has been waiting for a long time for the restore process to complete. But using the Accelerated database recovery feature introduced in 2019, you can expect faster SQL database recovery.
- Your work revolves around user-defined functions and table variables – In the 2019 version, Microsoft has introduced a lot of performance improvements for queries using Scalar User-Defined Functions (UDFs).
- Better handling of table variables – SQL Server handling table variable have had a bad reputation. But that seems to have changed with the release of version 2019 due to the ‘Table Variable Deferred Compilation’ feature.
- You prefer experimentation over documentation for learning – Since 2019 is the latest version, you might not find many best practices around the features introduced with it. So, you can expect to learn more as you experiment working with the newer version and using its features.
Conclusion
This article outlined reasons for installing a SQL Server version based on factors like the end of (mainstream/extended) support, cost, familiar environment, latest feature set, etc. Once you’ll go through the list of reasons, you might find version 2017 of SQL Server a good choice owing to a good balance of performance enhancements, stability, and Microsoft support. You can also look forward to installing SQL Server 2019, but more testing will be required as it’s relatively new compared to 2017 and other versions.
If you’re still confused about choosing the right version, make sure to select one that offers features needed by your database environment.
Once you’ve decided to migrate to a newer SQL Server version, run DBCC CHECKDB against the databases to ensure their consistency. If it fails, you can try restoring the database from backup. If there is no backup, you may consider using SQL repair tool to regain access to your database to its original consistent state.