How does SQL Server Licensing work and how to figure out what type of license you need?

As a DBA it is unlikely that you will be asked to work on licensing for SQL Server. But unfortunately (Or fortunately since I learnt new things) I was tasked to do the same.

First of all let’s see what the different types of licenses Microsoft offers are (We will consider SQL Server 2008 for this discussion)

SQL Server 2008 is available under three licensing models:

1.      Server plus device client access license (CAL)

2.      Server plus user client access license (CAL)

3.      Processor license

So how do you decide what type of license you need?

Server License: Every physical host which is running SQL Server needs a server license. SQL Server can be either database, Analysis services, Integration services or reporting services.

Device CAL: If your environment has fixed number of devices (PC, workstations, mobile devices etc.) which will connect to SQL Server then device CAL is most suitable for you. A service plus device CAL is most suitable for environments where multiple users use same workstations (e.g call centers)

User CAL: A user like employee, customer etc. requires access to SQL server then a user CAL is required for each user. Let’s say there are 10 employees and 50 customers then you will need 60 user CALs. Now please note that each of these users can connect to SQL server from ANY number devices. Also let’s say there are 3 SQL instances (1 database 1 SSRS and 1 SSAS) then each user will require a single user CAL to connect to all the 3 instances irrespective of host on which they are residing. All the 3 instances have to be of same SQL version.

Processor CAL: This is little different from device and user CAL. Let’s say you have a web application and number of users connecting is not fixed. In such situation you need to get processor CAL. 1 processor CAL is required for each processor installed on host running SQL Server (including any of its components like database, reporting services, analysis services or integration services) Number of users is unlimited and number of devices is also unlimited and connections can be from within the organization or outside (e.g via website).

If you already have a environment setup and you need to figure out the number of users connecting or number of devices connecting then use either of the following queries.

--FOLLOWING QUESY WILL GIVE ALL LOGINS WHICH HAVE ACCESS TO SQL SERVER

--FOR WINDOWS GROUPS YOU WILL HAVE TO MANUALLY FIND OUT NUMBER OF USERS IN THAT GROUP THROUGH ACTVE DIRECTORY SERVICE

SELECT *

FROM master.sys.server_principals

where type_desc in (

'WINDOWS_GROUP',

'SQL_LOGIN',

'WINDOWS_LOGIN'

)

--FOLLOWING QUERY WILL GIVE DISTINCT USERS CONNECTED TO A SQL SERVER INSTANCE AND NUMBER OF SESSIONS

SELECT  login_name ,

        COUNT(session_id) AS [session_count],getdate() logdate

FROM    master.sys.dm_exec_sessions

GROUP BY login_name

--FOLLOWING QUERY WILL GIVE NUMBER OF DEVICES CONNECTED TO A SQL INSTANCE ALONG WITH NUMBER OF CONNECTIONS

SELECT  ec.client_net_address ,

        es.[program_name] ,

        es.[host_name] ,

        es.login_name ,

        COUNT(ec.session_id) AS [connection count]

FROM    MASTER.sys.dm_exec_sessions AS es

        INNER JOIN MASTER.sys.dm_exec_connections AS ec

                                   ON es.session_id = ec.session_id

GROUP BY ec.client_net_address ,

        es.[program_name] ,

        es.[host_name] ,

        es.login_name

ORDER BY ec.client_net_address ,

        es.[program_name] ;

SQL Server license covers all components which come with it including SSRS, SSIS, SSAS.