Product:
Cognos Controller 10.3.1
Cognos Analytics 11.0.12
Planning Analytics 2.0.5
Problem:
What places should i change in Cognos in case i replace my Microsoft SQL database host to a new server?
First;
Stop all Cognos windows services.
Take a backup of the databases and copy them over to the new database server.
Ensure that the collation settings (sort order) is the same on old and new database instances.
Ensure that the SQL login have the correct dbowner rights on the database tables.
Start up the new database server.
Solution:
Cognos BI have connections to content store, and other databases you have setup from Cognos Configuration.
Start Cognos Configuration.
Go to Environment – Logging – Audit – Audit and change database server with port numbers value to the new SQL server.
Go to Data Access – Content Manager – Content Store, and change database server name value to the new SQL server.
Repeat the change for notification database,and any other database you have setup in Cognos configuration.
Save the changes and start the CA11 services, in case you have more than one BI server, start with the primary content manager first.
Surf to Cognos Connection, go to manage – administration console. Click on configuration tab. Go to Data Source Connections and in the right side click on the database link name. Then click on more in the right side.
Click on Set properties. Click on Connection tab. Click on pencil icon to edit the data source.
Change the server name to the new server, click on JDBC tab, and change Server name and port number to the new values. Click OK twice to save changes.
Test if the connection works.
Repeat for any data source you have here, that have changed database server.
Cognos Controller have database access that is stored in udl files, and is changed with IBM Cognos Controller Configuration program.
Start Controller Configuration on the Cognos Controller server.
Go to Database Connections – and for each database change the Data Source and click save.
Go to Report Server tab. Click on the check icon.
Press the REPAIR button, to update the data sources in Cognos Analytics.
Ensure that the windows user you are logged in to the Cognos Controller server, is system admin in CA11 Cognos connection, to make the update possible.
Go to Enhanced Reporting Optimizations tab – change the server name here. In most cases the file share for the ERO function is on the SQL server, and therefor need to be changed when you change your database server. Click save after each change, and you need to update each connection.
Ask the cognos controller users to test, by run a excel report with the ERO function active.
If the ERO share is on a file server (or the cognos controller server) you need to add the windows service account that the new SQL server is using to the file share, so the SQL server process have access to the files in this share.
The FAP service is configured in the C:\Program Files\ibm\cognos\ccr_64\server\FAP\FAPService.properties file. Open it in notepad++.
db=FAP
host=changethistothenewdbservername
dbType=sqlserver
user=cognos
passwd=xxxxxxx
Host should be servername\\instancename or servername:port if the port is correct.
http://www-01.ibm.com/support/docview.wss?uid=swg21417314
Save the FAPService.properties file and start the IBM Cognos FAP Service.
Check the C:\Program Files\ibm\cognos\ccr_64\server\FAP\error.log file for errors.
On the TM1 server there are ODBC connections to the SQL database, one is called FAP. All ODBC connections need to be updated to point to the new database server.
Export the values from registry and edit the server name in notepad.
Start Regedit program.
Go to HKEY_LOCAL_MACHINE – SOFTWARE – ODBC – ODBC.INI
Right click and select Export.
Save the file as ODBC_64.REG
Repeat the same for [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI] to get the 32 bit ODBC drivers exported to a text file, like odbc_32.reg.
The file can look like below;
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\FAP]
“Driver”=”C:\\Windows\\system32\\sqlncli11.dll”
“Server”=”roger2”
“Database”=”FAP_database”
“LastUser”=”cognos”
Open the file in notepad and change the server to the new database server, save the file.
Double click on the reg file to register the new value for the ODBC connection.
Start the FAP client connect, and enter the new server name and any new username or password, step through the database on the source tab to ensure it point correct. Edit as below if needed.
You need to edit the server for the cognos controller database, on the source tab.
You need to update the Controller Web to the new database. Stop the IBM Controller Web service.
Start a CMD prompt and go to folder; cd d:\Program Files\IBM\cognos\ccr_64\fcmweb
Enter the following command:
SyncDBConf.bat ..\Data wlp\usr\shared\config\datasources
Start the IBM Controller web service.
https://www-01.ibm.com/support/docview.wss?uid=swg21997329
More information:
https://www.ibm.com/software/reports/compatibility/clarity-reports/report/html/softwareReqsForProduct?deliverableId=13C73BD0195811E7A99D5014AD6C3D46&osPlatforms=Windows
https://www-01.ibm.com/support/docview.wss?uid=swg21882732&aid=3