High Availability in Analysis Services

A typical approach to high availability in Analysis Services 2000 is to use Windows Network Load Balancing (NLB) to distribute user queries across multiple Analysis Services instances on disparate machines while also increasing availability. You keep the databases on these machines in sync with file-based backup and restore (required for large databases due to the 2GB .cab file size limitation) from a secondary server on which cube and dimension processing is performed. For more information, read the Microsoft white paper "Creating Large-Scale, Highly Available OLAP Sites" at http://www.microsoft.com/sql/evaluation/bi/creatingolapsites.asp.

Even though Analysis Services 2000 isn't cluster-aware, you can cluster an Analysis Services 2000 database. SQL Server 2005 Analysis Services is cluster-aware and fully supports active-active clustering, which means you can create a failover cluster to ensure high availability. In addition, Analysis Services 2005 has a server-synchronization feature, which lets you incrementally synchronize metadata and data changes between a source database and a destination (production) database while users continue to query the destination database. Unlike Analysis Services 2000, with Analysis Services 2005 you can't simply copy the data files from the data folder of an Analysis Services 2005 instance on one machine to the data folder on another machine. These files are encrypted by default (using the machine name as part of the key) unless you modify the .config file to change the RequiredProtectionLevel setting from 1 to 0. Finally, in Analysis Services 2005, you can back up any size database (the 2GB .cab file limit has been removed), which means you can use back up and restore to move any size database between servers.