Microsoft SQL Server Optimization Report

IT Asset Management (Cloud)
For SQL Server, Microsoft offers two key alternatives for licensing:
  • SQL Server Enterprise, which covers an unlimited number of virtual machines (VMs) with installations of any edition of Microsoft SQL Server on the host, with capping applied according to the number of cores available in each host (and consumption is summed at the host level, so that capping can be applied)
  • SQL Server Standard, which must be applied separated to each VM with an installation of Microsoft SQL Server Standard Edition, with a minimum of four cores per VM.
There are no mobility rules for SQL Server licensing. If the licenses are covered with Software Assurance, you may license the current 'snapshot' position.
The report includes all consuming devices:
  • Virtual hosts where installed VMs are running Microsoft SQL Server
  • Orphan VMs (those where the host server cannot be identified)
  • Stand-alone computers.
The optimization recommended in the report is based on the working assumption that many administrators have taken the easier path, of paying for a SQL Server Enterprise license, rather than assessing each installation case-by-case, as this report now does for you. Therefore the report calculates consumption for three separate scenarios:
  • Covering all devices (including VMs) with SQL Server Enterprise license, applying capping to limit consumption to the number of cores available on the host (so that time-sharing between VMs does not increase consumption beyond the capacity of the host)
  • Where all VMs in a cluster are installed with Microsoft SQL Server Standard Edition, covering all those VMs with SQL Server Standard license
  • Licensing a host where each VM is running Microsoft SQL Server Standard edition with the SQL Server Standard license, and hosts where VMs are running Microsoft SQL Server Enterprise Edition with the SQL Server Enterprise license.
The report then recommends the one of those scenarios that yields the lowest cost.
Tip: This report may show consumption figures for SQL Server Enterprise licenses that are different from the consumption displayed within IT Asset Management. This is because, for any host that has a mix of Standard and Enterprise editions of Microsoft SQL Server running on its VMs, the license compliance calculations take the 'safe' route of applying a SQL Server Enterprise license to the entire host. This report may offer a more optimized solution.

Generating the report

Note: This report is scoped to the data that each operator is entitled to see, according to their access rights. While an administrator can see all available licenses, clusters, consumption, and optimizations, another operator who has access rights restricted to EMEA sees only those elements linked to the EMEA location, and to any of its child locations.
  1. Go to the Microsoft SQL Server Optimization page (Reporting > License Reports > Microsoft SQL Server Optimization).
  2. Click Run report to display the results for all installations of Microsoft SQL Server running in clusters or on stand-alone devices.
All results are shown as of the latest inventory import and license reconciliation process, for which the date and time are shown in the right-hand end of the title bar, against Reconciled:

Reading the report

The following columns (listed alphabetically) are available.
Note: To save space, in several of the following column names these abbreviations are used:
  • (SQL Standard) for the Microsoft SQL Server Standard license
  • (SQL Enterprise) for the Microsoft SQL Server Enterprise license.
Column name Description
Cluster name

The path in the virtualization hierarchy to the cluster (in the form of domain/clustername). Cluster names are not forced to be unique, although giving them unique names is best practice. If you need to differentiate between (for example) two clusters with the same name, check the hosts and instances.
Comment on optimization

For rows where the Device type is VM Host, this comment compares the costs of the two possible licensing approaches for the cluster where this host operates, identifying the cheaper of the two SQL Server license options. (The column remains blank for other device types, or where the Optimal license cannot be identified.)
Consumed cores

The licensed cored consumed by the virtual host, or orphan VM, or standalone device shown in this row. This is the consumption that will result if this device is covered by the Optimal license.

Consuming installations
A comma-separated list of the VMs (or other devices) consuming from the license in this row, along with:
  • The associated core count
  • The device role
  • The version (release number) and edition (Enterprise or Standard) of Microsoft SQL Server installed on each one.
Cores

The total number of processor cores available in a physical inventory device; or the number of cores assigned to an inventory device that is a virtual machine.
Cost/core (SQL Enterprise) (currency)
The cost per device core under a SQL Server Enterprise license. This is the unit cost used for calculating values in this report. The report finds the SQL Server Enterprise license with the largest number of associated purchases (see Reference license (SQL Enterprise)), and then this value is taken from the first available of:
  • The Amount field on the Financial tab of the license properties
  • The Override unit price shown at the top of the Purchases tab of the properties of the reference license
  • The most recent Unit price in software purchases attached to the reference license
  • The default value of 5,000 (a hard-coded value based on the US$ list price per core).
