Scaling Out with Analysis Services

A few months ago, I had the opportunity to work with a customer who had been intermittently encountering a variety of errors when attempting to connect to an Analysis Services server. The production environment consisted of a dedicated processing server and a dedicated query server. Both of the servers were configured with eight (8) logical cores and 64GB of RAM and were running SQL Server 2008 R2 Analysis Server. Owing to a business requirement that the data be regularly refreshed at 15 minute intervals, the processing server was used to execute a full process of a single database every 15 minutes. Since the query server was not used for processing, the database was being refreshed using the Synchronization feature that is native to Analysis Services. All-in-all, this is a recognized practice for scaling out Analysis Services that was described by Denny Lee and Nicholas Dritsas in 2007 in their whitepaper entitled SQL Server Best Practices Article.