Five Reasons to Upgrade Your SQL Server

Microsoft SQL Server is a state-of-the-art database management system. If you ask my personal opinion, then I think it's the best DBMS out there, with so many features and capabilities. It won lots of prizes and benchmarks, and it has the best price-performance ratio in the market (my honest opinion).

If you are already using SQL Server, then you are lucky. You have a powerful tool under your hands. Make sure that you take advantage of all the great features that it has to offer, and maximize your return on investment (ROI). If you are not already using SQL Server, then maybe you should consider migrating your data to SQL Server.

But I'm not here to talk about database migration. That's a topic for a whole different article. This article is about SQL Server upgrade. Before I continue, let me stress that this article is about the on-prem SQL Server product. I will not cover here the PaaS products, such as Azure SQL Database and Azure SQL Managed Instance. These are wonderful cloud database services, and in many cases they are excellent options for modernization, but they are not the subject of this article.

There are many shops where moving the data to the cloud is not an option, whether it's due to regulations, cost analysis, or something else. If you are one of those shops, and you are currently using an old version of SQL Server, then this article is for you.

As of today, the latest version of SQL Server is 2019. Microsoft has already announced SQL Server 2022, but currently it is still in private preview, so I will ignore this version at this point. If you are already using SQL Server 2019, that's great. I still recommend that you read to the end of this article, because you might learn some tips about maximizing your investment. If you are using an older version of SQL Server, then read on...

So you are using SQL Server and it works. Why bother? Why should you add another headache to your already packed schedule of endless projects and tasks?

Well, I promised five reasons, so here they are:

Microsoft provides 5 years of mainstream support, and after that, additional 5 years of extended support, for each released version of SQL Server. In the first 5 years, Microsoft provides functional, performance, scalability, and security updates on a regular basis. This means you get constant updates, bug fixes, and new functionality, and the product that you purchased will get better all the time. Well, not all the time, only until 5 years after the initial release.

During the extended support period Microsoft continues to support the product, but provides only security updates. No more bug fixes, new functionality, improved performance, etc. But if a security breach is identified, which might allows attackers to penetrate your database, then Microsoft will provide a security patch ASAP, so that you can close the hole and protect your data. This is very important.

But after 10 years, when even extended support is ended, you're on your own. Microsoft will not support that product anymore, and will not even provide security patches. This means that if a security breach is identified, then your database is exposed, and there is nothing you can do about it, as long as you continue to use the same SQL Server version. With the increased volume of security attacks on one hand, and the increase value of your data, on the other hand, this is a risk you simply can't afford to take.

Sometimes, it's not even a question whether you can afford the risk or not. You might be under some regulation that forces you to use a supported version. In order to be compliant, you have to upgrade to a supported version. You simply don't have a choice.

Here are the relevant dates for each version of SQL Server, from 2005 up to 2019:

You can download an Excel file with all the Microsoft product lifecycle data from this page.

As you can see, if you are currently using SQL Server 2008 R2 or an older version, you are already at risk. If you are currently using SQL Server 2012, then as of today you have less than 5 months before end of extended support. Even with SQL Server 2014 and 2016, mainstream support is already over, so you will not get any additional updates except for security patches.

So if you are using SQL Server 2012 or an older version, this is a very good reason to upgrade your SQL Server to a newer version with proper support going forward.

When SQL Server 2016 was released, Microsoft launched a marketing campaign under the title "It Just Runs Faster". But in this case, it wasn't just a marketing message. Microsoft improved the core engine in various ways, and by just upgrading to SQL Server 2016 (or higher), without changing anything else, you get a performance boost across the board.

Of course, the level of performance improvement that you'll see for your system depends on many factors, such as your workload and your hardware. But there were many published benchmarks that showed dramatic improvements in throughput and overall performance for organizations that upgraded to SQL Server 2016.

This was a specific campaign around the release of SQL Server 2016, but the same applies to any major version of SQL Server. Microsoft makes lots of performance improvements to the database engine in each major version. The nice thing about these improvements is that you don't need to do anything to take advantage of them (except for upgrading, of course). You get these improvements out of the box.

