- 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 Command
Much like other XMLA commands, you can script the synchronize statement directly from SQL Server Management Studio (SSMS). Right-click the databases folder on the synchronization target server and choose Synchronize to activate the Synchronize Database Wizard. The wizard will let you input the source server and database name, choose whether you wish to copy all security roles from source to target instance, and determine whether data files should be compressed during synchronization. The wizard allows specifying a file where the XMLA command should be saved or executing the command right away. The basic synchronization statement will have the following syntax:
<Synchronize xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Source>
<ConnectionString>
Provider=MSOLAP.4;Data Source=HostName\SourceInstanceName;ConnectTo=10.0;Integrated Security=SSPI;Initial Catalog=DatabaseName
</ConnectionString>
<Object>
<DatabaseID>DatabaseIdentifier</DatabaseID>
</Object>
</Source>
<SynchronizeSecurity>CopyAll</SynchronizeSecurity>
<ApplyCompression>true</ApplyCompression>
</Synchronize>
This statement must be executed on the synchronization target instance to copy necessary files from the source instance. Note that each statement can only synchronize one database to one SSAS instance; if you have multiple instances in your query load balancing pool, then you must execute the same statement on each target instance. Fortunately, you can synchronize the same database to multiple target instances in parallel.
The previous XMLA statement applies compression to files while they're synchronized and copies all security roles along with their members. Other security options include transferring just the roles without including role members or ignoring security roles altogether. The latter option could be useful if you're transferring the production database to a test or quality assurance server where the database will be queried by a different set of users.
While we're on subject of security, let me inform you that only members of Analysis Services server role (in other words, SSAS instance administrators) can run synchronization. You can check the membership of Analysis Services server role by right-clicking on the instance in SSMS, choosing Properties, and navigating to the Security tab of the Analysis Services Properties dialog (see Figure 1).
)
Once you have appropriate permissions, you can run the Synchronize command on the target server through SSMS or by using ASCMD command-line utility. SSMS displays the progress of execution, as shown in Figure 2.
)
The same dialog reports any errors that might occur during the synchronization and whether synchronization succeeded or failed. SSMS provides additional insight into what takes place behind the scenes; here is an output from a synchronization command that copies only part of all database files:
Backup metadata traversal started. Backup metadata traversal finished. Synchronization started. Synchronization finished.
To fully grasp the synchronization feature, let's recall that Analysis Services is a file-based data store; each database could have thousands of files. For example, the Adventure Works DW sample database contains 8012 files in 77 folders, as shown in Figure 3.
)
Each file is specific to an object, such as a dimension or partition, and stores a specific piece of data. For example, each dimension attribute will have a key store file containing attribute key values.
Built-in synchronization's primary advantage over other methods is its incremental nature: If the target instance already contains the database that we're trying to synchronize, SSAS will only copy the files that do not exist on the target server. For example, if our daily processing involves updating only one partition "Internet_Sales_2001", then synchronization will only transfer a relatively small number of files (that changed as part of "Internet_Sales_2001" partition processing) rather than having to copy all database files. Additionally, synchronization will also remove the files that exist on the target server but not on the source server. If the target database does not exist, then synchronization has no choice — it will have to copy every single file found in the source database folder.