Choosing between Enterprise and BI Edition for SSAS Environment

SQL Server licensing often finds itself at the butt of a joke amongst professionals in this industry.

20PageManual

In case it’s not obvious in the dialogue above, Kendal Van Dyke (b | t) is being sarcastic. Yes, there is a SQL Server licensing guide…and while technically its much closer to 30 pages (not even including all the additional references linked to in the document like Volume Licensing, Multiplexing, etc)…it really only gives you an overview. For example, it doesn’t include things like “discount magic” which is an outstandingly accurate term that I plan on borrowing blatantly stealing from Nick Craver (b | t)…

DiscountMagic

Buying SQL Server licenses has also been compared to buying a car which again seems pretty accurate to me since it can cost as much as a new car…at least on the low end 😉

If, however, you’re dealing with the high-end (think: big powerful servers), it’s probably more accurate to compare it to buying a piece of real-estate since licensing costs can start to approach that of a 10k sqft McMansion in a nice Atlanta neighborhood (or just a regular 2 bed/1 bath apartment in an ‘ok’ neighborhood of NYC or San Fran).

Regardless of which end of the spectrum you’re on, it’s absolutely worth a bit of discussion up front (preferably with non-biased confidant) before simply purchasing whichever license the Car Microsoft sales person is pushing.

The rest of this post is a high-level exercise to help you get a feel for the process of choosing the correct license for SSAS-only implementations…and it doesn’t include any further discussion of “discount magic”; only list prices.

The Basics

Prior to SQL Server 2012, there were only 2 editions to choose from for SSAS…

  • Standard
  • Enterprise

…and deciding between the two was fairly straight forward. Use Enterprise when you need every feature of SSAS available and/or require a big server with more than 16 cores and/or 64 GB of memory. Use Standard when you can get by without all the bells and whistles (e.g. > 3 partitions/measure group, writeback, measure expressions/semi-additive aggregation functions), and don’t require a high-powered server…or when you simply can’t afford Enterprise.

Note: there are ways to workaround some (not all) of the feature limitations of SSAS Standard Edition via creative MDX scripting (albeit usually at the cost of hefty performance penalty). But if you don’t have experienced SSAS developers on hand who are familiar enough with the product and know how to implement the desired workarounds, it may simply be easier/cheaper to pay for Enterprise (instead of paying for an outside contractor/consultant to add their duct tape).

Then SQL Server 2012 (same applies for 2014) came along and shook things up with the introduction of a new edition called Business Intelligence. This new license is a server plus CAL (client access license) model – which means you buy a single “server license” for each server and a single CAL for each user (regardless of how many servers they connect to). From a BI-feature perspective, the BI-Edition license is 95% feature complete for the BI-related services (i.e. Analysis Services, Reporting Services, Integration Services, Master Data Services, Data Quality Services, yada yada yada)…the only exceptions are with SSIS where several adapters and transforms are only available in Enterprise Edition. From a relational database perspective, the BI-Edition is lacking quite a bit…both in feature and hardware limitations…no table partitioning, no columnstore indexes, no Hekaton…read this for all the nitty-gritty.

All that said, from a simple SSAS-perspective, there isn’t a single difference (that I’m aware of) between Enterprise and Business Intelligence Edition. Zero. None. Nada. Zip. Ziltch.

So how do you know which to choose?

Is BI Edition Right for Me?

Yes! …well, maybe…It depends?

BI Edition is certainly a viable option for many SSAS implementations. However, there’s a tipping point once you hit a certain user/core ratio that you absolutely need to be aware of to avoid making the wrong choice.

Below is a quick capture of a breakdown…

image

As you can see, the cost-advantage of going with BI-Edition begins to break down at…

  • ~300 users for a 10 core server
  • ~500 users for a 16 core server
  • ~650 users for a 20 core server
  • ~1050 users for a 32 core server

…and there aren’t many folks I know of (besides Alex Whittles and Jens Vestergaard) who are running SSAS on more than 32 cores so the task of extrapolating out the numbers is left to the reader. And hopefully no one is running a tabular instance on a server w/ more cores – at this scale, we’re talking NUMA, and Tabular still doesn’t play well with NUMA (see here, here, and here).

Ok, so some folks might be thinking this is ridiculous …who has that many users?

You’re right, that’s a very valid question to ask as I suspect at least 80% of implementations have internal-only user bases that never sees more than 250 users which puts them squarely in the BI-edition camp. However, there’s still that other chunk of implementations, such as the case with one of my recent clients, where they will have >3k users (potentially as high as 5k) making the cost of BI-edition a joke with a price tag of ~$605k (for 3k users) up to ~$1mm (for 5k users)…OUCH!

