Remote Partitions

When partitioning a measure group, you can configure a secondary database on a remote Analysis Services instance as partition storage.

Remote partitions for a cube (called the master database) are stored in a dedicated Analysis Services database on the remote instance of Analysis Services (called the secondary database).

A dedicated secondary database can store remote partitions for one and only one master database, but the master database can use multiple secondary databases, as long as all the secondary databases are on the same remote instance of Analysis Services. Dimensions in a database dedicated to remote partitions are created as linked dimensions.

Before you create a remote partition, the following conditions must be met:

• You must have a second Analysis Services instance and dedicated database to store the partitions. The secondary database is single-purpose; it provides storage of remote partitions for a master database.
• Both server instances must be the same version. Both databases should be the same functional level.
• Both instances must be configured for TCP connections. Analysis Services does not support creation of remote partitions by using the HTTP protocol.
• Firewall settings on both computers must be set to accept outside connections. For information about setting the firewall
• The service account for the instance running the master database must have administrative access to the remote instance of Analysis Services. If the service account changes, you must update permissions on both the server and database.
• You must be an Analysis Services administrator on both computers.
• You must ensure your disaster recovery plan accommodates backup and restore of the remote partitions. Using remote partitions can complicate backup and restore operations. Be sure to test your plan thoroughly to be sure you can restore the necessary data.

Configure remote partitions

Two separate computers that are running an instance of SQL Server Analysis Services are each required to create a remote partition arrangement that designates one computer as the master server and the other computer as the subordinate server.

The following procedure assumes that you have two server instances, with a cube database deployed on the master server. For the purposes of this procedure, the cube database is referred to as db-master. The storage database containing remote partitions is referred to as db-storage.

You will use both SQL Server Management Studio and SQL Server Data Tools (SSDT) to complete this procedure.

Note:

Remote partitions can be merged only with other remote partitions. If you are using a combination of local and remote partitions, an alternative approach is to create new partitions that include the combined data, deleting the partitions you no longer use.

Specify valid server names for cube deployment (in SSDT)

• On the master server: In Solution Explorer, right-click the solution name and select Properties. In the Properties dialog box, click Configuration Properties, then click Deployment, and then click Server and set the master server's name.
• On the subordinate server: In Solution Explorer, right-click the solution name and select Properties. In the Properties dialog box, click Configuration Properties, then click Deployment, and then click Server and set the subordinate server's name. 

Create and deploy a secondary database (in SSDT)

• On the subordinate server: Create a new Analysis Services project for the storage database.
• On the subordinate server: In Solution Explorer, create a new data source pointing to the cube database, db-master. Use the provider Native OLE DB\Microsoft OLE DB Provider for Analysis Services 11.0.
• On the subordinate server: Deploy the solution.