Cube server migration
search cancel

Cube server migration

book

Article ID: 278475

calendar_today

Updated On:

Products

Information Centric Analytics

Issue/Introduction

In a two- or three-tier environment, you want to install a new instance of the Information Centric Analytics (ICA) cube on a new server but continue to use the existing application and database.

Environment

Release : 6.x

Component : Microsoft SQL Server Analysis Services

Resolution

Procedure

After installing Microsoft SQL Server Analysis Services (SSAS) in Multidimensional Mode on the new server and configuring the server's settings per the instructions in the ICA Administrator Guide, this procedure will require you to:

  1. Add the ICA service account to the Administrators group in SSAS
  2. Generate the XMLA deployment script for the RiskFabric cube from a working ICA installation
  3. Deploy the RiskFabric XMLA script on the new SSAS server
  4. (Optional) Update the data source connection string and impersonation information for the RiskFabric cube
  5. (Optional) Update the General portal setting Analysis Services Server
  6. (Optional) Update the data source connection string and impersonation information for the RiskFabric_ASDB linked server in SQL Server
  7. (Optional) Update the RiskFabric Processing and RiskFabric Intraday Processing jobs to point to the new SSAS server on all job steps of type SQL Server Analysis Services Command

NOTE: If the XMLA script is generated from the old SSAS server and the new SSAS server will retain the name and/or IP address of the old SSAS server, steps 4 through 6 can be omitted.

Add the ICA service account to the Administrators group in SSAS

  1. Open an instance of SQL Server Management Studio (SSMS) running under an account that has been assigned to the administrator role in SSAS
  2. Connect to the Analysis Services server hosting the RiskFabric cube
  3. In Object Explorer, right-click the server name/IP address and select Properties

    The Analysis Server Properties window appears

  4. Select the Security page
  5. If the ICA service account is in the list of Server administrators, proceed to step 7
  6. If the ICA service account is not in the list of Server administrators, add the account to the list
  7. Close the OK button to close the Analysis Server Properties window

Generate the XMLA deployment script for the RiskFabric cube from a working ICA installation

  1. Open an instance of SSMS running under an account that has been assigned to the administrator role in SSAS
  2. Connect to the Analysis Services server hosting your working RiskFabric cube
  3. In Object Explorer, navigate to Databases > RiskFabric
  4. Right-click the RiskFabric database and select Script Database as > CREATE To > File ...
    The Save As window will open
  5. Save the file in a location accessible for transfer to your new SSAS server
  6. Transfer the file to your new SSAS server

Deploy the RiskFabric XMLA script on the new SSAS server

  1. Open an instance of SSMS running under an account that has been assigned to the administrator role in SSAS
  2. Connect to the Analysis Services server hosting your production RiskFabric cube
  3. From the File menu, select Open > File...
    The Open File window will open
  4. Locate and select the XMLA script generated from your working RiskFabric environment
  5. If prompted to normalize line endings, click No
  6. Execute the script by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Execute from the Query menu
  7. In Object Explorer, right-click the Databases folder and select Refresh

(Optional) Update the data source connection string and impersonation information for the RiskFabric cube

  1. Open an instance of SSMS running under an account that has been assigned to the administrator role in SSAS
  2. Connect to the Analysis Services server hosting the RiskFabric cube
  3. In Object Explorer, navigate to Databases > RiskFabric > Data Sources
  4. Right-click RiskFabric and select Properties

    The Data Source Properties window appears

  5. Select the Connection String value and click the ellipsis to edit the string

    The Connection Manager window appears

  6. In the Server name field, enter the SQL Server hostname or IP address
  7. Select the RiskFabric database under the heading Connect to a database in the field under Select or enter a database name
  8. Click the OK button to save these changes and close the Connection Manager window
  9. Click the OK button to close the Data Source Properties window
  10. In Object Explorer, navigate to Databases
  11. Right-click RiskFabric and select Properties

    The Database Properties window appears

  12. Under Security Settings, click the ellipsis next to the Data Source Impersonation Info value

    The Impersonation Information window appears

  13. Select the option to Use a specific Windows user name and password and provide the ICA service account credentials
  14. Click the OK button to close the Impersonation Information window
  15. Click the OK button to close the Database Properties window

