Authors: Eric Jacobsen, Denny Lee
Contributors: Mike Vovchik

Technical Reviewers: Guillaume Fourrat, Richard Tkachuk, Thomas Kejser

Applicable to: SQL Server 2005 Analysis Services

Introduction

As a quick follow up to the SQL Server Best Practices article Scale-Out Querying with Analysis Services, below is an example cmd script on how to robocopy the OLAP databases from one server to another.  As a quick refresher, within this whitepaper we called out the technique of using robocopy as a method to synchronize an OLAP database between servers as a viable alternative to the Analysis Services' synchronize method.  The advantages of using this approach includes the fact that you can copy the database to multiple servers in parallel and it can be faster provided that you are not blocked by the constraints noted below.  A disadvantage to this approach is that it will require stopping/restarting the Analysis Services service in order for the server to recognize the OLAP database once it has been copied over.  This approach is typically used within environments where there is only one AS database on the server, though it can work for multiple databases provided there are no issues with you shutting out user access to multiple databases to stop/restart the server. You can review the whitepaper linked above for more details.

Robocopy

You can get a copy of robocopy from the Windows Server 2003 Resource Kit Tools.  Typically in a simple network configuration, robocopy will use about 1/2 the available bandwidth which implies that you can optimally robocopy your OLAP database to two query servers in parallel. 

Important

Please note, you should not intermix the use of this robocopy technique with the AS Synchronize command.  Once you start using the robocopy method, it is advisable to stay with it as this is a one-way only synchronize technique (one source to multiple targets). 

Recall, the purpose of this technique is to provide you with a fast alternate method to provide query-only OLAP databases on separate AS instances.  The Analysis Services synchronize command does far more than just copy these Analysis Services files.  The command also scans metadata and touches all of the partitions of the two databases between the servers to ensure a two-way synchronization ability (source and target can interchange), regeneration of the .db.xml file (containing among other things the data source connection strings), and cryptokey.  As well, there are some features such as ROLAP, writeback, real-time updates, and some data mining features that cannot be cloned via the robocopy method (i.e. to use these features, you will need to use the AS Synchronize command).

Sample Script

The sample robocopy script is as can be seen below in which it is copying the various Analysis Services files with 21 processes in parallel (i.e. count the number of “start %cmd%” statements in the example script below). 

Please note, this script is provided "AS IS" with no warranties and confers no rights.  This is a sample example script of how to do custom synchronizing of Analysis Services databases.  In your environment you may want to use this as a template for your own script, add error handling, and reduce the number of threads copying in parallel.

REM
REM Synchronize OLAP Data Folder
REM
REM    Make a copy of the entire OLAP data folder
REM    Run many instances of robocopy in parallel to speed it up.
REM
REM    Author: Eric Jacobsen Jun-2006
REM    Update: Denny Lee, Thomas Kejser Jan-2008

setlocal
REM Enter Source Directory Here
set SRC=

REM Enter Destination Directory Here
set DST=

REM Enter Log file Here
SET LOG=

REM Robocopy Template
set CMD=robocopy %SRC% %DST%

REM Robocopy Options
REM   If you do not want a log file, remove the "/LOG+:%LOG%" below
set OPT=/Z /S /LOG+:%LOG%

start /MIN "Copying Flexible Aggregations" %CMD% *.agg.flex.data %OPT%
start /MIN "Copying Flexible Mapping Files" %CMD% *.agg.flex.map %OPT%
start /MIN "Copying Flexible Aggregation Headers" %CMD% *.agg.flex.map.hdr %OPT% 
start /MIN "Copying Fact Mapping Files" %CMD% *.fact.map %OPT% 
start /MIN "Copying Fact Mapping Headers" %CMD% *.fact.map.hdr %OPT% 
start /MIN "Copying Flexible Data Headers" %CMD% *.flex.data.hdr %OPT% 
start /MIN "Copying AH Store Files" %CMD% *.ahstore %OPT% 
start /MIN "Copying AS Store Files" %CMD% *.asstore %OPT% 
start /MIN "Copying AStore Files" %CMD% *.astore %OPT% 
start /MIN "Copying SStore Files" %CMD% *.sstore %OPT% 
start /MIN "Copying DStore Files" %CMD% *.dstore %OPT% 
start /MIN "Copying Fact Data Files" %CMD% *.fact.data %OPT% 
start /MIN "Copying Fact Data Header Files" %CMD% *.fact.data.hdr %OPT% 
start /MIN "Copying KH Store Files" %CMD% *.khstore %OPT% 
start /MIN "Copying KS Store Files" %CMD% *.ksstore %OPT% 
start /MIN "Copying KStore Files" %CMD% *.kstore %OPT% 
start /MIN "Copying LStore Files" %CMD% *.lstore %OPT% 
start /MIN "Copying OStore Files" %CMD% *.ostore %OPT% 
start /MIN "Copying SStore Files" %CMD% *.sstore %OPT% 
start /MIN "Copying XML Files" %CMD% *.xml %OPT% 
start /MIN "Copying All other Files" %CMD% *.* /xf *.agg.flex.data *.agg.flex.map *.agg.flex.map.hdr *.fact.map *.fact.map.hdr *.flex.data.hdr *.ahstore *.asstore *.astore *.sstore *.dstore *.fact.data *.fact.data.hdr *.khstore *.ksstore *.kstore *.lstore *.ostore *.sstore *.xml %OPT% 

As you can see with this script, you will need to provide the three entries of:

· SRC: The source AS data folder

· DST: The destination (target) AS data folder

· LOG: This is the log file just in case you want to record all of the files being copied. Note, since all 21 processes are running concurrently, your log files will be interlaced with copy completions from all 21 robocopy executions. To turn off the log file, please go to the next line and remove the “/LOG+:%LOG%” statement.

Above is a screenshot of the robocopy method to synchronize the two different Analysis Services databases. As you can see, the cmd window executes the 21 separate processes, 19 of which are showing up within the Task Manager and minimized within the 19 robocopy cmd windows.

Closing Remarks

As noted in the introduction, this robocopy method of synchronizing database is an alternative to the AS Synchronize method. It is very effective for the specific scenarios described within the Scale-Out Querying with Analysis Services whitepaper. But before using this method, remember that this method does have some limits (query only, one-way sync only, etc.) and should never be used intermixed with the AS Synchronize method.