Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Scaling Up and Scaling Out
You can scale up the Analysis Services solution by migrating your current databases to a more powerful server (with more memory, more/faster processors and faster disk drives). Scaling out can be accomplished by using several different approaches, each of which help separate processing and querying operations. You could use the following approaches for scaling out:
- Back up the database on processing server and restore to one or multiple querying servers
- Synchronize the database from processing to querying server(s)
- Detach the database from processing server, copy files, and attach to querying server(s)
The backup/restore solution requires copying every file each time you wish to have a copy of the database on multiple servers. Detach followed by attach would require copying all files or implementing file compare mechanism that detects the files that are different between two databases and only copying those files. Detach/Attach requires some downtime during which user queries will fail. Synchronization copies all database files the very first time it runs; all subsequent synchronizations to the same target database will only copy those files that have changed since the last successful synchronization. Essentially, synchronization does the file comparison and copying for you. While committing the synchronization transaction, there will be a brief period of time when user queries will not succeed. Unfortunately, synchronization can fail and cause target database corruption (particularly with early builds of MSAS 2005).
So which approach of scaling out is right for your organization? The answer is the resounding “it depends.” If you can afford to copy all files from source to target server and you would like to have the same solution for scaling up and disaster recovery, then backup/restore solution is a reasonable approach. If you want faster performance than backup/restore and you feel adventurous, you can use the detach/attach solution and come up with your own code for detecting and copying files that changed since the last copy.
Synchronization is fairly straightforward to implement, but has its fair share of imperfections. It may also make sense to use multiple solutions in tandem—use synchronization regularly but also backup databases on the target server and have backup files handy in case synchronization fails and causes corruption.