SAM Considerations for Tracking SQL Server modes

Thanks to Gordon who brought this to my attention

SQL Server is licensed in 2 modes – Server and CAL which is Per Seat or Per Proc, it is an anomaly in the world of Licensing, but, that said, gives greater flexibility, see more info on SQL licensing HERE

 

For SQL 2000 we had an applet in Control Panel which helped to identify which mode you had deployed in and the user was prompted during setup to enter the license type. The resultant data was stored in the registry and could be viewed using the Control Panel applet. We found that this data was not consistently used nor could it be relied upon to be accurate. As a result it was removed in SQL Server 2005. Now this can prove to be a bit of a problem for larger companies who need to keep a track of this for SAM and compliancy, so, as a work around we have the following solution

 

Tracking License Information in SQL 2005

SQL 2005 no longer tracks licensing (per seat or per processor) via registry entries. SQL 2005 still reads the registry for this information, but the SQL 2005 setup doesn’t put licensing information in the registry during setup as in SQL 2000.

 

This is by-design. Hence, when ServerProperty(‘LicenseType’) is run on a SQL 2005 installation, ‘DISABLED’ is always returned.


Supported Resolution

Since SQL 2005 still queries the registry for licensing information, add the following key and values and ServerProperty(‘LicenseType’) will return license information.

Note: Licensing has always been server wide and not SQL instance specific. This setting would apply to all instances of SQL Server on the server.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\MSSQLLicenseInfo\MSSQL9.00

If you want to configure SQL Server for Per Processor then add these Registry Values under that Key adjusting for the number of processors you have a license for:

 

Name                         Type                           Value
Mode                         REG_DWORD           2           ß LICENSE_MODE_PERPROC   
ConcurrentLimit        REG_DWORD           4           ß Number of Processors

 

 

If you want to configure SQL Server for Per Seat licensing then add these Registry values under the Key adjusting for the number of seat license you have purchased.

 

Name                         Type                           Value
Mode                         REG_DWORD           0            ß LICENSE_MODE_PERSEAT   
ConcurrentLimit        REG_DWORD           100         ß No. of client licenses registered for SQL Server in Per Seat mode.

 

 

Test in SQL Management Studio

You need to stop and restart SQL Server 2005 before the information will be available to ServerProperty() as the registry is read on start-up of SQL Server.

With the above settings you would see the following when you restart SQL Server 2005.

 

SELECT  ServerProperty(‘LicenseType’) as LicenseType, ServerProperty(‘NumLicenses’) as ProcessorCount

 

Output:
LicenseType             ProcessorCount

PER_PROCESSOR           4

 

We are aware and committed to the need to provide a much more efficient way for centrally tracking SQL Server licenses and will be looking at different options.

 

The new control features added to SQL Server 2008 R2 are one possible place where we could centralize license metadata management in a future release and this is being assessed.

 

In terms of understanding what model/edition of SQL Server you have installed the Microsoft Planning and Assessment Toolkit, which is a free download, does an excellent job of creating an inventory of the SQL Server instances on your network.

 

Hope this helps

Emma

Advertisements

2 Comments

  1. Could you please explain sentense : “The new control features added to SQL Server 2008 R2 are one possible place where we could centralize license metadata management in a future release and this is being assessed?” Since we are still looking for best way how to track number of liceses acros our environment, it would be great to know how we can do that for SQL 2008 R2. Registry settings do not work anymore at least not for SQL 2008 R2. MAP Tool is great, but to scan manually 40.000 computers and 6.000 servers with this tool is nonsense (SCCM is collecting wrong data and reporting doesn’t seem to be working properly). Thanks for your reply.

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 )

Google+ photo

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

Connecting to %s