Analysis Services 2005 (and later versions) support synchronization, a feature that greatly simplifies migrating analytical databases among servers. It also eases the separation of querying and processing duties among multiple instances of Analysis Services. In this tip, I'll introduce you to how synchronization is implemented and how to use this powerful feature for scaling your analytical applications.
The Synchronize Database Wizard copies both metadata and data from a database on source server to a database on target server. If the database doesn’t exist on the target server, the Wizard will copy the entire database else the wizard will only copy changes.
Though this method is kind of similar to Backup/Restore method, using Synchronize database method will let you do the operation in a single step without having to manually manage backup files.There are four main techniques for synchronizing an Analysis Services database from one server to another. In general the attach/detach, backup/restore, and synchronize methods are great for individual database synchronizations.
- Analysis Services Synch Method-What is great about this particular method is that it is easy to operate and does not require a lot of development to get it to work in your production environment-This process scans the differences between two databases on the two servers and transfers over only files that have been modified.-performance of this new synchronization is quite impressive and is due to the changes to the underlying file system as part of Analysis Services 2008.
- Backup/Restore Database-The backup/restore method is the most common data recovery method for an Analysis Services database and can be adapted as a synchronization technique-You can copy it to multiple target servers, and then execute the restore method on to your target servers.-The advantage of this approach is that if your restoration of the database fails, users can still query the original database.
- Attach/Detach Database-As part of SQL Server 2008 Analysis Services , you have the option to attach/detach your Analysis Services database and set it to read-only.-The advantage of this approach is that while you have to robocopy the full database, this involves simply attaching a database
- Robocopy Method-The idea is to use a fast copy utility, such as Robocopy, to copy the changed or added (delta) files within the Analysis Services data folder. A limiting factor is that you must copy all of the files within this data folder, which means that you will be copying all of the OLAP databases on the processing server-SSIS package can execute multiple threads of Robocopy to copy the entire Analysis Services data folder (by default, this is C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data) from your Analysis Services processing server to the idle instance data folders of the query servers.
When to use Synchronize Database Wizard
Use this wizard for deployment when you can’t afford to process your analysis services database on a production server. For example, let’s say that you have a farm of production servers and you want to deploy the changes to each server; Instead of processing the analysis services database on each server, it would be more efficient to process the database on a dedicated staging server and then synchronize each of the production servers with the staging server.
Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2
No comments:
Post a Comment