
Applies to SQL Server 2019, 2022 and 2025. The script runs in Windows PowerShell 5.1 or PowerShell 7.
A SQL Server licensing report tells you which edition runs where, how many cores each instance sits on, and roughly how many core licences that adds up to. It’s the report nobody asks for until an audit letter or a renewal quote arrives, and then everybody wants it by Friday.
I run it weekly so the numbers are already there when that happens. It won’t replace a conversation with your licensing partner, but it means that conversation starts from facts.
What the report shows
One row per instance, per run:
- Edition and version. Enterprise, Standard, Developer, Express or Evaluation, and the build.
- Physical or virtual. From
sys.dm_os_sys_info.virtual_machine_type_desc. It changes the counting rules, so it matters more than anything else on the row. - Sockets, cores per socket and logical CPUs. What the instance can see.
- Schedulers online. How many of those CPUs SQL Server is actually using. Fewer than the CPU count means an edition limit or an affinity setting is holding it back.
- Install date. Taken from the creation date of the
NT AUTHORITY\SYSTEMlogin, which is made at setup. It’s what you need to work out when an Evaluation edition will stop. - Core licences needed. A first estimate, with a note explaining how it was worked out.
Why it matters
SQL Server is licensed per core, and the rules are easy to get wrong in both directions.
On a physical server you licence every physical core, with a minimum of four per processor. On a virtual machine licensed on its own, you licence every virtual core, with a minimum of four per VM. Hyperthreading doesn’t count on physical kit, but on a VM each vCPU is a core for licensing.
The expensive surprises are usually not the big production servers. They’re the forgotten ones: a Developer edition box that quietly became production, an Enterprise test server nobody has Software Assurance for, or a VM someone gave 16 vCPUs “just in case”. The report finds those.
It also works the other way. I’ve seen Standard edition instances on 32-core hosts where SQL Server can only use 24 of them. You pay for all 32 and get 24. Shrinking the VM saves money and costs nothing in performance.
The script
It reads the server list, connects to each instance with Windows Authentication, collects the CPU and edition facts, works out the estimate and stores a row in dbo.LicenceInventory in your DBA database. A server it can’t reach still gets a row marked FAILED, so a gap in the report never looks like a clean bill of health.
# Invoke-LicenceAudit.ps1
# Edition, CPU layout and a first estimate of the core licences each instance
# needs. Schedule it weekly, and run it before every true-up or renewal.
# The estimate is a starting point for the licensing conversation, not a ruling.
# ---- Settings ---------------------------------------------------------------
$ServerListPath = 'C:\DBA\Servers.txt'
$InventoryServer = 'SQLMON01'
$InventoryDatabase = 'DBA'
# -----------------------------------------------------------------------------
. "$PSScriptRoot\DbaReportHelpers.ps1"
$SqlQuery = @"
SELECT
CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)) AS InstanceName,
CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128)) AS Edition,
CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(32)) AS ProductVersion,
i.virtual_machine_type_desc AS VirtualMachineType,
i.socket_count AS Sockets,
i.cores_per_socket AS CoresPerSocket,
i.cpu_count AS LogicalCPUs,
(SELECT COUNT(*) FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE') AS SchedulersOnline,
CAST(i.physical_memory_kb / 1048576.0 AS DECIMAL(18,2)) AS RAM_GB,
-- NT AUTHORITY\SYSTEM is created at install time: a good proxy for the install date
(SELECT create_date FROM sys.server_principals
WHERE sid = 0x010100000000000512000000) AS InstallDate
FROM sys.dm_os_sys_info AS i;
"@
function Get-LicenceEstimate {
param($Row)
$Edition = [string]$Row.Edition
if ($Edition -match 'Developer|Express|Evaluation') {
$Note = if ($Edition -match 'Evaluation' -and $Row.InstallDate -isnot [System.DBNull]) {
'Evaluation: expires 180 days after ' + ([datetime]$Row.InstallDate).ToString('yyyy-MM-dd')
} elseif ($Edition -match 'Evaluation') {
'Evaluation: expires 180 days after install'
} else { 'Free edition: check it is not serving production' }
return @{ Licences = 0; Note = $Note }
}
if ([string]$Row.VirtualMachineType -ne 'NONE') {
# Licensing by virtual machine: every vCPU, minimum 4 per VM
$Licences = [math]::Max(4, [int]$Row.LogicalCPUs)
$Note = 'Virtual: per-VM core count (or covered by host licensing with SA)'
}
else {
# Physical: every physical core, minimum 4 per processor
$Licences = [int]$Row.Sockets * [math]::Max(4, [int]$Row.CoresPerSocket)
$Note = 'Physical: all physical cores'
}
if ($Edition -match 'Enterprise' -and $Edition -notmatch 'Core-based') {
$Note += '; Server+CAL Enterprise: limited to 20 cores'
}
if ($Edition -match 'Standard' -and [int]$Row.LogicalCPUs -gt 24) {
$Note += '; Standard caps the cores it uses (24, or 32 from SQL Server 2025), but you still licence them all'
}
if ([int]$Row.SchedulersOnline -lt [int]$Row.LogicalCPUs) {
$Note += "; only $($Row.SchedulersOnline) of $($Row.LogicalCPUs) CPUs in use by SQL Server"
}
@{ Licences = $Licences; Note = $Note }
}
$Servers = Get-ServerList -Path $ServerListPath
$Repo = Open-SqlConnection -Server $InventoryServer -Database $InventoryDatabase
try {
Invoke-SqlNonQuery $Repo @"
IF OBJECT_ID('dbo.LicenceInventory', 'U') IS NULL
CREATE TABLE dbo.LicenceInventory
(
CaptureDate DATETIME2(0) NOT NULL
CONSTRAINT DF_LicenceInventory_CaptureDate DEFAULT SYSDATETIME(),
ServerName NVARCHAR(128) NOT NULL,
InstanceName NVARCHAR(128) NULL,
Edition NVARCHAR(128) NULL,
ProductVersion NVARCHAR(32) NULL,
VirtualMachineType NVARCHAR(60) NULL,
Sockets INT NULL,
CoresPerSocket INT NULL,
LogicalCPUs INT NULL,
SchedulersOnline INT NULL,
RAM_GB DECIMAL(18,2) NULL,
InstallDate DATETIME2(0) NULL,
CoreLicencesNeeded INT NULL,
LicenceNote NVARCHAR(400) NULL,
CollectionStatus VARCHAR(20) NOT NULL,
ErrorMessage NVARCHAR(MAX) NULL
);
"@
foreach ($Server in $Servers) {
$Row = [ordered]@{ ServerName = $Server; CollectionStatus = 'SUCCESS' }
try {
$Source = Open-SqlConnection -Server $Server
try {
$Sql = (Invoke-SqlQuery $Source $SqlQuery).Rows[0]
}
finally {
$Source.Close()
}
foreach ($Column in $Sql.Table.Columns.ColumnName) {
$Row[$Column] = $Sql[$Column]
}
$Estimate = Get-LicenceEstimate $Sql
$Row.CoreLicencesNeeded = $Estimate.Licences
$Row.LicenceNote = $Estimate.Note
Write-Host "$Server : $($Row.Edition) - $($Row.CoreLicencesNeeded) core licences"
}
catch {
$Row.CollectionStatus = 'FAILED'
$Row.ErrorMessage = $_.Exception.Message
Write-Warning "$Server : $($Row.ErrorMessage)"
}
Add-InventoryRow -Connection $Repo -Table 'dbo.LicenceInventory' -Row $Row
}
}
finally {
$Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script)
# DbaReportHelpers.ps1
# Shared helpers for the auditing scripts. Save it next to them and dot-source it:
# . "$PSScriptRoot\DbaReportHelpers.ps1"
# Windows Authentication throughout: run the scripts as a service account that
# can reach every server. No passwords live in any of these files.
function Get-ServerList {
# One server per line. Blank lines and anything after # are ignored,
# and duplicates (in any case) are removed.
param([Parameter(Mandatory)][string]$Path)
Get-Content -Path $Path |
ForEach-Object { ($_ -split '#')[0].Trim() } |
Where-Object { $_ } |
Sort-Object -Unique
}
function Get-ComputerName {
# SERVER\INSTANCE -> SERVER, for the Windows calls (CIM, ping, remoting)
param([Parameter(Mandatory)][string]$Server)
($Server -split '\\')[0]
}
function Open-SqlConnection {
param(
[Parameter(Mandatory)][string]$Server,
[string]$Database = 'master'
)
$Conn = New-Object System.Data.SqlClient.SqlConnection
$Conn.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;" +
"TrustServerCertificate=True;Connect Timeout=15;Application Name=DBA reports"
$Conn.Open()
$Conn
}
function Invoke-SqlQuery {
# Runs a query and returns a DataTable (the leading comma stops PowerShell unrolling it).
param($Connection, [string]$Sql, [hashtable]$Parameters = @{})
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 300
foreach ($Name in $Parameters.Keys) {
$null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
}
$Table = New-Object System.Data.DataTable
$null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Table)
, $Table
}
function Invoke-SqlNonQuery {
param($Connection, [string]$Sql)
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 300
$null = $Cmd.ExecuteNonQuery()
}
function Add-InventoryRow {
# Inserts one row from an ordered hashtable of column = value.
# Every value goes in as a parameter, never pasted into the SQL text.
param(
$Connection,
[Parameter(Mandatory)][string]$Table,
[Parameter(Mandatory)][System.Collections.IDictionary]$Row
)
$Columns = @($Row.Keys)
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = "INSERT INTO $Table (" + ($Columns -join ', ') + ') VALUES (' +
(($Columns | ForEach-Object { "@$_" }) -join ', ') + ');'
foreach ($Column in $Columns) {
$Value = $Row[$Column]
if ($null -eq $Value -or $Value -is [System.DBNull] -or
($Value -is [string] -and $Value.Trim() -eq '')) {
$Value = [System.DBNull]::Value
}
$null = $Cmd.Parameters.AddWithValue("@$Column", $Value)
}
$null = $Cmd.ExecuteNonQuery()
}
The report queries
The summary query sets the licences you own against the cores in use, from the latest run. Put your Enterprise and Standard core counts in the two variables at the top. It shows the cores spare for each edition and flags anything short or with under 10% left. That’s the number for the renewal conversation.
Your agreements beat the estimate, so the query reads dbo.LicenceOverride first. It creates the table if it isn’t there. Put a row in it for any server where you know better: 0 for a VM covered by a host licence or one being decommissioned, or the real core count where the estimate is wrong. It also counts several instances on one server once, against the highest edition on that server, and flags any Enterprise installed as Server+CAL.
/*
LicenceSummary.sql - run in the DBA database, after Invoke-LicenceAudit.ps1.
Core licences owned against core licences in use, from the latest run.
Cores per server, first match wins:
1. dbo.LicenceOverride (your own figure: 0 for a server covered by a host
licence or being decommissioned, or whatever your agreement says)
2. CoreLicencesNeeded from the licence audit
Several instances on one server share one licence, counted once against the
highest edition on that server.
*/
USE DBA;
IF OBJECT_ID(N'dbo.LicenceOverride', N'U') IS NULL
CREATE TABLE dbo.LicenceOverride
(
ServerName NVARCHAR(128) NOT NULL CONSTRAINT PK_LicenceOverride PRIMARY KEY,
LicensedCores INT NOT NULL, -- 0 = don't count
Note NVARCHAR(200) NULL
);
DECLARE @EnterpriseOwned INT = 0; -- Enterprise core licences you own
DECLARE @StandardOwned INT = 0; -- Standard core licences you own
IF OBJECT_ID(N'tempdb..#Lic') IS NOT NULL DROP TABLE #Lic;
WITH Latest AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY CaptureDate DESC) AS RN
FROM dbo.LicenceInventory
WHERE CollectionStatus = 'SUCCESS'
)
SELECT
L.ServerName,
LEFT(L.ServerName, CHARINDEX('\', L.ServerName + '\') - 1) AS HostName,
L.Edition,
E.LicenceType,
CASE WHEN E.LicenceType = 'Enterprise' AND L.Edition NOT LIKE '%Core-based%' THEN 1 ELSE 0 END AS IsServerCal,
CASE WHEN E.LicenceType = 'Free' THEN 0
ELSE COALESCE(O.LicensedCores, L.CoreLicencesNeeded) END AS LicensedCores,
CASE WHEN O.ServerName IS NOT NULL THEN 'Override: ' + ISNULL(O.Note, '')
ELSE L.LicenceNote END AS Note
INTO #Lic
FROM Latest AS L
CROSS APPLY
(
SELECT CASE WHEN L.Edition LIKE '%Evaluation%'
OR L.Edition LIKE 'Developer%'
OR L.Edition LIKE 'Express%' THEN 'Free'
WHEN L.Edition LIKE 'Enterprise%' THEN 'Enterprise'
WHEN L.Edition LIKE 'Standard%' THEN 'Standard'
ELSE 'Other' END AS LicenceType
) AS E
LEFT JOIN dbo.LicenceOverride AS O ON O.ServerName = L.ServerName
WHERE L.RN = 1;
-- Second and later instances on the same server: already counted
WITH Hosts AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY HostName
ORDER BY CASE LicenceType WHEN 'Enterprise' THEN 1 ELSE 2 END,
LicensedCores DESC, ServerName) AS HostRN
FROM #Lic
WHERE LicensedCores > 0
)
UPDATE Hosts
SET LicensedCores = 0,
Note = ISNULL(Note + '; ', '') + 'Shares a licence with another instance on this server'
WHERE HostRN > 1;
/* 1. Owned against in use: the figure for the renewal ----------------------- */
WITH Used AS
(
SELECT LicenceType,
SUM(LicensedCores) AS CoresInUse,
SUM(CASE WHEN LicensedCores > 0 THEN 1 ELSE 0 END) AS Servers,
SUM(IsServerCal) AS InstalledAsServerCal
FROM #Lic
WHERE LicenceType IN ('Enterprise', 'Standard')
GROUP BY LicenceType
),
Owned AS
(
SELECT 'Enterprise' AS LicenceType, @EnterpriseOwned AS CoresOwned
UNION ALL
SELECT 'Standard', @StandardOwned
)
SELECT
O.LicenceType,
O.CoresOwned,
ISNULL(U.CoresInUse, 0) AS CoresInUse,
O.CoresOwned - ISNULL(U.CoresInUse, 0) AS CoresSpare,
ISNULL(U.Servers, 0) AS Servers,
ISNULL(U.InstalledAsServerCal, 0) AS InstalledAsServerCal,
CASE WHEN O.CoresOwned < ISNULL(U.CoresInUse, 0) THEN 'SHORT'
WHEN O.CoresOwned - ISNULL(U.CoresInUse, 0) < O.CoresOwned * 0.1 THEN 'UNDER 10% SPARE'
ELSE 'OK' END AS Position
FROM Owned AS O
LEFT JOIN Used AS U ON U.LicenceType = O.LicenceType;
/* 2. Every instance and what it counts for --------------------------------- */
SELECT ServerName, Edition, LicenceType, LicensedCores,
CASE WHEN IsServerCal = 1 THEN 'Installed as Server+CAL: check which licence covers it; ' ELSE '' END
+ ISNULL(Note, '') AS Note
FROM #Lic
ORDER BY CASE LicenceType WHEN 'Enterprise' THEN 1 WHEN 'Standard' THEN 2 WHEN 'Other' THEN 3 ELSE 4 END,
ServerName;
DROP TABLE #Lic;
The second query runs on each instance and lists features stored in each database that tie it to an edition. Anything it returns stops that database being restored on a lower edition, which is exactly what you need to know before anyone suggests “just move it to Standard”.
-- LicenceReport.sql
-- The licence count itself (owned against in use) is in LicenceSummary.sql.
--
-- Edition-specific features stored in each database (run on each instance).
-- Anything listed here stops you restoring that database on a lower edition,
-- so check it before moving a server from Enterprise to Standard.
DECLARE @Sql NVARCHAR(MAX) = N'';
SELECT @Sql += N' UNION ALL SELECT ' + QUOTENAME(name, '''') + N' AS DatabaseName, feature_name'
+ N' FROM ' + QUOTENAME(name) + N'.sys.dm_db_persisted_sku_features'
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND database_id > 4
AND HAS_DBACCESS(name) = 1;
IF @Sql <> N''
BEGIN
SET @Sql = STUFF(@Sql, 1, 11, N'') + N' ORDER BY DatabaseName;'; -- drop the first ' UNION ALL '
EXEC sys.sp_executesql @Sql;
END;
How to read it
- Free editions in production. Developer and Express show 0 licences. Check every one of them is genuinely dev, test or a tiny app. Developer edition is not licensed for production use at all.
- Evaluation edition. It stops after 180 days. The note gives the install date, so put the expiry in the diary now.
- Enterprise without “Core-based” in the edition name. That’s Server + CAL licensing, which has a 20-core limit per instance. If the server has more, SQL Server simply ignores them.
- Schedulers online lower than CPUs. Either the edition cap or affinity. Either way you may be licensing cores nobody uses.
Watch out for
- It’s an estimate. The script can’t see your agreements. Licensing a whole virtual host with Enterprise and Software Assurance, for example, covers every VM on it, and the per-VM numbers no longer add up to anything. Record those VMs in
dbo.LicenceOverridewith 0 cores. - Passive replicas. With Software Assurance, a passive secondary for high availability and one for disaster recovery are usually covered. The report counts every instance it finds, so mark those up by hand. A readable secondary, or one that takes backups or runs jobs, isn’t passive.
- Licences follow the operating system environment, not the instance. Three instances on one VM need one set of licences, not three. The summary query groups them by the server part of the name, so it only works if every instance is listed as
SERVER\INSTANCEunder the same server name. - Old hosts with hyperthreading on VMs. A 4-core physical box presenting 8 vCPUs to one VM means 8 core licences if you licence by VM.
- Microsoft changes the rules. SQL Server 2025 raised the Standard edition limit to 32 cores. Check the current licensing guide before you quote any number to finance.
Where it fits
This is part of my Auditing section, alongside the server inventory, availability, disk space, database, backup and SSIS reports. They all share the same server list, the same DBA database and the same helper file. The SQL Server DBA Handbook covers editions and setup in more depth.
Microsoft’s Compute capacity limits by edition of SQL Server page has the current core and socket limits. The licensing guide itself comes from your Microsoft licensing partner.
The other Auditing reports: Availability: ping and response time | Disk space alerts | Server inventory | Database inventory | Backups | SSIS errors
Clive Roberts DBA
2 thoughts on “SQL Server Licensing Report”
Comments are closed.