How to Find the OS Version on your SQL Server Machine - Single vs Many Machines (article on SQLNetHub)

How to Find the OS Version of your SQL Server Machine – Single vs Many Machines

by

Finding the Windows OS version of a single SQL Server machine is easy. There many ways you can do it. However, what about when you work with hundreds of SQL Server instances? How can you efficiently generate a report with the OS versions? This article discusses some of the methods you can use to find the OS version of your SQL Server machine.

These methods take into consideration various scenarios:

  • Working with just a single SQL Server instance vs working with hundreds of SQL Server instances
  • Working with SQL Server on Windows vs working with mixed SQL Server Instances on Windows and Linux
  • Working with unsupported SQL Server instances (i.e. SQL Server 2005/2008/2008R2)

 

A Note About SSMS and support for Linux

In many of the methods I list below, you will see that I’m using SSMS for SQL Servers on both Windows and Linux. That’s OK because SSMS, even though it is a Windows-based tool, it can also connect to remote Linux SQL Server instances (I really like this since SSMS is my favorite tool 🙂

Great! Now let’s talk about the different methods you can use, in order to find the OS version of your SQL Server machines.

 

Method 1 – Via SSMS

Within SSMS, while you are connected to the SQL Server instance, you right-click on the instance name, navigate to “Properties” and in the “General” Tab (that’s the first tab that opens by default), you can find the OS version in the “Operating System” field.

Applicable in: All active SQL Server versions
Supported OS: Windows, Linux

 

Method 2 – sys.dm_os_windows_info

A second method is to run the below T-SQL statement that uses the sys.dm_os_windows_info dmv:

SELECT windows_release,windows_service_pack_level FROM sys.dm_os_windows_info;
GO

Applicable in: SQL Server 2008 R2 and later
Supported OS: Windows

 

Method 3: sys.dm_os_host_info

Another method is to run the below T-SQL statement that use the sys.dm_os_host_info dmv:

SELECT host_platform, host_distribution, host_release
FROM sys.dm_os_host_info;
GO

Applicable in: SQL Server 2017 and later
Supported OS: Windows, Linux

 

Method 4: winver

Another method of getting the OS version of your SQL Server machine, is the obvious: remotely connect to the database server, and run the “winver” command to get OS version info such as: version, build, etc.

Supported OS: Windows

 

Finding the OS version of Many SQL Server Instances

What about however, if you administer hundreds of SQL Server instances and machines, and you need an efficient way of compiling a report with their underlying Operating System versions?

The easiest approach for such cases, is to have a group of registered SQL servers in SSMS that contains all these servers, then right-click on the group and run one of the below T-SQL statements for all registered servers, based on each case:

Query 1: When your Servers Group Contain Unsupported SQL Server 2005/2008/2008R2 Instances – Windows

SELECT SUBSTRING(@@VERSION,CHARINDEX('Windows',@@VERSION,0),100) AS OSVersion;
GO

Note: Supports Windows Machines

 

Query 2: When your Servers Group Contain Only SQL Server 2017 and Later – Windows and Linux

SELECT host_platform, host_distribution, host_release FROM sys.dm_os_host_info;
GO

Note: Supports both Windows and Linux machines

 

The Best Option in the Case of Multiple Windows and Linux DB Servers

In case you have many Windows and Linux based SQL Server instances, including unsupported ones, I would suggest creating two groups of registered servers in SSMS, one for SQL Server instances up to SQL Server 2016 (Group 1), and another one for SQL Server 2017 instances and later (Group 2). Then, you can run Query 1 for group 1, Query 2 for Group 2, and finally compile the 2 reports into one.

 

Why Not Just @@version?

Someone might argue “why not just running @@version” and that would be a good and logical argument. However, if you compare the just using the @@version global variable against all the above methods I listed in the article, you will note the difference:

@@version returns not only the OS version but also many other information. 

To this end, if you end up using @@version, that’s OK with me, no problem! However, note that you will have to apply some serious text parsing on the results that you get, in order to get a final report with just the OS versions 🙂

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips”
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

Featured Online Courses:

 

Related SQL Server Administration Articles:

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 13,202