Once you have finished the development of Analysis Services project, there are several methods available for deploying analysis services objects from one server to other server. In this series, we will discuss the most commonly used deployment methods one by one. This article covers deployment using BIDS. The entire series covers deployment using:
Deployment with Synchronize Database Wizard
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.
TIP: To avoid any potential issues, please be sure to have both the servers at the same patch level (SP, MHF, etc) before running the synchronize database wizard.
To launch synchronize database wizard, connect to the analysis server using SQL server management studio, right-click the database folder and select Synchronize as shown in the figure below.
Figure 1
The Select Database to Synchronize dialog box appears. Type the source server name and source database name as shown below. If you wish to change the storage location for the database, click the ellipsis button corresponding to Storage location and navigate to the directory where you intend to store the target database. You need to run the wizard on the destination server as the wizard will automatically fill out the destination details (hard coded) as shown in the figure below. Click next after filling in the details.
Figure 2
The Specify Locations for Local Partitions dialog box will appear. Here you can change the storage locations for local partitions on the destination server and/or remote partitions on other destination server. If the default location is not changed, the wizard will copy the partitions to the same location on the target server and if there are any remote partitions, the target server will use the same remote partitions as the source server.
Figure 3
The Synchronize Options dialog box lets you specify security and compression settings as shown in the figure below. For better performance, leave the compression option checked.
Figure 4
The security options and their descriptions are as follows:
Copy all - This option will copy all the roles in the source database as well as all the windows user/group accounts associated with the roles. As indicated, if the roles or user accounts already exist on the target server, they will be replaced.
Skip Membership - This option will copy the roles in the source database, but it will not copy the associated user/group accounts.
Ignore all – This option will not copy any roles or user accounts from the source database.
In the Select Synchronization dialog box shown below, select Synchronize now to synchronize the databases immediately. You can also opt to create synchronization script to synchronize the database later. If you opt to create the synchronization script, the wizard will create an XMLA file and save it under the specified location. You can later execute the script in SQL server management studio or schedule it to run by creating a job using SQL agent.
Select the appropriate option and click next to see a summary of options you have chosen and then click finish.
Figure 5
You must have got this question by now: is it possible to use synchronize database wizard to upgrade an analysis services database from SSAS 2005 to 2008?
No, it is not possible to use the synchronize database wizard to synchronize data between SSAS 2005 and SSAS 2008. You can however, backup the analysis services 2005 database and restore it on SSAS 2008 to accomplish the same thing.
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.
You will also use this method when you don’t have the analysis services project file which gets created when the database is first developed in Business Intelligence Development Studio.
