What SQL Server Version Am I Running?

One of the first things we look at when doing a SQL Server Assessment (Health Check) is the SQL Server version and patch level. We don’t stop there, of course. We check a ton of other things that can affect the SQL Server’s performance, its security, and its reliability. But we start with the version of the database engine. Why? Because the version and patch level affects everything else.

If you want to skip the “why” and jump straight to the “how”, click here.

Does your SQL Server version really matter?

Microsoft releases a new version of SQL Server every 18 to 24 months. Each new version builds on the prior version, adding new features and improving existing capabilities. It’s a bit like Apple releasing a new iPhone. It’s new and shiny and can do things the prior versions couldn’t.

However, there’s a big difference. Upgrading your iPhone is really a luxury. If you don’t upgrade, you’ll miss out on a better camera and a maybe few O/S improvements. But otherwise, you’ll function just as well.

But not upgrading your database servers is another story. Not upgrading can create big problems. See Why Not Upgrading Could Be Risky for Your Data. Additionally, over time SQL Server will reach its end of mainstream support. That usually happens after five years. Extended support typically ends 10 years after the initial version release. What does the loss of mainstream and extended support mean? Check out End of Mainstream Support for SQL Server 2016 for a summary.

Your SQL Server version matters. You want to be as close to current as your application venders will support.

What’s with all these SQL Server updates?

Before releasing a new SQL Server version into the wild, the SQL Server team spends a lot of time testing. However, SQL Server is complex and over time bugs are uncovered. Bugs that need to be fixed. So, the SQL Server team continues to improve the product even after the initial release date.

Microsoft pushes out updates using a couple of vehicles. Hotfixes are issued for high priority items. Items that affect security and system integrity are often released in hotfixes first.

Cumulative Updates

Periodically Microsoft will bundle the hotfixes along with other fixes into a Cumulative Update, or CU for short. Prior to SQL Server 2017, Microsoft had even larger updates called Service Packs, or SPs. Since then, they’ve opted for a more straightforward CU approach.

Applying CUs is important. Let’s look at SQL Server 2019 CU8 released in October of 2020 as an example. The CU contained the following fixes.

• Fixed an issue that reduced throughput and caused higher CPU when you run workloads that frequently allocate and release memory, such as XML related functions.
• Query Store scalability improvement for adhoc workloads. Query Store now imposes internal limits to the amount of memory it can use and automatically changes the operation mode to READ-ONLY until enough memory has been returned to the Database Engine, preventing performance issues.
• FIX: Restoring database fails due to backup command timeout in SQL Server 2019.
• FIX: SQL Server 2019 service fails to start in Linux operating system

This is just a sampling. There were at lot of other fixes included in CU8. For a full list, see the SQL Server CU8 Release Notes.

So, keeping your SQL Server patched with CUs can improve its reliability, its performance, and its security. That’s why checking the SQL Server version and patch level is the first thing we check in our SQL Assessments. And you can easily check this, too.

How can I tell what version of SQL Server I’m running.

Figuring out which version of SQL Server you’re running is straightforward. Let’s look at a graphical way, a couple of T-SQL scripts, and a PowerShell/dbatools script.

Using SQL Server Management Studio or Azure Data Studio

If you only have a server or two to check, using the point-and-click method in SQL Server Management Studio or Azure Data Studio is convenient. Right-click on the instance name in the list of connections and select Properties. In the window that opens, look for the version information on the General page.

You can see that this one is SQL Server Developer Edition running on Ubuntu Linux. It’s version is 15.0.4073.23. But what do those numbers mean? Is it up to date or not?

Go to https://sqlserverbuilds.blogspot.com/ and you’ll see that 15.0.4073.23 represents Cumulative update 8 (CU8) for SQL Server 2019. We can also see that this instance is behind on updates. See also Is There an Update for My SQL Server?

Using a T-SQL query

Often it’s faster and easier to run a quick script to gather the version information. This is especially true if you need to collect the information for dozens or even hundreds of SQL Servers.

You can use a couple of different queries to return the version and update information.

SERVERPROPERTY()

Let’s look at the SERVERPROPERTY() function first. Use the following query to return server version and patch level information.

SELECT 
     SERVERPROPERTY('ServerName') AS [Server Name] ,
     SERVERPROPERTY('Edition') AS [Edition], 
     SERVERPROPERTY('ProductLevel') AS [Product Level] ,
     SERVERPROPERTY('ProductUpdateLevel') AS [Update Level] ,
     SERVERPROPERTY('ProductVersion') AS [Version Number] ;

@@VERSION

You can also query the @@Version variable as shown below.

SELECT @@VERSION ; 

The results are shown below.

Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) 	Sep 23 2020 16:03:08 	Copyright (C) 2019 Microsoft Corporation	Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>

Both queries work and are quick to run. You can even run the queries in Central Management Server to collect information from multiple instances at once.

Using PowerShell and dbatools

If you’ve looked through our Script Library, you’ve probably noticed we’re fans of using PowerShell and dbatools.io to perform common administrative tasks in SQL Server. And as you’d expect, you can get the version information using these tools.

In the PowerShell script below, I’m using the Get-DbaInstanceProperty commandlet on the localhost to gather the information.

Get-DbaInstanceProperty -SqlInstance localhost -SqlCredential sa -InstanceProperty NetName, Edition, VersionString, ProductLevel, HostPlatform | Format-Table 

There’s much more information available from the commandlet. Just omit the InstanceProperty parameter to get a complete list.

Get-DbaInstanceProperty -SqlInstance localhost -SqlCredential sa |Format-Table

Want to work with The SERO Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? Or learn more about our SQL Server Assessments? It’s easy and there is no obligation. Schedule a call and let’s talk.