- Synchronization Methods
- Synchronization Command
- Synchronization Internals
- Synchronization Performance and Troubleshooting
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Synchronization Performance and Troubleshooting
As mentioned earlier, synchronization can be considerably faster than the backup and restore method because it is incremental by design. However, due to the single-threaded file copy phase, synchronization might not be the most efficient way of transferring files. In fact, it's not uncommon to see synchronization failures while copying large files. Pay attention to the Analysis Services configuration settings <Network><ServerSendTimeout> and <Network><ServerReceiveTimeout>; all configuration settings are stored in the msmdsrv.ini file found in the configuration folder of your SSAS installation. By default, both settings have a value of 60 seconds (or 60,000 milliseconds). If copying a file takes longer than 1 minute, your synchronization command will fail. SSAS files can grow to a large size measured in gigabytes, and on a slow network it could easily take longer than a minute to transfer a 5GB file. Therefore, if you use synchronization and have large SSAS files, then you should increase the values of mentioned configuration settings up to something more reasonable, perhaps 5 or 10 minutes instead of 1 minute.
It should come as no surprise that a reliable network connection is essential for a synchronization's success and optimal performance. If the network is unreliable and some packets are dropped, synchronization will fail. Analysis Services will report an error stating "the peer prematurely closed the connection." If you do expect issues with the network, you might opt for a different option for synchronizing your databases.
Another configuration option that we must watch out for is lazy aggregations. This setting controls how Analysis Services computes aggregations on a given instance and database. If you use the ProcessUpdate method for refreshing your dimension data, all flexible index and aggregation files will be deleted. You can either manually reprocess the missing indexes and aggregations (by employing the ProcessIndexes option), or SSAS will automatically detect that such files are missing and will try re-creating the files using the background "lazy" thread. This can conflict with the synchronization command's commit phase. The synchronization will try to replace the existing database folder with an updated folder while the "lazy" thread is updating the existing folder with aggregations and indexes. If you plan to use synchronization, it's best to disable lazy processing altogether by setting the <LazyProcessing><Enabled> configuration property to 0 (the default value is 1).
As with any database topic, we should consider factors affecting synchronization performance. Unfortunately, there aren't any configuration tweaks we could apply to make synchronization faster. But if you continuously monitor synchronization using SQL Profiler, you'll notice that the execution time depends heavily on the number of files being transferred as well as the total size of these files. There are some tradeoffs we can make to speed up synchronization:
- If data latency on querying servers is the primary concern, and MDX query performance isn't an as big of an issue, then we could synchronize the database to the querying instance first and later build aggregations on the target instance. Clearly the queries will perform suboptimally until the missing aggregations are computed.
- If you have many historical partitions (each with many files) that are seldomly queried, you could merge them into a single partition prior to running the synchronization command, thereby reducing the total number of files that must be transferred.
- Consider whether to use ProcessAdd or ProcessUpdate for refreshing dimensions. ProcessAdd only imports new dimension members and accounts only for INSERT statements in the relational dimension table. ProcessAdd does not affect any existing dimension values and hence does not require reprocessing indexes or aggregations. As discussed earlier, ProcessUpdate is more intrusive because it is more thorough than ProcessAdd; it accounts for INSERT, UPDATE, and DELETE statements. If you anticipate relatively few UPDATE and DELETE commands in your relational dimensions, then you could use ProcessAdd throughout the day, thereby minimizing the number of files that must change/be synchronized to querying instances. ProcessUpdate followed by ProcessIndexes could be exploited less frequently, perhaps after business hours or during non-peak user activity, when we can afford for synchronization to take longer.
Summary
If a business intelligence solution must serve many users, you may have to peruse scale-out architecture by processing the SSAS database objects on one instance and synchronizing the database to multiple querying instances. We have several options for ensuring that multiple SSAS instances have an identical copy of the same database: We could use backup and restore, built-in synchronization, or detach and attach methods. The built-in synchronization is relatively easy to implement because it only requires a single XMLA command. Synchronization is incremental and only copies the files that have changed since previous successful execution of the command.
The feature has come a long way since its original debut in SSAS 2005. It is faster and more reliable in later versions; however, file copy phase is still single-threaded, so detach/Robocopy/attach method could outperform built-in synchronization. To optimize synchronization performance, you should minimize the number of files and the total size of files to transfer. Keep in mind that synchronization requires instance administrator privileges on both source and target instances.