MSBI (SSIS/SSRS/SSAS) Online Training

Thursday, February 24, 2011

SSAS-Implementing Analysis Services synchronization

Synchronization is a fairly simple concept resembling the snapshot replication feature available with a SQL Server relational database engine. Synchronization copies the data files from the source server to the destination server. For example, you could synchronize an Adventure Works sample database from server A to server B. If server B (destination) does not have this database yet, it will be created during synchronization. If server B already has the Adventure Works database, it will be overwritten.
If the destination database exists, it remains online while you synchronize it, so your users can continue querying the database. Analysis Services creates a separate folder in the data directory of the destination server. This folder will have a globally unique identifier (GUID) as its name -- for example 761A1D04B1C476A9886 -- and will contain only files that were modified since databases were last synchronized. In other words, synchronization is always incremental.
Note, however, that before incremental changes can be transferred you must first synchronize the entire database. Once you synchronize a primary database from a secondary database, you will only have to copy a subset of all files for subsequent synchronizations. The first synchronization must copy all files, even if the existing copies of data on both servers are identical. Keep in mind that since all files must be copied for the initial synchronization, you will need plenty of disk space to store the existing database files and those files that are being copied from the standby server.
After all necessary files are copied to the synchronization folder, Analysis Services deletes the existing folder, storing the current database files, and replaces it with the folder containing synchronized files. The change occurs very fast because it's a matter of renaming the folder from GUID to the database name (and version number). Users can continue querying the primary database, although queries will fail for a brief period while the folder is being renamed.
For example, the following command synchronizes a test database between two instances of Analysis Services:

Exploiting Analysis Services synchronization
You can use the SYNCHRONIZE command to:
Have a standby Analysis Server dedicated to processing. The primary production server will be dedicated to user queries. After you process cubes on the standby server, you can transfer modified data files to the production server.
Deploy changes from the development environment to production servers. You can include the security settings that exist on the development database or keep those settings that are defined in production. If the development database only has a subset of production data, then you will need to reprocess the production database once synchronization is complete.
Keep multiple copies of the same database on multiple servers for scaling out your analytical solution. If there are thousands of users querying your cubes, you can direct a subset of users to each server.
Keep multiple copies of the same database on multiple servers to assure high availability. Should any of your servers experience hardware issues, you could redirect users' queries to other servers.
Copy the production database to development or quality assurance servers when you need to troubleshoot performance or data accuracy issues.
Here is the screen shots for oyur referrence.


You can get more details from the below page:
http://searchsqlserver.techtarget.com/tip/Synchronizing-Analysis-Services-2005-databases-in-SQL-Server

1 comment:

Shikha said...

Hi,

I tried synchronization using SQl Server Analysis Services 2008 R2. I did full sync of cube of size 18.8 GB . It took around 9.59 mins.
Added 3 new partions size increased to 20 GB and again did synchronization. It took almost same time. I'm not getting how it is working as incremental sync. The command which I'm providing is same and when I checked how its working , while incremental sync it is creating new folder having globally unique identifier (GUID) as its name like 00B1234476A9886. It copied all the files along with new one and finally it verify existing with new one and delete the existing and copy all files from the folder it created i.e 00B1234476A9886 and delete the folder (00B1234476A9886).

Let me know where I'm missing as Incremental sync is not satisfying my condition.

Even after full sync if I again start sync it takes same time. But this time there is no change. Why it is so ?