License increase

An alert will be thrown if a server host has an increase in the required number of SQL Server licenses.

The following query in the Performance Store database shows the license increase per host (first table) and before/after license count per SQL Server (second table):

select h.sample_time, h.host_name, h.sql_server_edition, sum(h.licenses_required) licenses_required
into #current
from dbo.host_license_info h
where h.sample_time in
(
 select top 1 h1.sample_time
 from dbo.host_license_info h1
 order by h1.sample_time desc
)
group by h.sample_time, h.host_name, h.sql_server_edition
select h.sample_time, h.host_name, h.sql_server_edition, sum(h.licenses_required) licenses_required
into #previous
from dbo.host_license_info h
where h.sample_time in
(
 select distinct top 2 h1.sample_time
 from dbo.host_license_info h1
 order by h1.sample_time desc
)
and h.sample_time not in
(
 select c.sample_time
 from #current c
)
group by h.sample_time, h.host_name, h.sql_server_edition
select c.sample_time sample_time_current, p.sample_time sample_time_previous, c.host_name, c.
sql_server_edition, c.licenses_required - isnull(p.licenses_required, 0) license_increase
into #increase
from #current c
left join #previous p on p.host_name = c.host_name and p.sql_server_edition = c.sql_server_edition
where c.licenses_required > p.licenses_required
or (p.licenses_required is null and c.licenses_required > 0)
drop table #current, #previous
select i.host_name, i.sql_server_edition, i.license_increase
from #increase i
select a.*
from dbo.all_servers_license_info a
inner join #increase i on i.host_name = a.host_name and i.sql_server_edition = a.sql_server_edition and (a.
sample_time = i.sample_time_current or a.sample_time = i.sample_time_previous)
order by a.server_name, a.sql_server_edition, a.sample_time desc
drop table #increase

The required number of SQL Server licenses for each SQL Server edition can be seen from the dbo.v_license_info view:

select l.*
from dbo.v_license_info l

To see details for the license calculations use the dbo.v_license_info_detailed view:

select l.*
from dbo.v_license_info_detailed l

The following query gives an idea of where to optimize licensing costs:

select *
from dbo.all_servers_license_info l
where l.sample_time in
(
 select top 1 a.sample_time
 from dbo.all_servers_license_info a
 order by a.sample_time desc
)
and (l.require_license_due_to_not_clean = 1 or l.require_license_due_to_mixed = 1 or l.require_license_due_to_prod_in_dev = 1 or l.require_license_due_to_ags_with_secondary_backup = 1 or l.require_license_due_to_ags_with_readable_secondary = 1)

For a description of require_license_due_to_not_clean and require_license_due_to_mixed please see: SQL Server with databases not in availability group

require_license_due_to_prod_in_dev means a non-Developer Edition license is used in a development environment, thus require a paid license.

The alert will be visible on the Latest stats dashboard.

To exclude specific servers from this alert, see how to use the dbo.team_alert_exclude table in Alerting.