SQL 2012 PowerShell script to detect CPU’s configured for licensing

SQL 2012 has changed the license model around CPUs/Cores. The below script can be used to help detect how many CPU’s are on the host and also configured in SQL. If you detect that you do not have the correct CPU’s, then you may need to reconfigure SQL.

Example outputs:

SQL Server CPU check
Checking server instance TESTSQL
—————————————————
Server Physical Cores : 4 ( 2399 mhz )
Server Logical Cores  : 4
SQL Server Cores      : 4
SQL Edition           : Standard Edition (64-bit)
—————————————————

SQL Server CPU check
Checking server instance TESTSQL
—————————————————
Server Physical Cores : 4 ( 2399 mhz )
Server Logical Cores  : 4
SQL Server Cores      : 2
SQL Edition           : Standard Edition (64-bit)
—————————————————

Script:

################################################################################
#
# Script: DetectCPUCores.ps1
#
# Author: Craig Wilson
# Date: 22/06/2012
# Version: v1.0.0
#
# This script will connect SQL and the WMI of then host to collect CPU core
# information
################################################################################

function DetectCPUCores ([string]$SQLINSTANCE, [string]$HOSTNAME)
{

    Write-Host "SQL Server CPU check"
    Write-Host " Checking server instance $SQLINSTANCE"
    Write-Host "---------------------------------------------------"    

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
    $SqlConnection.ConnectionString = "Data Source=$SQLINSTANCE;Integrated Security=TRUE" 
    $SqlConnection.Open() 

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
    $SqlCmd.CommandText = "select serverproperty('ProductVersion')" 
    $SqlCmd.Connection = $SqlConnection 
    $sql_ProductVersion = $SqlCmd.ExecuteScalar() 

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
    $SqlCmd.CommandText = "select serverproperty('Edition')" 
    $SqlCmd.Connection = $SqlConnection 
    $sql_edition = $SqlCmd.ExecuteScalar() 

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
    $SqlCmd.CommandText = "select count(*) from sys.dm_os_schedulers where status = 'VISIBLE ONLINE'" 
    $SqlCmd.Connection = $SqlConnection 
    $sql_schedulers = $SqlCmd.ExecuteScalar() 

    $SqlConnection.Close() 

    $property = "systemname","maxclockspeed","addressWidth","numberOfCores", "NumberOfLogicalProcessors"
    $cpu_info = Get-WmiObject -computername $HOSTNAME -class win32_processor -Property  $property | Select-Object -Property $property 

    Write-Host "Server Physical Cores :"$cpu_info.numberOfCores "("$cpu_info.maxclockspeed"mhz ) "
    Write-Host "Server Logical Cores :"$cpu_info.NumberOfLogicalProcessors
    Write-Host "SQL Server Cores : $sql_schedulers"
    Write-Host "SQL Edition : $sql_edition"
    Write-Host "SQL Product Version : $sql_ProductVersion"
    Write-Host "---------------------------------------------------"

}

DetectCPUCores "SQLInstance" "LOCALHOST"



For more information on how the licensing impact you check out the following blog posts

SQL Server 2012 Enterprise Editions

http://blogs.msdn.com/b/saponsqlserver/archive/2012/06/15/sql-server-2012-enterprise-editions.aspx

SQL Server 2012 Licensing (Resources Pane)

http://www.microsoft.com/sqlserver/en/us/get-sql-server/licensing.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s