I'll give you just two examples. But there are dozens, or perhaps, hundreds of improvements and performance features that you get out of the box.

The first is related to Always On Availability Groups. If you are using Availability Groups on an old version of SQL Server (2012 or 2014), then you might suffer from low transaction log throughput on the primary replica when the workload increases. It slows down your application, and it sucks. In SQL Server 2016, Microsoft made lots of changes to the code of Availability Groups, and improved a lot of things, such as reducing the amount of worker thread context switches to replicate a log block. They managed to improve log throughput by more than 300%. This is amazing!

If you want to read more about this magic, then you can read Bob Ward's article from 2016: SQL Server 2016 - It Just Runs Faster: Always On Availability Groups Turbocharged.

So if you are using Availability Groups today, and you suffer from low application performance because of that, then by just upgrading to SQL Server 2016 (or higher), all of these problems might just go away.

The second example is a feature called Table Variable Deferred Compilation, which was introduced in SQL Server 2019. This feature is part of an amazing family of features called Intelligent Query Processing, which was first introduced in SQL Server 2016, and got a lot better in SQL Server 2017 and then in SQL Server 2019.

The goal of Intelligent Query Processing is to produce better execution plans and improve query performance in various ways. Our feature (Table Variable Deferred Compilation) aims to do that by improving the way table variables are handled.

If you are using table variables in your application, then you should know they can cause serious performance issues. The reason is that unlike other types of tables, they don't have statistics, so the optimizer, which is responsible for generating the execution plan, can't estimate the number of rows in the table variable. In many cases the optimizer chooses the wrong execution plan because of that, and your application will suffer.

I am writing a series of blog posts about table variables and temporary tables, so if this topic interests you, then here is a link to the first post in the series: The Complete Guide to Temporary Tables and Table Variables - Part 1.

There is a good chance that you are already using table variables in a lot of places in your code, and you are not even aware of the performance impact it has on your system.

The best thing to do, in most cases, is to replace those table variables with temporary tables, which do have statistics. But if you have a lot of code in different places, then this might be time consuming and risky. In this case, you can solve the problem by upgrading to SQL Server 2019.

With the new feature, Table Variable Deferred Compilation, the optimizer waits until just before the execution of a query that references a table variable. Then it generates an execution plan for that query at runtime. The big difference is that even without statistics, during runtime, the optimizer knows how many rows are in the table, and can produce a much better plan.

Once again, you don't have to do anything other than upgrading. If you are currently suffering from performance issues because of the use of table variables, then most likely, by just upgrading to SQL Server 2019, your problems will go away. It's magic!

And these are just two examples out of so many new features and performance improvements that you get out of the box, without changing anything. And it's a very good reason to upgrade, if you ask me.

Each version introduces more improvements, but also includes all the improvements from previous versions. This is why I recommend upgrading to the latest version, which is SQL Server 2019 as of today.

The previous section was about new features and improvements that you get out of the box, without having to change anything in your code or your database configuration. This one is about new features for which you can decide whether you want to take advantage or not, but they require some level of adjustment to apply the new features.

Each major version of SQL Server ships with a lot of new features that you can choose to leverage for your applications. I'll give you just 3 examples.

Accelerated Database Recovery - This new feature was introduced in SQL Server 2019, and it aims to improve availability by redesigning the database engine recovery process and how the transaction log is used. You need to enable this feature, but once you do, you will get fast and consistent database recovery, instantaneous transaction rollback, and aggressive log truncation. No more transaction logs that grow out of control or long downtimes due to database recovery.

Columnstore Indexes - These indexes are performance boosters for data warehouse workloads and other analytical queries. A columnstore index stores and manages data in a compressed form by using a columnar data format. Coupled with batch processing mode, they can achieve gains of up to 10 times in query performance. This feature was first introduced in SQL Server 2012, but it was very limited. It got a lot better in the following versions, and starting with SQL Server 2016, columnstore indexes are one of the most powerful performance features in SQL Server.

Always Encrypted - This is a security feature that was first introduced in SQL Server 2016, and has already been enhanced in later versions. It allows developers to encrypt sensitive data and work with that data transparently. It provides a separation between those who own the data and can view it, and those who manage the data but should have no access. If you store and manage sensitive data, such as credit card numbers, then this is an important feature to consider.

