IBM Support

How to move Controller databases to a new database server (basic instructions)

How To


Summary

Customer has an existing (working) Controller system. Customer would like to move the Controller-related databases to a new database server.

What do they need to do?

Objective

It is impossible to give perfect/thorough/in-depth step-by-step instructions suitable for all environments
  • Therefore, this Technote is intended to give basic ("bullet point") steps.
  • These steps are suitable for standard/basic Controller implementations.
If a customer does not feel confident to use these bullet-point steps, then they should employ an experienced Technical Consultant (for example from IBM Services, or an IBM Partner) to perform the tasks for them.
  • Customers follow these instructions at their own risk.
For the avoidance of doubt, IBM Support recommends that (in an ideal world) customers employ an experienced Technical Consultant (for example from IBM Services, or an IBM Partner) to perform these tasks for them.

Environment

This Technote assumes that the customer is using Microsoft SQL server.
  • If using a different database server (for example DB2 or Oracle) the instructions will need to be amended slightly.

Steps

Ideally, the following steps should be performed by an experienced technical consultant (for example IBM Services or IBM Partner consultant).
(1) Ensure that the new database server version is compatible with your version of Controller
  • For example, make sure that the new version of Microsoft SQL is officially supported (by IBM) for that version of Controller.
IBM publishes supported environments for all their versions of Controller.
  • For example, use a search engine to search for something similar to:      cognos controller 10.3.1 supported environments
(2) Make sure that the new (target) SQL server has exactly the same default collation as the original (source) database server.
  • Specifically, make sure that the TEMPDB of the old and new servers has *exactly* the same collation (for example "SQL_Latin1_General_CP1_CI_AS")
  • For more details, see separate IBM Technote #1388368.
(3) Ensure new (target) SQL server is configured for 'mixed mode' authentication
  • In other words, it allows SQL logins (for example 'sa').
(4) Create a new SQL login (for example 'fastnet') to match the same name used for the original source system
Typically simply launch 'Controller Configuration' and open 'Database Connections'. Open the relevant connection (for example 'production') and make a note of the user (for example 'fastnet') currently being used.
  • Create a new SQL login (on the target database server) to match this
(5) Ensure new (target) SQL server is configured for 'ERO' (also known as 'Optimise2')
  • For more details, see separate IBM Technote #1347048.
 
(6) Obtain downtime (no users on the system) and then backup all the source databases
Typically this will be the following databases:
  • The 'Contentstore' (Cognos BI / Cognos Analytics repository)
  • The 'main' Controller databases (for example 'production', 'test' databases)
  • The 'FAP' database (if using FAP).
(7) Restore the backups onto the new/target SQL server
(8) Check the owner of each of the tables for all databases (on the new SQL server)
For example, inside 'SQL Management Studio' expand section 'tables'.
  • By default (for modern versions of SQL) the tables will begin 'dbo.tablename'
  • However, if the database was originally created may years ago (with an old version of SQL) the tables will begin with the SQL login name, for example:   'fastnet.tablename'
image-20190118135907-1
To make the later steps easier, if the tables begin with the SQL login name (for example 'fastnet.TABLENAME') then convert all the tables, views and stored procedures to dbo.
  • In other words, perform the steps inside separate IBM Technote #1364881.
(9) Assign the SQL login (for example 'fastnet') 'dbowner' rights to all the restored databases
(10) Inside 'Cognos Configuration' modify the Cognos BI/CA contentstore settings to use the new SQL server
  • In other words, change the setting "Database server" here:
image-20190118142257-1
Afterwards, save changes and restart the service:
image-20190118142350-2
(11) Inside 'Controller Configuration' modify the settings to use the new SQL server
  • In other words, change this setting for all connections:
image-20190118141509-1
If (unlikely) you are publishing data to an RDBMS data mart (in other words, *not* a FAP data mart) then you will also have to modify your data mart settings:
image-20190118141603-2
Afterwards, restart the batch service by clicking 'Restart' here:
image-20190118141623-3
Finally, inside the 'Enhanced Reporting Optimizations' section, modify the settings to point to the new SQL server, for example:
image-20190118141747-4
(12) If using FAP, then modify the FAP settings in the following order:
(a) Launch the FAP client, and login to the existing (old) database.
(b) Inside the tab 'Sources' stop the relevant source
(c) Stop the Windows service ‘IBM Cognos FAP Service’
(d) Stop the relevant TM1 instance (for example 'FAP') which is hosting the FAP cube
(e) On the TM1 server, modify the ODBC 'System DSN' connection (called 'FAP') to point to the new SQL database server
image-20190118143932-4
(f) On the Controller application server, edit the file "FAPService.properties" (by default this is located here: C:\Program Files\IBM\cognos\c10\server\FAP)
- Modify it to point to the new SQL server
(g) Start the Windows service ‘IBM Cognos FAP Service’
(h) Inform all users that when they launch the FAP client, they need to use the new SQL database server name (during login):
image-20190118142628-3
(13) If using Controller Web, then resynchronise the database (UDL) connections
  • Specifically, perform the steps inside separate IBM Technote #1997329.
(14) Test.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.3.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
18 January 2019

UID

ibm10795804

Page Feedback