Note: Just another reminder that this breakdown is based on SQL 2014 **list price** which I doubt many companies at this level/size are paying as most end up getting a better price by bundling with Office/OS licenses and other discount magic. 

What about Scale-Out Query Servers?

This is one area where its easy to make a mistake and pick the wrong license.

Note: For those unfamiliar, in the world of SSAS, the term “Scale-Out Query Servers” or “Scale-Out Query Architecture” refers to a group of SSAS servers each hosting the same copy of the Multidimensional or Tabular model. The query load is then balanced between all the servers in the cluster providing linear scalability for concurrency.

With Enterprise-Edition, all cores on each box have to be licensed ($6,874 per core)…where as with BI-Edition nothing really changes – a server license for each box ($8,592 each) plus a single CAL for each user ($199)…that’s right, single CAL per user – NOT “single CAL per user per server” which would be insanely ridiculous.

That’s a pretty big deal. To see why, consider a scenario where we have 3 scale-out query servers (20 cores each) and 1000 users…keeping in mind that the BI-Edition breakdown for a single 20-core server in the first part of this post was around 650 users after which Enterprise becomes the better choice.

  • BI-Edition: (3 servers * $8,592/server) + (1k users * $199/user) = ~$225k
  • Enterprise: (3 servers * 20 cores/server * $6,874/core) = ~$412k

…that’s a big difference – nearly $200k saved by choosing the correct license!

Here’s the rest of the breakdown…

image

…to summarize, the breaking point for BI-Edition for a Scale-Out Query Server Architecture (with 3 servers) is as follows…

  • ~900 users for 10-core servers
  • ~1550 users for 16-core servers
  • ~1950 users for 20-core servers
  • ~3200 users for 32-core servers

…these are the tipping points, after which Enterprise becomes the correct choice.

Real-World Scenario

Remember the client referenced earlier in this post? Below is the original architecture that was setup to meet the required level of service…

  • 1x 20-core server (processing) – Enterprise Edition – $137k
  • 6x 20-core server (scale-out query servers) – Enterprise Edition – $825k
  • 1x 32-core server (scale-out query server) – Enterprise Edition – $220k
    (added late in the game to accommodate an increase in load)

That’s ~$1.2mm in licensing costs alone…YIKES!

Considering this client had an expected user base of 3k-5k, there was definitely some savings to be had by simply switching to BI-Edition: immediate savings of ~$515k for 3k users or a savings of ~$115k if/when they hit 5k users (an estimated high-watermark by the business).

Sounds pretty good right? It gets better…

Fortunately for this client decided to hire us (for a tiny fraction of their licensing costs) to tune the cube and MDX ultimately – after which they were able to scale back down to just 2 servers while handling the same load. This pushed the decision (between BI-Edition and Enterprise) very clearly in the Enterprise-camp…and saved them nearly ~$900k!

  • ORIGINAL ARCHITECTURE: $1.18mm
    • 8 servers, 172 cores total – Enterprise Edition
  • NEW ARCHITECTURE: $275k
    • Option1: 2x 20-core server – Enterprise Edition – $275k
    • Option2: 2x 20-core server @3k users – BI Edition – $615k  (or $1mm at 5k users)

Needless to say, the client was quite please with the results 😉

 

Got a big SSAS implementation and not quite sure you’re using an optimal architecture/licensing strategy? Contact me for a free consultation…you could be leaving large piles of money on the table.

 

Additional Resources:

 

4 thoughts on “Choosing between Enterprise and BI Edition for SSAS Environment

  1. Whilst SQL engine on BI Edition only supports up to max 4 sockets and 16 cores (no limit on SSAS, SSRS, SSIS), your calculation is based on 20 cores on BI Edition. What’s the implication on this? Are you still only paying 1 server license?

    https://msdn.microsoft.com/library/cc645993.aspx

    By the way, really great article.

    Thanks,
    Simon

    1. Hi Simon,

      Yes, with BI Edition you are always only paying 1 server license plus as many CALs as you require for your user base. So even (in the most extreme case) if the SSAS server has 64 “sockets” and 512+ “physical cores” (woah!)…you’re still only buying 1 server license (@ ~$8.6k) for all of those cores.

      Btw, at 512 cores, it still makes sense to go with BI Edition until you cross ~17650 users…after which enterprise makes sense…the breaking point is $3.5mm in licensing.

  2. Didnt realise this about BI Edition, it sounds unreal! A great win for those who plan implement BI solutions

    Simon

Leave a Reply