How to migrate / upgrade / upsize an existing database to a new (later) version of Controller

Answer

(a) Make sure that the new (target) database server's settings match the old (source) database server

• Most importantly, make sure that the new collation (or character set) exactly matches the old server

(b) Use the Database Conversion utility to update/upgrade the database, so that it can be used by the newer/later version of Controller.

• This needs to be repeated for all 'application repository' databases, plus (if used) the 'FAP' and/or 'Data Mart' databases.

Steps:

(a) Make sure that the new (target) database server's settings match the old (source) database server

Microsoft SQL:

1. Ensure that the new SQL server's default collation exactly matches the old SQL server's default collation.

• TIP: For more information on how to do this, see separate IBM Technote #1594174 (also see 1388368 for why).

2. Create a SQL login (on the new SQL server) with the same name as the one currently used by the old system (for example, called 'fastnet')
• TIP: For more information, see separate IBM Technote #1594174 (also see Scenario #4 inside separate IBM Technote #1366714)

3. After restoring the database (on the new SQL server), synchronise the SQL login names (remove orphaned users) by running a script similar to: EXECUTE sp_change_users_login 'Update_One', 'fastnet', 'fastnet'
• TIP: For more information, see separate IBM Technote #1594174 (or Scenario #2 inside separate IBM Technote #1366714)



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TIP: Step 3 is only necessary if the database tables are owned by a user (for example 'fastnet') instead of 'dbo'. Typically this will only be true if the database was originally created in an old version of SQL (for example SQL 2000).

• If the database was created in a modern version of SQL, then the tables are (normally) already owned by the user 'dbo' and therefore step 3 is unnecessary.



Also, be aware that there is an alternative method to step 3 (which also means that in step 2 you can change your SQL login name) which involves modifying all the table owners to be 'dbo'.

• For more details on this alternative method, see separate IBM Technote #1364881.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oracle:

Ensure that the new Oracle server's character set exactly matches the old Oracle server's character set

• TIP: For more information, see separate IBM Technote #1347750



DB2:

Ensure that the new DB2 server's settings are compatible with Controller

• TIP: For more information, see separate IBM Technote #1570572

(b) Use the Database Conversion utility

Launch Controller Configuration on the Application Server, by opening “Start –Programs – ‘IBM Cognos Controller – 64’ - IBM Cognos Controller - Controller Configuration”

• Click “database connections” and then highlight your database
• Click on the green “tick” and ensure that it says “connection succeeded”
• Click on the green “play” icon (see below - next to the tick) labelled “Run”

 
Click “Connect” at the following screen:

IMPORTANT: MAKE SURE THAT YOUR SCREEN ABOVE IS FILLED IN SIMILARLY TO THE ABOVE BEFORE PROCEEDING
• Most importantly, ensure that 'Current Version' has a value larger than zero (for example 883)
• If the value of 'current version' is zero (0) then read advice on separate IBM Technote #1366714.

You will see that the “Current Version” is lower than the new “Upgrade to” DB version (927). Because of this, click “Upgrade” and wait for the process to finish:

Click “Close”.
Repeat the above process to upgrade all other databases

=========================================
** Optional ** – This section is only necessary if you are publishing Data Marts

Repeat the instructions in the previous section, but this time:

• Click on the ‘”Datamart DB’ radio button (see picture below)

• Click on the ‘browse’ button (“…”) and MAKE SURE THAT YOU OPEN THE “DMData” folder [NOT the “Data” folder]

• Choose your UDL file and upgrade it.

Repeat this section to upgrade the Data Mart version for *all* other database connections (contained inside the “DMData” folder).

=========================================

** Optional ** – This section is only necessary if you are using FAP

Launch “Controller Configuration” and then:

• Open “Database Connections” and create a new entry
• Choose your settings for the FAP database, for example:
• database Type (for example “SQL Server”)
• Name: controller_fap_data_mart
• Provider: SQLNCLI11.1
• User id: fastnet
• Password: <password>
• Initial Catalog: controller_fap_data_mart
• Data source: <SQL_server>
• Click ‘Save’
• Select this database connection (for example “controller_fap_data_mart”) and click the “run” button (green triangle).

This will launch the "Database Conversion Utility"

• Check that the "UDL File" is correctly pointing to your FAP database
• Click the "FAP DB" radio button
• Click "Connect"
• Click “Upgrade”

IMPORTANT: After doing the above steps, you should ‘tidy up’ the new Database Connection by moving the UDL file (for example C:\Program Files\ibm\cognos\ccr_64\data\ controller_fap_data_mart.UDL) to a new folder (for example C:\Program Files\ibm\cognos\FAP_UDL). If you do not do this, then users will see this in their list of databases to choose from when they launch Controller.
=========================================