Important: USD currency must be added as a supported currency to your tenant. Note there is no requirement to make USD your default currency. If USD is not added to your list of supported currencies, returned VM host data may contain discrepancies such as missing VM hosts and inaccurate price related data. To add USD currency, navigate to Administration > IT Asset Management Settings > Defaults > Currency Settings. On this page select Add a Currency and then United States Dollar (USD) from the Currency drop down list. When finished select Add.
Cost/core (SQL Standard) (currency)
The cost per device core under a SQL Server Standard license. This is the unit cost used for calculating values in this report. The report finds the SQL Server Standard license with the largest number of associated purchases (see Reference license (SQL Standard)), and then this value is taken from the first available of:
  • The Amount field on the Financial tab of the license properties
  • The Override unit price shown in the Purchases tab of the properties of this reference license
  • The most recent Unit price in software purchases attached to the reference license
  • The default value of 1,300 (a hard-coded value based on the US$ list price per core).
Important: USD currency must be added as a supported currency to your tenant. Note there is no requirement to make USD your default currency. If USD is not added to your list of supported currencies, returned VM host data may contain discrepancies such as missing VM hosts and inaccurate price related data. To add USD currency, navigate to Administration > IT Asset Management Settings > Defaults > Currency Settings. On this page select Add a Currency and then United States Dollar (USD) from the Currency drop down list. When finished select Add.
Current SQL license

Displays the name of the license under which consumption on the ESX server (or orphan VM, or standalone computer) is currently covered.
Device corporate unit

The corporate unit that has been linked to the device shown in this row.
Device cost center

The cost center that has been linked to the device shown in this row.
Device location
The location that has been linked to the device shown in this row.
Remember: If this device is a virtual machine, and your system setting Update virtual machine location to match host location is set to true, this location may be inherited from the host where the VM is a guest.
Device name

The name of the inventory device in this row. The compliance calculation updates this field with the machine name returned in inventory (matched by several properties, including serial number).
Device role

Specifies the role assigned to a device. Assigning a role may allow for exemptions that limit license consumption for roles specified in some license agreements. Device roles (where permitted by the product use rights on a license) can exempt devices from consuming entitlements on a license to which they are (and remain) attached. For example, some license agreements may grant an exemption for testing devices. For more details, see Allocations and Exemptions.

Possible roles include:
  • Backup / Archive — This device cannot be started without first restoring it from an archive copy. Typical publisher terminologies include backup, or archival.
  • Cold Standby / Disaster Recovery — This device is not currently running, but could be started at any time. Typical publisher terminologies include disaster recovery, cold standby, cold disaster recovery, or failover.
  • Development — This device is used exclusively for system development. It does not carry any production load.
  • Hot Standby / Active Failover — This device is running, and probably doing work such as mirroring database changes. Typically, this system requires a license, so that it would be unusual to select this role for an exemption. Typical publisher terminologies include hot standby, active, active clusters, mirroring, HA, standby, or remote mirroring.
  • Production — Specifies that the device is being used in the production environment, and it consumes license entitlements for its installed software. This is the default value for all newly imported devices, and you need to change it to reflect any other specific role.
  • Test — Specifies that the device is being used exclusively in system testing, and does not carry any production load.
  • Training — Specifies that the device is being used for training purposes. Most publishers require that this use is exclusive, and may also require that the device is within a dedicated training facility.
  • Warm Standby / Passive Failover — This device is on, but is idling and not carrying any production load.
Device status The condition of this device at the most recent inventory import and compliance calculation. Possible values include:
  • Active — A device for which the inventory information is received from an inventory source.
  • Ignored — The device that is not managed. An ignored device is not considered in license consumption calculations.
    Tip: If an inventory device is linked to an asset record, and that asset is given a status of either Retired or Disposed, this Ignored value is automatically set for the linked inventory device.

Provided that the inventory device has not been linked to an asset record (which would be extremely unlikely for a VM), this value is editable in the General tab of the inventory device properties.
Device type
The kind of computer (or other device) represented in this row. Possible values include:
  • Computer
  • Virtual Machine
  • VM Host.
Enterprise cores for all host VMs

The cores consumed from the license when all the VMs on the virtual host are covered by a SQL Server Enterprise license.
Enterprise cores for VMs with SQL Enterprise
In the scenario where you are 'splitting' the VMs into two groups and applying the most economical license, this is the consumption of cores for VMs running Microsoft SQL Server Enterprise Edition, and covered with a SQL Server Enterprise license.
Tip: It is also possible for a single VM to run instances of both editions of SQL Server, with both the Enterprise Edition and the Standard Edition on the VM covered by the same SQL Enterprise license. Such cases are also included in this column.
ESX cost (SQL Enterprise) (currency)

The cost of licensing the ESX host identified in this row under the SQL Server Enterprise license (with consumption capped at the total number of physical cores in the host).
Exempted device roles