(Optional) Update the General portal setting Analysis Services Server

  1. Open an instance of SSMS running under an account that has been assigned the sysadmin role in SQL Server
  2. Connect to the Database Engine hosting the RiskFabric database
  3. From the File menu, select New > Query with Current Connection

    A new query editor window appears

  4. Copy the following block of statements and paste it into the new query editor window: 
    /* Metrics Table */
    UPDATE RiskFabric.dbo.Metrics
    SET ServerName = '<target server>';

    /* DetailsGridQueries */
    UPDATE RiskFabric.dbo.DetailsGridQueries
    SET [Server] = '<target server>';

    /* PortalSettings */
    UPDATE RiskFabric.dbo.PortalSettings
    SET ASDBServer = '<target server>';
  5. Modify each statement by replacing '<target server>' with the target (new) server name
  6. Execute the block of statements by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Execute from the Query menu

(Optional) Update the data source connection string and impersonation information for the RiskFabric_ASDB linked server in SQL Server

  1. Open an instance of SSMS running under an account that has been assigned the sysadmin role in SQL Server
  2. Connect to the Database Engine hosting the RiskFabric database
  3. In Object Explorer, navigate to Server Objects > Linked Servers
  4. Right-click the linked server RiskFabric_ASDB and select Delete
    The Delete Object window will open
  5. Click the OK button to delete the linked server
  6. In Object Explorer, right-click the folder Server Objects > Linked Servers and select New Linked Server
    The New Linked Server window will open
  7. On the General page, enter the following values:
    • Linked server: RISKFABRIC_ASDB
    • Server Type: Other data source
    • Provider: Microsoft OLE DB Provider for Analysis Services 14.0
    • Product name: Analysis Services OLEDB Provider
    • Data source: <SSAS server name or IP address>
    • Catalog: RiskFabric
  8. On the Security page, select the radio button next to Be made using this security context and enter the username and password of the ICA service account that has been assigned to the administrators group in SSAS
  9. On the Server Options page, ensure the following values are selected:
    • Collation Compatible: False
    • Data Access: True
    • RPC: False
    • RPC Out: True
    • Use Remote Collation: True
    • Collation Name: <no value>
    • Connection Timeout: 0
    • Query Timeout: 0
    • Distributor: False
    • Publisher: False
    • Subscriber: False
    • Lazy Schema Validation: False
    • Enable Promotion of Distributed Transaction: False
  10. Click the OK button to save the new linked server and close the New Linked Server window

(Optional) Update the RiskFabric Processing and RiskFabric Intraday Processing jobs to point to the new SSAS server on all job steps of type SQL Server Analysis Services Command

  1. Open an instance of SSMS running under an account that has been assigned the sysadmin role in SQL Server
  2. Connect to the Database Engine hosting the RiskFabric database
  3. In Object Explorer, navigate to SQL Server Agent > Jobs
  4. Right-click the job RiskFabric Processing and select Properties
    The Job Properties - RiskFabric Processing window will open
  5. Select the Steps page
  6. Select Risk Fabric cube processing (step 16) and click the Edit button
    The Job Step Properties - Risk Fabric cube processing window will open
  7. Edit the Server field to match the hostname or IP address of the new SSAS server
  8. Click the OK button to close the Job Step Properties - Risk Fabric cube processing window
  9. Repeat steps 6 through 8 for the following job steps:
    • Risk Fabric cube processing (16)
    • Process Collections and Scenarios and Risk Models Measure Groups (18)
    • Process Risk Vectors Measure Group (20)
  10. Click the OK button to save changes and close the Job Properties - RiskFabric Processing window
  11. Repeat steps 3 through 10 for the job RiskFabric Intraday Processing and the following job steps:
    1. Process DIM Measures and Dimensions (13)
    2. Process Event Scenario Measures and Dimensions (15)
Powered by Wolken Software