Check SQL Server Version & license details

1461 views 14:05 0 Comments 12 March 2023
SQL

As a SQL Server DBA, I would rephrase the statement as follows:

While SQL Server does not keep track of license information, it can provide you with details about the installed edition and version. To ensure proper licensing, it is recommended that you contact Microsoft with the product ID. They can assist you in determining the number of licensed instances, cores, clients, and other relevant details. Additionally, they can clarify the licensing terms and help you achieve compliance. Ref. https://www.quora.com/How-do-I-check-if-the-MS-SQL-server-licenses-are-valid

Check Version or Edition of SQL Server

This blog provides three methods for determining a SQL Server instance license and identifying the installed edition or version on your system.

Check SQL Server Version Using SSMS

To use this method, launch SQL Server Management Studio (SSMS) and connect to your SQL Server. In Object Explorer, select the server and right-click on it. From the drop-down menu, select Properties.

Check SQL Server Version Using SQL Server Installation Center

To use this method, click on the Start button and locate the SQL Server folder. Within this folder, launch the SQL Server Installation Center. Tools > Installed SQL Server features discovery report.

Check SQL Server Version Using SQL Server Installation Center

Check SQL Server Version Using T-SQL

To begin, open SSMS and connect to your SQL Server instance by executing the following command:

select @@version
go

We can also extract licensing information from the SQL Server Error Log.

EXEC sp_readerrorlog @p1 = 0
                    ,@p2 = 1
                    ,@p3 = N'licensing'

TSQL to find server name, edition, logical CPU count, sockets, cores per socket, and the number of licenses required

In this below T-SQL script retrieves server information about the current SQL Server instance, including the server name, edition, logical CPU count, sockets, cores per socket, and the number of licenses required for the current edition of SQL Server.

The script uses system functions to retrieve server properties, including the @@SERVERNAME function to retrieve the server name, and the SERVERPROPERTY function to retrieve the edition of the SQL Server instance.

The script also uses the dm_os_sys_info dynamic management view to retrieve information about the server hardware, including the number of logical CPUs and hyperthreading ratio.

The CASE statement in the script evaluates the edition and hardware information to determine the number of licenses required for the current SQL Server edition. The number of licenses required varies based on the edition and the number of logical CPUs and sockets available on the server.

For example, the script determines that the Developer Edition, Express Edition, and Express Edition with Advanced Services require 0 licenses. Standard and Enterprise Editions require 2 licenses for servers with less than 4 cores. The script also checks the number of sockets and cores available on the server, and limits the licenses accordingly.

Finally, the script returns the server name, edition, logical CPU count, sockets, cores per socket, and the number of licenses required for the current edition of SQL Server.

SELECT  @@SERVERNAME as [Server Name],SERVERPROPERTY(N'Edition') AS Edition,cpu_count AS [Logical CPU Count],  
             cpu_count/hyperthread_ratio AS [Sockets],  hyperthread_ratio AS [Cores Per Socket], 
    CASE
       -- Developer Edition,Express Edition,Express Edition with Advanced Services = 0 licenses
       WHEN ((UPPER(Cast(SERVERPROPERTY(N'Edition') as sysname)) like N'%EXPRESS%') OR (UPPER(Cast(SERVERPROPERTY(N'Edition') as sysname)) like '%DEVELOPER%'))
            THEN 0 
       -- less then 4 cores = 2 licenses for Standard and Enterprise Edition
       WHEN ((cpu_count/2)<4) 
            THEN 2 
       -- Limited to 4 sockets & 24 cores for Standard Edition 
       WHEN ((cpu_count/hyperthread_ratio)> 4) AND UPPER(Cast(SERVERPROPERTY(N'Edition') as sysname)) like N'%STANDARD%'  AND 4*(hyperthread_ratio/2) <= 24
            THEN (4*(hyperthread_ratio))/2  
       WHEN ((cpu_count/hyperthread_ratio)<= 4) AND UPPER( Cast(SERVERPROPERTY(N'Edition') as sysname)) like N'%STANDARD%'  AND 4*(hyperthread_ratio/2) > 24
            THEN 24/2 
       -- Limited to 4 sockets & 16 cores for Web Edition 
       WHEN ((cpu_count/hyperthread_ratio)> 4) AND UPPER(Cast(SERVERPROPERTY(N'Edition') as sysname)) like '%WEB%'  AND 4*(hyperthread_ratio/2) <= 16
            THEN (4*(hyperthread_ratio))/2  
       WHEN ((cpu_count/hyperthread_ratio)<= 4) AND UPPER(Cast(SERVERPROPERTY(N'Edition') as sysname)) like '%WEB%'  AND 4*(hyperthread_ratio/2) > 16
            THEN 16/2 
       --Logical cores for Enterprise (unlimited)
       ELSE cpu_count/2   
     END as [Number of licenses Required]
FROM [sys].[dm_os_sys_info]

For more visit : https://learn.microsoft.com/en-us/troubleshoot/sql/releases/find-my-sql-version

Leave a Reply

Your email address will not be published. Required fields are marked *