Sunday, August 12, 2007

SSAS Database Synchronization for Scalability & Availability

Database synchronization can be leveraged as a convenient solution to promote scalability and availability for the OLAP tier in a DWBI implementation.  It allows for a scale-out strategy by deploying copy of the SSAS database to multiple servers to load-balance user queries.  It increases availability by reducing the duration of time the SSAS database is offline or inaccessible due to processing.  In many scenarios, synchronization takes less time to perform than processing.

Traditionally, in Analysis Services 2000, synchronization implies backing up and restoring the SSAS database.  Nonetheless I had mixed success of getting msmdarch.exe to work properly with larger .cab files and across servers.  Analysis Services 2005 simplifies the task of synchronizing SSAS databases by making available the Synchronize XMLA command and the Synchronize Database Wizard which is a UI that ultimately creates and issues the Synchronize XMLA command.  Of course, in 2005, we can still back up and restore the .abf file.  But the synchronization capability is much more straight-forward.  

Several of our DWBI implementations take advantage of this synchronization capability Analysis Services 2005 has to offer.  Our solution performs processing of the cubes on a staging SSAS server and deploys (or sync's) the cubes to the presentation SSAS servers dedicated for user queries.  The diagram below illustrates the processing-flow of our solution.

Synchronization to the production SSAS servers is achieved by the means of the Synchronize XMLA command.  A DDL script in the form of XML specifies the command and all of its parameter elements (i.e. Source database, compression option) in an .xmla file.  (XMLA provides a declarative approach to specifying requirement and effectively eliminates the need of writing more complicated code - consequently enhancing the maintainability of the solution.) 

The Synchronize XMLA command is to be issued on the target SSAS server as opposed to the source SSAS server.  It can be manually executed in SSMS.  Or it can be run programmatically as in our solution using the Analysis Services Execute DDL task in a SSIS package. 

In order for the Synchronize command to work successfully, make sure that the Windows account under which the target SSAS server runs has administrative rights to the source database.  The target server executes the Synchronize command under its service account.


Bharath R S said...
This comment has been removed by a blog administrator.
Bharath R S said...
This comment has been removed by a blog administrator.
Bharath R S said...


During the synchronization process are the production cubes down?

Bharath R S

Amos said...

Hi Bharath,

I do believe so. Note that synchronization usually takes less time than processing as we are essentially just copying data (and metadata). Cheers.