Again, these are just 3 examples out of so many. You can see the full list of features as of SQL Server 2019, broken down by category and edition, on this page.

I guess that for many people, this would be the number one reason to upgrade. Yes, you can actually save money. The upgrade itself costs money, and it can even be quite expensive. It will cost you in working hours, possible downtime and business disruption, maybe you need to purchase new licenses, etc.

But on the other hand, you might be able to save even more money from the upgrade, which means this project can have a positive ROI (Return On Investment). If you save more than what you pay, then it's a good investment, at least from a financial point of view. Your CFO will love it.

So how can you save money, you're probably asking. There are many ways. I'll give you a few.

First, thanks to all the performance improvements mentioned in the previous sections, you might be able to run the same workload (your applications) with fewer resources. For example, maybe you have a query that scans and aggregates a very large table. This query runs around 20 minutes and consumes a lot of resources (CPU, memory and disk IO). If you create a proper columnstore index, then the same query might finish in less than a minute and consume so much less resources. Now, if you add up all the performance improvements and all the queries that can benefit from these improvements, then you might be able to reduce resource consumption by 50% or even more.

How does this translates to saving money? First, it frees resources on the server, and it allows you to scale without having to purchase more hardware. If your SQL Server runs in a virtual environment, then you can allocate less resources to the server and use them for another virtual server instead of purchasing new hardware. Another way to look at it is the time you might save, because maybe today your support reps spend a lot of time handling calls from users complaining about slow response times, and your DBAs spend a lot of time trying to troubleshoot and improve performance. After the upgrade, you will be able to focus more on development of new features and less on performance tuning.

Another important thing you should know is that when SQL Server 2016 Service Pack 1 was released, Microsoft announced a dramatic change in the product and made many features that were only available in Enterprise Edition before that release - available in all editions. Here is the original announcement.

What it means for you is that if you are using today an old version of SQL Server, and you are using Enterprise Edition for features like table partitioning or Change Data Capture, then now you can upgrade to a recent version of SQL Server (2016 SP1 or higher), and at the same time downgrade from Enterprise Edition to Standard Edition or maybe even Web Edition, and continue to use the same features. By doing this, you can save a lot of money on licensing. This is a big deal!

Finally, let's talk about business continuity. For some shops, every minute of downtime translates directly to a big loss of revenue. By leveraging improvements to the core engine as well as features such as Always On Availability Groups and Accelerated Database Recovery, you can improve your SQL Server availability and reduce the amount of downtime. Let's say your business loses $1,000 for every minute of downtime, and by upgrading you manage to reduce the annual downtime from 30 minutes to 8 minutes. That's a nice saving of $22,000 every year.

If you want the best results, you need the best people. Naturally, there are only a few "best people", and everybody wants to hire them. So you have to be competitive, and this includes things like the salary, bonuses, maybe cool gadgets. But it also includes the technology you let them work with.

One of the things that make the best people so good is because they like to be innovative and work with the latest technology. If you are going to tell them that they are going to work with SQL Server 2008 R2, then, well, you are not being very competitive.

But if you are going to tell them that they are going to use in-memory technology in SQL Server 2019, then you have a much better chance to hire much better people.

I hope you are convinced by now that you should seriously consider upgrading your SQL Servers, if you are currently using old versions. In my opinion, each one of the reasons I gave you is a good reason by itself, and you have all five of them. You will protect your databases, improve performance, increase functionality, save money, and attract talented people.

But upgrading your mission-critical production SQL Server is not a kids game. There are many risks involved, and you should carefully plan the project, prepare a proper test environment and test your process, monitor the environment, prepare a risk analysis plan and a rollback plan, and more.

If you have the right people, you can do it yourself. Or you can contact us. We have many years of experience with SQL Server upgrades of all types, shapes, and colors. We've seen it all. Based on the extensive knowledge and experience of our team, we developed a structured solution with a proven methodology to plan and implement a SQL Server upgrade in the fastest way and with minimum risks.

You might be surprised - we call this solution: SQL Server Upgrade.