How do I move the Analysis service cube database to a different folder or server? (NETIQKB70853)

  • 7770853
  • 24-Dec-2007
  • 24-Jan-2012

Environment

Security Manager 6.5

SQL server 2005

 

Situation

How can I migrate the Security Manager Analysis Services Cubes?

Resolution

You can migrate the Analysis service cube database to a new server with the following process:

      1. Disable the NETIQ_SM_SSIS Job on the sql agent. Go to SQL server management studio > sql server agent > jobs > NetIQ_SM_SSIS > right click > disable. A progress box will appear and afterward there will be a red arrow pointing down to show the job has been disabled.
      2. If moving the SMCUBEDEPOT, it will need to be backed up also. Go to SQL server Management Studio > Database server > databases > SMCUBEDEPOT > right click and choose backup. Note: Other properties that may need to be configured are the path for the back up if the default is not used and a password if you choose to encrypt the back up file.
      3. Backup the SMreporting db on the analysis server. Go to SQL server Management Studio > Analysis server > databases > SMreporting > right click and choose backup. Note: Other properties that may need to be configured are the path for the back up if the default is not used and a password if you choose to encrypt the back up file.
      4. Delete the SMReporting database.
      5. If moving SMCUBEDEPOT, install SM Reporting on the new server. Note that during the restores, you will be overwriting the databases, and this install will create the jobs that were on the previous server.
      6. Open the analysis service properties. Change the property called 'DataDir' and "LogDir" to point to the new location. This may require a restart of the analysis services service.
      7. Restore the SMReporting database from the back file. Right click the databases folder and choose restore > type in the name of the database, browse to the location of the backup file, type in the name of the back up file, and type in the encryption password if needed. Click okay and wait for the restore to complete. If the database does not appear immediately, you may need to refresh.
      8. If moving SMCUBEDEPOT, it will need to be restored also. Right click the databases folder and choose restore > type in the name of the database, browse to the location of the backup file, type in the name of the back up file, and type in the encryption password if needed. Click okay and wait for the restore to complete. If the database does not appear immediately, you may need to refresh.
      9. If moving SMCUBEDEPOT, change the connection string in the Data Sources . Go to SQL server Management Studio > Analysis server > databases > SMReporting > Data Source > right click on SMCubeDepot and select Properties. In the Connection String, there is a Data Source=, change the servername after the = to the new server that the SMCUBEDEPOT database resides on and click OK.
      10. Change the CurrentDate in the SMCUBEDEPOT database. Go to SQL server Management Studio > Database server > databases > SMCUBEDEPOT > Tables > right click on Process_Control and open it. Change the value for CurrentDate to a date that is older the the PartitionPeriod value (ie if today is Jan15, 2012 and the PartitionPeriod is 31, any date older than Dec 14, 2011 can be used).
      11. If moving SMCUBEDEPOT, change the reporting server in the LAS configuration. On the Log Archive server, open the Log Archive Configuration utility. Go to the Log Archive Settings page. Go to the Reporting section, and change the value for the Reporting Server Name and click Close. Note this requires a service restart to start sending data to the new server.
      12. Enable the 'NETIQ_SM_SSIS' job on the sql agent.

Cause

The OLAP cube (SMReporting) needs to be moved to another server, drive, or directory.

Additional Information

Formerly known as NETIQKB70853

Feedback service temporarily unavailable. For content questions or problems, please contact Support.