Database and cube server migration
search cancel

Database and cube server migration

book

Article ID: 375680

calendar_today

Updated On:

Products

Information Centric Analytics

Issue/Introduction

This document outlines the process for migrating an existing deployment of Symantec Information Centric Analytics' (ICA) relational database and OLAP cube to new servers. This document is particularly appropriate for customized deployments of ICA.

Resolution

Table of Contents

Migration Overview

For any migration, it is important to ensure that the source and target versions (major, minor, and build; for example: 6.6.10200) of ICA and its components are matched exactly. This will minimize the likelihood of incompatibilities once the source files are deployed to the target environment. If you wish to perform an upgrade in conjunction with a migration, upgrade the target environment post-migration.

Follow this procedure to determine the version of ICA and its ancillary utilities in your source environment:

  1. Open SQL Server Management Studio (SSMS)
  2. Connect to the Database Engine hosting the RiskFabric relational database
  3. From the File menu, select New > Query with Current Connection
    A new query editor window opens
  4. Copy the following query and paste it into the new query editor window: 
    SELECT * FROM RiskFabric.dbo.Versions;
    NOTE: if you named the database something other than the default name (RiskFabric), you must modify this query to use the database name you selected
  5. Execute the query by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Execute from the Query menu
  6. If you have installed the Active Directory importer, repeat steps 3 through 5, replacing the query on step 4 with the following query: 
    SELECT * FROM ActiveDirectoryDW.dbo.BayDynamics_ADConnector_System;

Note the Database and Application versions with the most recent CreatedDate values returned by the first query. Download the installation package matching this application version from Broadcom's support website.

Preparing the database and utilities for migration

Database (Microsoft SQL Server) Server

Follow each of these procedures to create backup copies and CREATE scripts of the RiskFabric database, data source linked server(s), SQL Server Agent jobs, and SQL Server Agent proxies.

Database

  1. Open SQL Server Management Studio (SSMS)
  2. Connect to the Database Engine hosting the RiskFabric relational database
  3. In Object Explorer, navigate to the Database folder
  4. Right-click the RiskFabric database and select Tasks > Back Up...
    The Back Up Database window opens
  5. Click the OK button to start the backup creation process and close the Back Up Database window

Repeat this procedure for each integration's data warehouse (DW) in your environment (for example, ActiveDirectoryDW).

Linked Servers

The linked server RiskFabric_ASDB does not need to be backed-up. For all other linked servers, follow this procedure:

  1. In Object Explorer, navigate to Server Objects > Linked Servers
  2. Right-click a linked server and select Script Linked Server as > CREATE To > File
    The Save As window opens
  3. Select a destination location to save the script and assign it a uniquely identifiable name
  4. Repeat steps 2 and 3 for all other RiskFabric linked servers, naming each per the data source it represents (for example: RISKFABRIC_DLP.sql)

SQL Server Agent jobs

For each job with the naming prefix RiskFabric_ and the Bay Dynamics AD Connector Job (if installed), follow this procedure:

  1. In Object Explorer, navigate to SQL Server Agent > Jobs
  2. Right-click the RiskFabric Processing job and select Script Job as > CREATE To > File ...
    The Save As window opens
  3. Name the file RiskFabric_Processing.sql and save the file
  4. Repeat steps 2 and 3 for each job, naming each file to match the job

If you have created SQL Server Agent jobs for other data sources (for example: CloudSOC Import Job), follow the procedure above to generate CREATE scripts for these jobs.

SQL Server Agent proxies

  1. In Object Explorer, navigate to SQL Server Agent > Proxies > Operating System (CmdExec)
  2. Right-click the RiskFabric Nightly Processing proxy and select Script Proxy as > CREATE To > File ...
    The Save As window opens
  3. Name the file RiskFabric_Nightly_Processing.sql and save the file
  4. Repeat steps 2 and 3 for the Bay Dynamics AD Connector Proxy (if exists) and the Qualys Guard Data Import proxy (if exists)
  5. In Object Explorer, navigate to SQL Server Agent > Proxies > Analysis Services Command
  6. Right-click the RiskFabric Proxy and select Script Proxy as > CREATE To > File ...
    The Save As window opens
  7. Name the file RiskFabric_Proxy.sql and save the file

