As Database Administrators (DBAs), safeguarding the security of our SQL Server instances is paramount. Regular monthly patching is a critical practice, especially for Windows servers, to ensure a secure database environment. In this guide, we’ll explore methods on how to check SQL Server version numbers, while underlining the crucial role of staying current with monthly patching to ensure a secure database environment.
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
See MS Docs: Support Lifecycle End Dates for more information.
It’s essential to note that while SQL Server 2014 is still supported until July 9, 2024, it is reaching the end of its extended support period. DBAs are encouraged to plan and transition to a more recent version to continue receiving security updates and support beyond this date. Stay vigilant and ensure that your SQL Server instances align with the latest supported versions for enhanced security and performance.
Staying Proactive with Patching
Our commitment to security aligns with Patch Tuesday schedules, reinforcing the importance of proactive measures for system security. While Microsoft doesn’t release a new SQL Server update or patch every month, it’s essential to proactively check for updates to keep your 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
To check your SQL Server Version, we have various methods available to help identify this information, including:
– SQL Server Management Studio (SSMS): Object Explorer and Options
– SQL Query to Get SQL Server Version
– Check the SQL Server Error Log for Version
– Run the SQL Server Features Discovery Report for Version
SSMS Object Explorer and Options
To quickly obtain the version number of your SQL Server in SSMS, it’s as easy as looking at the Object Explorer when connected to a server. We can see the version number displayed next to the server name.
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.
My local development SQL Server instance is fully up to date running the latest MSSQL 2022 CU. Cross check this number with MS Docs as linked above to verify that you are running on the latest version.
SQL Query to Get SQL Server Version
Another quick way to retreive the SQL Server Version number is using T-SQL:
-- 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';
Query 1 (@@VERSION): This query retrieves comprehensive information about the SQL Server version. The result provides details on the major version, release, and build.
Query 2 (SERVERPROPERTY): This query uses the SERVERPROPERTY function to extract specific details about the SQL Server installation. It includes the product version, product level (e.g., RTM or Service Pack), and edition information. This concise query offers a detailed breakdown of essential server properties.
Check the SQL Server Error Log for Version
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.
Run the SQL Server Features Discovery Report for Version
The SQL Server Features Discovery Report, while slower, offers detailed insights into installed features and versions. Executing it in SQL Server Management Studio provides valuable information about your SQL Server instance’s 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 comprehensive SQL Server installation includes a multitude of features beyond the Database Engine, encompassing functionalities like Replication, Full-Text and Semantic Extractions for Search, Analysis Services, Integration Services, Reporting Services, and more.
Conclusion
Now that you’ve successfully identified the version of your SQL Server instance, the next crucial 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. Leveraging Monthly Windows patching schedules proves effective, aligning with Patch Tuesday schedules where new Microsoft patches are released every second Tuesday of the month.
In cases where a more immediate or manual update is necessary, visiting the Microsoft site for SQL Server allows you to download and install the latest version directly. Refer to the Microsoft SQL Server Builds Page to confirm that you are acquiring and implementing the most recent updates.
For critical servers, planning and arranging downtime for the upgrade process becomes imperative. This ensures a smooth transition without compromising essential operations. Staying on point with this, whether through routine updates or immediate installations, is key to maintaining a secure, efficient, and up-to-date SQL Server environment.