My quest to bring Best Practices to Identity Management especially with Microsoft FIM / ILM

Saturday, August 16, 2008

SQL 2008: Processor or Server/CAL

Congrats to the SQL Server team for shipping 2008. It looks like a great product. Congrats as well for keeping the licensing costs the same and adding a new option with the web edition.

One question that many still have in mind is how to license SQL server. Processor licensing allows unlimited users and devices, whereas a server license allows unlimited users or devices as long as they have a CAL. Server CAL can be much cheaper than Processor license or it can become much more expensive.

Many ILM customers also have this question, and while the product team pushes you to processor licenses and says that if you want to go Server/CAL you need 1 CAL for each user that will connect directly to the SQL server (in case it does other stuff, or your ILM admins like to run unsupported queries under the hood) for each Management agent (how in the world did 1 device -- the ILM server -- become multiple devices and need many CALs? -- Maybe under some sort of multiplexing scenario since the data is getting pushed to other places) each situation will be different.

So I have developed a formula and a table to help you figure this out

Per Processor Licensing Costs =
Cost Per Processor * # of Servers * Avg CPUs per Server

Server + CAL Licensing Cost =
Cost Per Server * # of Servers + Cost Per CAL * # of CALs

Break Even Formula
# of CALs = (Cost Per Processor * Avg CPU per Server - Cost Per Server) * # of Servers/Cost Per CAL

As you can see the break even point between Server/CAL and Processor comes down to a ratio between CALs divded by the number of servers vs Processors (not cores, but physical packages) divded by the number of Servers. (Remember that one CAL allows one user or device to access an unlimited number of licensed servers -- you don't have to buy a CAL for each server you want to access. See

Using the sample licensing costs from Microsoft's site

Edition Workgroup* Standard Enterprise
Per Processor  $         3,700  $       5,737  $     23,911
Per Server  $            730  $         885  $       8,487
Per CAL  $            146  $         162  $          162


Using these sample numbers (and your costs maybe different)


Break Even Points in terms of CALs per Server vs CPU/Server
Workgroup Standard Enterprise
CPUs/Server CALs/Server CALs/Server CALs/Server
1                  25               30               95
2                  51               65             243
4               136             538
6                 833
8               1,128
10               1,424
12               1,719
14               2,014
16               2,309
18               2,604
20               2,900
32               4,671
64               9,394


10 SQL Enterprise Edition servers with an average of 2 quad core processors (that is still just two processors for licensing purposes) and I have less than 2430 (243x10) devices or users to license and am likely to maintain these ratios than Server/CAL should be cheaper than processor licensing. 20 processor licenses at $478,220 and 10 server licenses $84,870 plus $393,350 cost the same. Hiring more people and/or acquiring more devices might tip the balance, but acquiring more SQL servers or adding processors to existing boxes could counter that.

As you can see this is really an enterprise wide decision how will I license SQL servers for my organization.

What to do about users coming in from the web, well you can use processor licensing for those SQL servers or you could go with the web edition for $15 per processor per month. What about data on internal servers? Replicate it to the web edition server! SQL to SQL communication does not require a CAL.

Remember that "SQL Server 2008 Web may be used only to support public and Internet-accessible Web pages, sites, applications, and services."

In terms of features it is comparable to Workgroup edition, so none of the high availability features like clustering or mirroring are supported, only log shipping. There are several minor differences in the functionality of Web vs Workgroup

No ad hoc reporting through report builder, in service broker it can only be a client and its development tools come from SQL Express Management studio.

Labels: , , ,

Thursday, May 15, 2008

Processing Actions Asynchronously outside of ILM MA's

For years developers have had access to the Microsoft Message Queue (MSMQ) as a way to be able to queue up actions for processing later or on a remote machine. With the release of SQL 2005 back in well 2005, developers with access to SQL 2005 could replace these MSMQ apps with SQL Service Broker Queues (SSB). With ILM 2007 /MIIS 2003 SP 2 supporting SQL 2005 the use of SQL Service Broker Queues became much more accessible to ILM Developers.

Here is an article comparing MSMQ with SQL Service Broker Queues

Here it is another discussion on the matter in an MSDN forum:

Here is an interesting blog post about working with both SQL Service Broker Queues (SSB) and

Windows Workflow Foundation:

The DevHawk has many good insights into SSB.

One huge advantage to me in dealing with SSB is that I can query a view just like a table to examine its current contents. SSB is also very easy to scale out.

At DEC 2008 Craig Martin and Marshall Hamilton (both from OCG North America) spoke on the SSH MA (originally developed by Patrick Rempel from OCG Germany) which is being used to manage thousands of Unix servers (well together multiple instances of the MA are managing several thousand servers) through SSH connections and issuing he command line commands to create users etc.

One problem they had to solve was what if one server is down during import? Regurgitate data for systems that are down which necesitates caching the data somewhere. Another problem I envision in such scenarios is that performance is probably an issue. Imagine a slightly different design.

What if an intermediate database was used to hold the results from the servers. Importing data from the servers on separate schedules, then importing to ILM on yet another separate schedule. The requests to import could be popped into a queue for multiple processes to implement. For executing exports have the XMA pop the requests into a SSB queue and then have a process that pulls info from the queue. If performance on export becomes an issue it is child's play to scale out with SSB, add another process that pulls import request messages from the queue.

Another use case for SSB is to replace anytime MSMQ has been used. Benefits: SSB can be clustered for failover, back up is as simple as backing up the database hosting the SSB queues.

For a Gentle Intro to SSB

Labels: , , , , ,