Data Warehousing and Machine Learning

27 October 2013

Can I Cluster SSAS? (How to minimize downtime during deployment)

Filed under: Analysis Services — Vincent Rainardi @ 9:37 am

Some people asked me if we can cluster SSAS. Yes we can cluster SSAS. SSAS supports clustering natively. This is since 2008 R2. Meaning that, if Server1 dies, clients will automatically access Server2. Just need to Refresh from Excel, SCA, Tableau etc and results will be returned. How does it work in the background? Same as Database Engine: 1 set of disks, 2 servers.

The real question is how do I handle huge AS cubes without any downtime when I do deployment? Because the issue is: when the new version of the cubes are being processed, they are down, i.e. users can’t access the cube. And the Full Process of the cubes can take many hours.

First, let me clarify the incorrect popular perception that “if we Process Full the SSAS database, users can’t access the cube”. That is incorrect. Provided that the structure of the cube is the same, we don’t have a new version, then Process Full on DB does not bring the cube down. Users can access the cube when the DB is being full-processed. This is because Process Full on DB is done in one transaction (read about managing XMLA transaction here). Thank you Darren.

But during deployment we have a new version, and the structure of the cube changes. So the above doesn’t apply. We will have a down time when we process the DB after deployment. So here’s the solution:

Microsoft provides a detailed guide on how to minimize downtime during deployment here. However, this requires extra hardware. Lots of extra hardware and software, which means additional investment. Essentially the process is:

  1. Deploy and full process new version of the cube to a separate SSAS instance (“Processing Server”)on a separate LUN, then detach the DB.
  2. Stop NLB traffic, detach the original DB
  3. Swap the original LUN with the new LUN.
  4. Bring the new LUN online as read only.
  5. Attach the DB as read only, re-enable NLB.
  6. Refresh the old LUN from the new LUN.
  7. Attach the old DB on the old LUN to the Processing Server.

A more economical way (no additional server required) is to deploy as Cube 2, process full, delete the old Cube and rename the cube. This way, the downtime decreases from hours to seconds.

Processing SSAS Cubes using PowerShell


Processing SSAS Cubes using PowerShell

There are 3 ways to process SSAS cubes using PowerShell:














  1. Darren Gosbell, Using PowerShell to execute MDX.
  2. Microsoft, Analysis Services PowerShell
  3. Microsoft, XMLA in SSAS.
  4. Daniel Calbimonte, Using Powershell to do AS Tasks
  5. The MAK, SQLAS (Analysis Services PowerShell Provider)
  6. Richard Lee, find unprocessed objects using PowerShell
  7. Keith Babinec, PowerShell Error Handling.
  8. Microsoft, PowerShell User’s Guide
    Also see basics of AMO programming
  9. SSAS-Info, PowerShell AMO
  10. Sandeep iii, Processing Cubes using AMO PS.

Blog at