Preparing the new database server

In order to migrate the RiskFabric relational database and OLAP cube to new servers, additional ancillary objects must be created on the new servers that are not included in a backup of the database. The simplest method for creating these objects is to run the ICA installer to create a second (temporary) instance of the RiskFabric site that points at the new database and cube servers. After the database, cube, and ancillary objects are created and the original database backup is restored, the original RiskFabric site's connection string will be updated to point to the new relational database server and the temporary RiskFabric site will be deleted.

Follow this procedure on the application (IIS) server to create ancillary objects on the new SQL Server and Analysis Services host servers:

  1. Install all prerequisite software components on the relational database and OLAP cube servers (refer to the Server Requirements for Symantec ICA section of the Symantec ICA Administrator Guide)
  2. Run the Symantec ICA installer that matches the version installed on the old servers and select these options during the installation process:
    1. Specify an alternative website name (for example, RiskFabric2)
    2. Specify an alternative port for the site (for example, 8080)
    3. Specify an alternative installation directory for the site files (for example, C:\Program Files\Bay Dynamics\Risk Fabric Server Web 2)
    4. Create a new RiskFabric database on the new server hosting Microsoft SQL Server (MSSQL)
    5. Create a new RiskFabric database on the new server hosting Microsoft SQL Server Analysis Services (SSAS)
    6. Install the Database Utilities on the new SQL Server host server
  3. After installation has completed, open Internet Information Services (IIS) Manager
  4. In the Connections pane, navigate to <hostname> > Sites
  5. Select the original RiskFabric site
  6. In the Actions pane under Manage Website, select Stop

Database (Microsoft SQL Server) Server

Database

Follow this procedure to prepare the new server that will host SQL Server and the RiskFabric relational database:

  1. If ICA has been or will be integrated with Symantec DLP, install the latest Oracle Client (refer to the Installing the Oracle Database Client for Microsoft Windows (x64) section of the Symantec ICA Integration and Solution Accelerator Guide for instructions)
  2. Open SQL Server Management Studio (SSMS)
  3. Connect to the Database Engine hosting the RiskFabric relational database
  4. In Object Explorer, navigate to the Databases folder
  5. Right-click the RiskFabric database and select Delete
    The Delete Object window opens
  6. Click the OK button to delete the database and close the Delete Object window

Database Utilities

  1. Copy the following folder and its contents from the old SQL Server host to the target location on the new SQL Server host, overwriting any existing files: 
    <path>\Bay Dynamics
    This folder is installed in the following path by default: 
    %SystemDrive%\Program Files
    NOTE: Use the same path on the new server as was used on the old server. If you intend to change the path, note that some SQL Server agent job steps and the Database Utilities path in the application's general settings will need to be updated to point to the new path.
  2. Compare the permissions assigned to the folder and those inherited by its contents on the new server to those assigned on the old server

Migrating the source application, database, and utilities

Database (Microsoft SQL Server) Server

Database

  1. Open SQL Server Management Studio (SSMS)
  2. Connect to the Database Engine that will host the restored RiskFabric database
  3. In Object Explorer, navigate to the Databases folder
  4. Right-click Databases and select Restore Database...
    The Restore Database - Workbench window opens
  5. Select Source > Device and click the ellipsis button
    The Select backup devices window opens
  6. Click the Add button
    The Locate Backup File window opens
  7. Locate the database file copied from the source database server
  8. Click OK to close the Select backup devices window
  9. Click OK to close the Restore Database - Workbench window and restore the database

Repeat this procedure for each DW database for which you created a backup during database migration preparation.

Database References

