SQL Server License Control and Management

[mleal]

Today many applications (SAP, SIEBEL, MANUGISTICS, PEOPLESOFT, and so
on) does not have a client direct access to connect to the database.
They use an Application Server that has only one connection to the
database and the users get connected to this application server.

THE QUESTION: How to control SQL Server User License in this new model,
automatically?

You cannot enable SQL Server Audit because users are not connected
directly to the database.
You cannot use Identity Management applications because they do not
work on NOT Enterprise Business software.

How to do that if you have hundred of applications and database?
Regards,
Marcio

[Dan Guzman]

I don't see how it it's possible to automate CAL tracking with an n-tier
application model. It's more cost-effective to use processor licensing with
enterprise systems like this anyway so it's a non-issue.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"mleal" <ma****************@ig.com.br> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...

Today many applications (SAP, SIEBEL, MANUGISTICS, PEOPLESOFT, and so
on) does not have a client direct access to connect to the database.
They use an Application Server that has only one connection to the
database and the users get connected to this application server.

THE QUESTION: How to control SQL Server User License in this new model,
automatically?

You cannot enable SQL Server Audit because users are not connected
directly to the database.
You cannot use Identity Management applications because they do not
work on NOT Enterprise Business software.

How to do that if you have hundred of applications and database?
Regards,
Marcio

[mleal]

Hi Dan. Thanks for answering.

But I cannot use processor licensing for all these applications and the
other ones we have here.
Believe me, it would increase the costs a lot.

Try to imagine almost 1000 databases. How do I know that I have the
CALs that I need?

It's a very complicated problem.
Regards,
Marcio

[David Portas]

mleal wrote:

Hi Dan. Thanks for answering.

But I cannot use processor licensing for all these applications and the
other ones we have here.
Believe me, it would increase the costs a lot.

Try to imagine almost 1000 databases. How do I know that I have the
CALs that I need?

It's a very complicated problem.
Regards,
Marcio

The point is that SQL Server CALs are licensed per end user not per
connection. So regardless of the method used to connect SQL Server
can't count the number of users for you. The number of databases is
also irrelevent.

With Enterprise Edition, Processor Licensing is cheaper unless you have
fewer than about 100 users per processor. About half that number for
Standard Edition. So if your user base is that small you shouldn't have
so much trouble tracking the number of licences required. How many
users do you have?

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[mleal]

David,

I agree with you and I know that I need to count CALs by my number of
end user.

The problem I don't know correctly how many users I have and many of
them are not employee any more.

My solution for that would be: Export all my users from the
application, because if it connects to the application it needs a sql
license, and than match it to my HR system. If user is an employee I
count a sql license. This would be a solutions.

But it is hard work. Is there not another way?
Thanks,
Marcio

[David Portas]

mleal wrote:

David,

I agree with you and I know that I need to count CALs by my number of
end user.

The problem I don't know correctly how many users I have and many of
them are not employee any more.

My solution for that would be: Export all my users from the
application, because if it connects to the application it needs a sql
license, and than match it to my HR system. If user is an employee I
count a sql license. This would be a solutions.

But it is hard work. Is there not another way?
Thanks,
Marcio

If you really don't know how many users you have then that is one way.
I still don't understand why this would be an issue. Like I said, CALs
are economical only for small enterprises. If you only have 100 or 200
employees then it can't be that difficult to track what applications
they need to use (I once used to do it for a user base of less than
300). Also, applications such as SAP or SIEBEL will cost you FAR more
per user than SQL Server will. For that reason I've never considered
that any of them would be licensed any other way than with a processor
licence. I've not known CAL licensing used with those apps in practice.

Check out the licensing and pricing options at:
http://www.microsoft.com/sql/howtobuy/default.mspx

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Luukkanen Tapio]

Maybe you could periodically select stuff from master.db.sysprocesses, and
count the number of distinct remote client workstation names or number
of distinct client user names over some suitable period?

-tapio

Something developed out of the following might give a guess of the
number of distinct "real users" you have?
Of course this needs changes if you use sqlserver security.

if not exists (select 1 from sysobjects
where name = 'connstats' and type='U')
begin
create table connstats (
client varchar(20)
,ntuser varchar(20)
,program varchar(40)
,dbname varchar(20)
,ts datetime
)
end
go

if exists (select 1 from sysobjects
where name = 'dbu_gather_stats' and type='p')
drop procedure dbu_gather_stats
go

create procedure DBU_gather_stats
as
begin
insert into connstats(client, ntuser, program, dbname, ts)
select p.hostname, p.nt_username, p.program_name
,d.name, getdate()
from master.dbo.sysprocesses as p
,master.dbo.sysdatabases as d
where p.dbid = d.dbid
end
go

grant execute on DBU_gather_stats to public
go

if exists (select 1 from sysobjects
where name = 'dbu_stats' and type='p')
drop procedure dbu_stats
go
create procedure DBU_stats
as
begin
select
"date"=substring(convert(varchar,ts,126),1,10)
,"clients"=count(distinct client)
,"users"=count(distinct ntuser)
,"programs"=count(distinct program)
from master..connstats
group by
substring(convert(varchar,ts,126),1,10)
end
go

grant execute on DBU_stats to public
go

[Luukkanen Tapio]

Ouch... i didn't see the original post about having an n-tier
architecture until now, so my previous post is of no help to the
original poster. Sorry for this minor waste of bandwidth.

-tapio

[mleal]

David, thanks.

Last question: Thing big.

How would you manage CALs from more than 300 servers, 500 databases
used for more than 400 applications with lots of users being created
and deleted from applications and employees that use these applications
being hired or fired? (you need to manage if employee is active to
count a license). At the end of year this is the number we need,
accurate, to buy more or restart, diminish, with MS.

Thanks for your help.
Marcio

[David Portas]

mleal wrote:

David, thanks.

Last question: Thing big.

How would you manage CALs from more than 300 servers, 500 databases
used for more than 400 applications with lots of users being created
and deleted from applications and employees that use these applications
being hired or fired? (you need to manage if employee is active to
count a license). At the end of year this is the number we need,
accurate, to buy more or restart, diminish, with MS.

Thanks for your help.
Marcio

The number of servers, applications and databases is irrelevant as far
as CAL numbers are concerned so the problem is much simpler than you
make it sound. I assume you are talking about CAL numbers in the
thousands or tens of thousands? Have you talked to a Microsoft
licensing specialist? I expect they can suggest an assessment of your
CAL numbers that can satisfy both you and them. At that level you
aren't expected to account for every single user. This is my opinion
not Microsoft's but I'm guessing you can use departmental head counts
or other organizational metrics as the basis for the numbers.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--