Friday, May 25, 2012
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 (
2. Server plus user client access license (
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.
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
where type_desc in (
--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
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 ,
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 ,
ORDER BY ec.client_net_address ,