Follow this procedure only if the name of the server(s) hosting SQL Server and SQL Server Analysis Services will change with the migration:

  1. Open SQL Server Management Studio (SSMS)
  2. Connect to the Database Engine hosting the RiskFabric relational database
  3. From the File menu, select New > Query with Current Connection
    A new query editor window opens
  4. Copy the following block of statements and paste it into the new query editor window: 
    /* Metrics Table */
    UPDATE RiskFabric.dbo.Metrics
    SET ServerName = '<new_server>';

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

    /* PortalSettings */
    UPDATE RiskFabric.dbo.PortalSettings
    SET    [Value] = N'<new_server>'
    WHERE  [Name] = N'ASDBServer';
  5. Modify each statement by replacing <new_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

Linked Servers

  1. Open SQL Server Management Studio (SSMS)
  2. Connect to the Database Engine hosting the RiskFabric relational database
  3. From the File menu, select Open > File
    The Open File window opens
  4. Locate and open a linked server script file saved previously
  5. Execute the script by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Execute from the Query menu
  6. Repeat steps 3 through 5 for each of the linked server scripts saved previously
  7. For Oracle linked servers and other OLEDB data sources (e.g., Symantec DLP), the script will need to be updated to include the user password:
    1. Locate the following parameter:
      @rmtpassword=’########’
    2. Replace the hash (#) symbols with the password for the Oracle database user account
    3. Execute the script by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Execute from the Query menu

Repeat this procedure for each linked server for which you generated a CREATE script during during database migration preparation.

SQL Server Agent jobs

This restore procedure should not need to be followed for the following jobs:

  • RiskFabric DB Maintenance Weekly
  • RiskFabric Intraday Processing
  • RiskFabric Processing
  • RiskFabric Send Scan Exclusion Notifications
  • RiskFabric Send Vulnerability Summary Emails

For all other jobs, follow this procedure:

  1. Open SQL Server Management Studio (SSMS)
  2. Connect to the Database Engine hosting the RiskFabric relational database
  3. From the File menu, select Open > File
    The Open File window opens
  4. Locate and open a SQL Server Agent job script file saved previously
  5. Execute the script by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Execute from the Query menu

SQL Server Agent proxies

This procedure should not need to be followed for the following proxies:

  • Operating System (CmdExec)
    • RiskFabric Nightly Processing
  • Analysis Services Command
    • RiskFabric Proxy

For all other proxies, follow this procedure:

  1. Open SQL Server Management Studio (SSMS)
  2. Connect to the Database Engine hosting the RiskFabric relational database
  3. From the File menu, select Open > File
    The Open File window opens
  4. Locate and open a SQL Server Agent proxy script file saved previously
  5. Execute the script by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Execute from the Query menu

Application (IIS) Server

Follow this procedure to point the original RiskFabric site at the migrated RiskFabric database on the new database server:

  1. Open Windows Explorer
  2. Navigate to the following folder: 
    <path>\Risk Fabric Server Web
    The default installation path is: 
    %SystemDrive%\Program Files\Bay Dynamics
  3. Open the file following file using a text editor run as an administrator: 
    web.config
  4. Edit the Data Source in the connection string to point to the new database server and instance (if the default instance is not used): 
    <configuration>
      <configSections>
      </configSections>
      <connectionStrings>
        <add name="RiskFabric.Web.Properties.Settings.DatabaseConnectionString" connectionString="Data Source=<server[\][instance]>;Initial Catalog=RiskFabric;Integrated Security=SSPI;" />
      </connectionStrings>
  5. Save and close the file
  6. In the Actions pane under Manage Website, select Start

Post-Migration Function Tests

  1. Run the RiskFabric Processing job and check the job status for errors
  2. Open the ICA console and check for authentication and navigation errors
  3. Create a test KPI and add it as a metric (refer to the About Metrics section of the Symantec ICA Dashboard Designer Guide)

After successfully completing post-function migration tests, you may delete the new RiskFabric site in IIS Manager. You may also delete its files from the file system.

Powered by Wolken Software