How to check your SQL Server version


Microsoft SQL Server is one of the world’s most popular database platforms. According to some estimates as many as one in three databases runs on SQL Server, alongside competitors such as Oracle, MySQL, and PostgreSQL.

However, SQL Server is not a single product. Since its original launch in 1989, there have been more than 20 major releases of SQL Server. At the time of writing four different releases are still supported, namely: 

SQL Server 2016;

SQL Server 2017;

SQL Server 2019; and 

SQL Server 2022. 

All supported releases of SQL Server can handle everyday database tasks, but there are several reasons why it’s important to know which specific version you’re using.

How to check your SQL Server version

Support lifecycle

If you’re planning a deployment, or scoping out future database projects, you need to know that your SQL Server installation will be supported for the required duration, or you might miss out on important security or functionality updates.

Each version of SQL Server receives around five to six years of mainstream support after its release, meaning it may receive new features and performance improvements alongside security updates. After this, there’s a five-year period of extended support when no new features are added, but security updates and paid technical support are still available. Once extended support ends, no more assistance or updates will be provided, even if critical security vulnerabilities are discovered.

Here are the key support dates for the current major editions of SQL Server:

Swipe to scroll horizontally
Version Mainstream support until Extended support until
SQL Server 2016 13 July 2021 14 July 2026
SQL Server 2017 11 October 2022 12 October 2027
SQL Server 2019 28 February 2025 8 January 2030
SQL Server 2022 11 January 2028 11 January 2033

As you can see, the 2016 and 2017 editions of SQL Server are already out of mainstream support and will be entirely unsupported within three years. For this reason, it’s important to keep track of the SQL Server version, whether you’re beginning a new project or maintaining an existing database. 

Feature availability

Each subsequent release of SQL Server brings new capabilities that weren’t available in previous editions. For example, SQL Server 2016 introduced enhanced encryption capabilities, for working with sensitive information such as financial data; it also brought JSON support, making it easier to programmatically extract and process data. 

New features in SQL Server 2017 included new string manipulation functions and automatic performance tuning, while SQL Server 2019 brought support for big data clusters and improvements for parallel processing. The latest SQL Server 2022 release has many features designed to work better with Azure and other cloud data sources.

If you’re building a new database application, you’ll want to know which version of SQL Server you’re using, to ensure you have access to all the capabilities you might need.

Security and feature updates

As well as knowing which major SQL Server edition you’re using, it’s important to check the minor version number (the digits after the first decimal point) to ensure that the latest available updates to security and functionality have been installed. 

An up-to-date installation might have two different version numbers, depending on how server updates have been configured. If the server is set to receive GDR (General Distribution Release) updates then only security fixes will be added to the original installation, and the version number will reflect that. If it’s on the CU (Cumulative Update) branch it will also receive new features and usability enhancements, and will usually have a higher version number.

You can check the latest GDR and CU version numbers on Microsoft’s SQL Server update page. At the time of writing the latest updates for the past three releases are:

Swipe to scroll horizontally
Version Latest GDR update Latest CU update
SQL Server 2017 14.0.2056.2 14.0.3456.2
SQL Server 2019 15.0.2116.2 15.0.4385.2
SQL Server 2022 16.0.1121.4 16.0.4135.4

SQL Server 2016 and earlier major releases were updated with milestone Service Packs as well as individual GDR and CU updates; see the Microsoft website for details of the latest version numbers for different Service Pack levels.

SQL Server version number

The moment you’ve been waiting for: how to check your SQL Server version number.

There are numerous methods you can use to check the major and minor version numbers of a SQL Server instance. Here are four quick ways to find the information:

1. Use SQL Server Management Studio

Microsoft’s free SQL Server Management Studio tool can provide extensive information about a SQL Server installation. After you’ve connected the application to your SQL Server instance, check the Object Explorer window and you should see the server’s version number shown in brackets after its name. If you can’t see the full version number, or want more information, right-click on the server name and select Properties to open a window with many more details about the version and capabilities of the server.

2. Query the server directly

You can query a SQL Server’s version number directly from a Windows command prompt. To connect to the server, enter sqlcmd -S servername\instancename, replacing server\instancename with the appropriate details for your server. If your connection is successful, you should now see a “1>” prompt: enter “select @@version”, then at the “2>” prompt, enter “go”. This should return a copyright statement which includes the server version number and build date.

3. Examine the executable

If you have desktop access to the computer your SQL Server instance is running on, you can check the version number by inspecting the executable file that runs the server. By default you’ll find this file in a folder called C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn (the precise path will be slightly different for older versions of SQL Server); navigate here in the Windows File Explorer, then find the file called sqlservr.exe, right-click on it and select “Properties”. In the Properties window that opens, click on the Details tab to see information about the server, including its version number.

4. Check the log files

You can also find your SQL Server version number by inspecting its logs. By default, these are stored in a folder called C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log (again, this will be slightly different for older versions of SQL Server). Note that this folder is protected, so you’ll need to either edit its security settings to give yourself access or navigate to it from an elevated Command Prompt.

Once you’ve got access to the Log folder, open the text file called ERRORLOG, or any older file with this name and a numbered extension. The first line of each log file contains the version number of the server.




Source link

Exit mobile version