A comma-separated list of the device roles which are exempted from consuming license entitlements in this report. Exempted roles are assumed to be the same for SQL Enterprise and SQL Standard licenses. These exemptions are defined by the Reference license (SQL edition) that has the higher number of purchases (and are set on the Use rights & rules tab of the properties for that reference license, under Exemptions).
Hosted in

Shows whether the inventory device is on-premises (the default, meaning the device is within your enterprise), or in a cloud operated by a particular service provider. For some cloud service providers (like AWS and Azure), the Hosted in value is set automatically through inventory. For other cloud service providers, you must make a selection manually.
Hosted in cloud

Indicates No if the inventory device is hosted on-premises (within your enterprise). Indicates Yes if the inventory device is hosted in the cloud by a cloud service provider. Most inventory devices hosted in the cloud are virtual machines; but this value is available for all inventory devices, because some cloud service providers also rent entire machines (for example, AWS provides dedicated hosts and bare metal instances). This is a convenience column for grouping/filtering inventory devices that are hosted in the cloud. The name of the cloud service provider for each inventory device is available in the Hosted in column.

Host name

The system name of this device.

License type
Shows the type of core license for which consumption in this row is calculated. The license name has +SA appended as a reminder that the license is expected to be covered by Software Assurance, so that (for example) mobility rights apply. Identifies one of:
  • Microsoft SQL Server Standard Core license
  • Microsoft SQL Server Enterprise Core license.
Operating system

The operating system running on this device. This value is collected from the device inventory record.

Value may be blank for rows that do not represent a single inventory device.

Editable in the Hardware tab of the inventory device properties (for manually-created records).
Optimal license
The preferred license to apply to the virtual host, stand-alone device, or orphan VM in this row.
Tip: This device could have two rows, one with each of the following values, in cases where it is financially optimal to split consumption across the separate license editions.
The optimal license is one of:
  • SQL Server Standard license, when the device has Microsoft SQL Server Standard Edition installed
  • SQL Server Enterprise license at other times.
In short, the lower cost option is the one proposed in each row. The VMs/devices that are in scope for this recommendation are listed in the Consuming installations column.
Overall optimization (currency)

The sum of all the Saving for host values for all the hosts that are covered by the SQL Server Enterprise license (that is for all rows where this is shown as the Optimal license in this report).

Processors
Depending on the Device type in this row, this is:
  • The number of processors in the standalone device (for a Computer)
  • The number of processors in the ESX server (for a VM Host)
  • The number of processors assigned to the VM (for a Virtual Machine).
Recommended license (host)

The optimum (lowest cost) license type to apply to the virtual host shown in this row, so that all the VMs running SQL Server on this host are compliant with the license terms.
Reference license (SQL Enterprise)

The SQL Server Enterprise license (with Software Assurance) that has the largest number of associated purchases recorded, and which is used to identify the cost per core for this license type (see Cost/core (SQL Enterprise)).

Reference license (SQL Standard)

The SQL Server Standard license (with Software Assurance) that has the largest number of associated purchases recorded, and which is used to identify the cost per core for this license type (see Cost/core (SQL Standard)).

Saving for host (currency)
The difference between the cost of including the ESX host server in this row under the SQL Server Enterprise license and the cost under the SQL Server Standard license.
Tip: In the case when the optimal solution is to mix the license editions on the same virtual host (based on what product editions the hosted VMs are running), there are two similar rows, one for the SQL Standard license and one for the SQL Enterprise license. In this case, the overall saving is halved, with half the total value shown on each of these two rows.
This value is blank when either:
  • The row does not relate to a virtual host (that is, when Device type has any value other than VM Host)
  • The Optimal license is not displaying Microsoft SQL Server Enterprise Core+SA.
Standard cores for VMs with SQL Standard

In the scenario where you are 'splitting' the VMs into two groups and applying the most economical license, this is the consumption of cores for VMs running Microsoft SQL Server Standard Edition, and covered with a SQL Server Standard license.
Total cores consumed (SQL Enterprise)
The total exposure (for the next subscription renewal) of consumption for all rows relating to clusters covered by the SQL Server Enterprise license, if the proposed optimization is applied in your environment.
Tip: Shows a zero for rows containing an ESX VM Host in a cluster where the recommended license is SQL Server Standard.
Total cores consumed (SQL Standard)
The total exposure (for the next subscription renewal) of consumption for all rows relating to clusters covered by the SQL Server Standard license, if the proposed optimization is applied in your environment.
Tip: Shows a zero for rows containing an ESX VM Host in a cluster where the recommended license is SQL Server Enterprise.
Value consumed (currency)

The cost of the license option shown in this row for the host in this same row.

IT Asset Management (Cloud)

Current