Create and Manage a Remote Partition (Analysis Services)

Artikkeli
12/09/2022

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

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

Remote partitions for a cube (called the master database) are stored in a dedicated SQL Server Analysis Services database on the remote instance of SQL Server 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 SQL Server Analysis Services. Dimensions in a database dedicated to remote partitions are created as linked dimensions.

Prerequisites

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

You must have a second SQL Server 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. SQL Server 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, see Configure the Windows Firewall to Allow Analysis Services Access.
The service account for the instance running the master database must have administrative access to the remote instance of SQL Server Analysis Services. If the service account changes, you must update permissions on both the server and database.
You must be an SQL Server 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 SQL Server Analysis Services are each required to create a remote partition arrangement that designates one computer as the primary server and the other computer as the secondary server.

The following procedure assumes that you have two server instances, with a cube database deployed on the primary 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 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 primary 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 primary server's name.
On the secondary 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 secondary server's name.

Create and deploy a secondary database (in SSDT)

On the secondary server: Create a new Analysis Services project for the storage database.
On the secondary 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 secondary server: Deploy the solution.

Enable features (in SSMS)

On the secondary server: In SQL Server Management Studio, right-click your connected SQL Server Analysis Services instance in Object Explorer and select Properties. Set both Feature\LinkToOtherInstanceEnabled and Feature\LinkFromOtherInstanceEnabled to True.
On the secondary server: Restart the server by right-clicking the server name in Object Explorer and selecting Restart.
On the primary server: In SQL Server Management Studio, right-click your connected SQL Server Analysis Services instance in Object Explorer and select Properties. Set both Feature\LinkToOtherInstanceEnabled and Feature\LinkFromOtherInstanceEnabled to True.
On the primary server: To restart the server, right-click the server name in Object Explorer and select Restart.

Set the MasterDataSourceID database property on the remote server (in SSMS)

On the secondary server: Right-click the storage database, db-storage, point to Script Database as | ALTER To | New Query Editor Window.

Add MasterDataSourceID to the XMLA, and then specify the cube database, db-master, ID as the value. The XMLA should look similar to the following.

<Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
<Object>  
   <DatabaseID>DB-Storage</DatabaseID>  
</Object>  
<ObjectDefinition>  
   <Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" 400"   
      <ID>DB-Storage</ID>  
      <Name>DB-StorageB</Name>  
      <ddl200:CompatibilityLevel>1100</ddl200:CompatibilityLevel>  
      <Language>1033</Language>  
      <Collation>Latin1_General_CI_AS</Collation>  
      <DataSourceImpersonationInfo>  
<ImpersonationMode>ImpersonateAccount</ImpersonationMode>  
         <Account>*********</Account>  
      </DataSourceImpersonationInfo>  
      <MasterDataSourceID>DB-Master</MasterDataSourceID>  
   </Database>  
</ObjectDefinition>  
</Alter>

Press F5 to execute the script.

Set up the remote partition (in SSDT)

On the primary server: Open the cube in Cube Designer and click Partitions tab. Expand the measure group. Click New Partition if the measure group is already configured for multiple partitions, or click the browse (. . ) button in the Source column to edit the existing partition.
In the Partition Wizard, in Specify Source Information, select the original Data Source View and fact table.
If using a query binding, provide a WHERE clause that segments the data for the new partition you are creating.
In Processing and Storage Locations, under Processing Location, choose Remote Analysis Services data source and click New to create a new data source that points to your subordinate database, db-storage.

Note

If you get an error indicating the data source does not exist in the collection, you must open the project of the storage database, db-storage, and create a data source that points to the master database, db-master.
On the primary server: Right-click the cube name in Solution Explorer, select Process and fully process the cube.

Administering remote partitions

SQL Server Analysis Services supports both parallel and sequential processing of remote partitions. The master database, where the partitions were defined, coordinates the transactions among all the instances that participate in processing the partitions of a cube. Processing reports are then sent to all instances that processed a partition.

A cube that contains remote partitions can be administered together with its partitions on a single instance of SQL Server Analysis Services. However, the metadata for the remote partition can be viewed and updated only on the instance of SQL Server Analysis Services where the partition and its parent cube were defined. The remote partition cannot be viewed or updated on the remote instance of SQL Server Analysis Services.