Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Virtualising Analysis Services

Microsoft reckon that 7% of workloads are virtualised, so there’s still a lot of consolidation work to be done, but how high up the list should SQL Server Analysis Services (SSAS) be?

In order to answer this it’s important to understand what SSAS is there to do, which is to provide a high performance ad hoc query environment for quantative analysis.  To do this effectively SSAS needs RAM and CPU as well as good connectivity, so in many BI projects analysis services is installed on a dedicated server specifically tuned for the task. 

So it is then important to understand what the motive is for Virtualisation:

a. We need to consolidate our server workloads

b. We want to leverage the ease of management that virtualisation can bring, by being able to bring up, roll back and turn off workloads.

c.  It’s new and shiny and everyone is doing it, so it must be good.

Applying this to analysis services

a.  If your cubes are of any size or are serving a large user community then you aren’t going to consolidate. Or you have a smaller business and you already have all the SQL Server features on one box to reduce licenses and fully utilise a single server.  Either way Virtualisation isn’t really going to help.

b. Updates and patching and switching can bring benefits to SSAS like any platform.

c. You will ignore this post and press on anyway.

I haven’t mentioned performance yet and I don’t believe there are any definitive figures on running SSAS on Hyper-V or any other virtualisation platform, but some customers have noticed problems running SSAS in a virtual environment, more around concurrency than response times.

So my question is do the benefits outweigh the disadvantages? In my opinion virtualisation of SSAS or indeed any OLAP using any virtualisation technology should be right at the end of your list of workloads to virtualise.  Firstly Virtualisation technology is moving on all the time, secondly as more and more people try this there will be more best practice around.

Having possibly put you off if you are considering doing this using the Microsoft platform (i.e. SSAS over Hyper-V) then the SQL Customer Advisory Team Team (SQLCAT) would love to hear from you so please contact me or them on there blog.