Checking your SQL Server versions is a thing we need to do quite often as Database Administrators (DBAs). We know how important it is to keep our software up to date. This post is here to help you check the version of your running SQL Server instances, with added tips to help you understand what’s needed.
You can have multiple versions of SQL Server running on the same host. Whether that’s multi-tenant (many SQL instances), or left-over old SQL installations. We should aim to keep them all with the latest SQL CU, or at minimum GDR. For more information on the differences of each, check out my other post, Keeping SQL Server and Critical Systems Up to Date.
Regular monthly patching is a critical practice, especially for Windows servers, to ensure a secure and issue-free database environment. At minimum, we really need to make sure we’re operating within supported SQL Server Versions & Extended Support Dates:
SQL Server 2014 – Currently Supported, End Date: >> JULY 9, 2024 <<
SQL Server 2016 – Currently Supported, End Date: JULY 14, 2026
SQL Server 2017 – Currently Supported, End Date: 2027
SQL Server 2019 – Currently Supported, End Date: 2030
SQL Server 2022 – Currently Supported, End Date: 2033
If you’re still running with SQL Server 2012 or below… what can I say. Just try upgrade it, do your best, because it’s worth it.
The reason I say this is because if you have a critical issue with your server, you can’t ask for help. A SQL Consultant might be able to save the day with some issues, however you’re really making life more difficult in the long run by doing nothing.
Staying Proactive with Patching
Our commitment to security aligns with Patch Tuesday schedules, which is the second Tuesday of every Month, Microsoft releases their updates. It’s important for us to proactively check for updates to keep servers up to date.
Having a proactive approach not only ensures compatibility with the latest technologies but also guarantees that any bugs or issues present in previous versions have been addressed. By applying our cumulative updates (CUs), we have an added advantage when troubleshooting complex issues with Microsoft support, they may request you to be on an updated CU to rule out any potential compatibility concerns. For the latest information on new SQL patches, refer to the Microsoft Docs link for SQL Server Updates and Version History.
Checking SQL Server Versions
There’s many ways to check our running SQL Server Version. I’m going to run through this from most preferred to least, for your convenience.
Example 1: SSMS Object Explorer
The quickest way to obtain the version number of your SQL Server instance is within SQL Server Management Studio (SSMS).
When we connect to a SQL Server we see the version number next to the server name as displayed in the example below, 16.0.4100.1:
As well as this, we can right-click the server you are connected to, click Properties, and we can view the SQL Server version number including other useful information.
Example 2: SQL Query to Get SQL Server Version
Another quick way to retrieve the SQL Server version number is using a SQL query.
In the example below we are querying @@VERSION and SERVERPROPERTY, 2 different ways to show SQL version info.
-- Query 1: Using @@version to Retrieve SQL Server Version Information
SELECT @@VERSION AS 'SQL_Server_Version';
-- Query 2: Using SERVERPROPERTY to Extract Detailed SQL Server Information
SELECT
SERVERPROPERTY('productversion') AS 'Product_Version',
SERVERPROPERTY('productlevel') AS 'Product_Level',
SERVERPROPERTY('edition') AS 'Edition_Info';
Example 3: Check the SQL Server Logs for Version Info
To check a SQL Server Version via File Explorer, we can find the appropriate SQL Log files and check for entries resembling the version format (16.0.x, 15.x, 14.x, …).
To identify the installed SQL Server version, examine the following files:
C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\Log\Summary.txt
This file provides a summary log for the SQL Server installation. The “160” in the file path corresponds to SQL Server 2022. For other versions, replace “160” with the respective version number (e.g., “150” for SQL Server 2019, “140” for SQL Server 2017).
C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\Error.log
The “16” in the file path pertains to the SQL Server instance version. For SQL Server 2022, it represents the major version. Similarly, for other versions, replace “16” with the respective major version number. This log file contains error information and is instrumental for troubleshooting and version identification.
Example 4: Run the SQL Server Features Discovery Report for Version Info
The SQL Server Features Discovery Report, while slower, offers more comprehensive info into your installed features and versions.
To run the SQL Server Feature Discovery Report, start by accessing the SQL Server Installation Center. If SQL Server is already installed on your machine, the Installation Center should be available in the Start menu under the SQL Server folder. Once the Installation Center is launched, navigate to the “Tools” section, and select “Feature Discovery Report.”.
In the example above, the SQL Server Engine is the sole feature installed. Typically, a SQL Server installation will include many features/services, like Replication, Full-Text Analysis Services, Integration Services, Reporting Services, and more.
Conclusion
Now that you’ve successfully identified the version of your SQL Server instance, the next steps revolve around ensuring its currency and security. For routine updates, especially for non-critical servers, SQL Server patching can be handled by your regular Windows Updates maintenance window.
In cases where a more immediate or manual update is necessary, visiting the Microsoft site to download the latest MSSQL version directly.
For business critical servers, planning and arranging downtime for the upgrade process is more important. If you’re in more an Enterprise environment, you’ll be running on, or considering High Available (HA) infrastructure using Always On Availability Groups (AAGs).
Leave a Reply