When business users accessing a SSAS cube by connecting via excel or any other front end tool and at the same time if you process the cube then user can be affected badly. User simply can't access the cube if you are processing the same.
To avoid the above scenario, we can have two different SSAS server, one for Processing and other for Query. Business user should connect the Query server. So while you processing the cube it will update the Processing server; so user can't be affected while you are processing the cube since user will be connected with Query server.
Today's writing will cover how to sync cube from one server to other.
Step 1: Find the Query server where you want to sync.
Click at the database of the server and then click Synchronize as like below fig 1
Server->Database->Synchronize
Step 2: Synchronize wizard
At this step it explains what do you with the wizard
Step 3: Choose the server from where you would like to sync which can be your processing server and Destination server will be automatically chosen.
Step 4: Specify location for the partition, you can leave as default
Step 5: Choose security roles and members
To avoid the above scenario, we can have two different SSAS server, one for Processing and other for Query. Business user should connect the Query server. So while you processing the cube it will update the Processing server; so user can't be affected while you are processing the cube since user will be connected with Query server.
Today's writing will cover how to sync cube from one server to other.
Click at the database of the server and then click Synchronize as like below fig 1
Server->Database->Synchronize
Fig 1: Sync to the Query server |
Step 2: Synchronize wizard
At this step it explains what do you with the wizard
Fig 2: synchronize wizard |
Step 3: Choose the server from where you would like to sync which can be your processing server and Destination server will be automatically chosen.
Fig 3: Choose source and Destination to Sync |
Step 4: Specify location for the partition, you can leave as default
Fig 4: location for partition |
Step 5: Choose security roles and members
You need to take this part as per your need, if you don't want to copy any role and security from the processing server then you can choose third option 'Ignore all'. And if you want role need to copied but not members then choose 2nd option.
You may choose Synchronize now, however; you can copy the script so that you can add the script to a SQL Server Agent job.
Fig 5: Choose what to Copy from processing Server |
Step 6: Save script or Synchronize
At this last step, you can either start synchronize or generate the script to save in a file for later uses.
Fig 6: Last step of synchronization |
You may choose Synchronize now, however; you can copy the script so that you can add the script to a SQL Server Agent job.
Why do you create SQL Server Agent job?
If you need to sync the cube from Processing server to Query server time to time then creating job will be the best solution. Hence, the script saved from step 6 can be used to create a job. And whenever you need to sync the cubes you can run the job.