Move Planning databases to another server

[mr j]

Hi all,

This topic has been somewhat discussed, but still I'm not confident with the details. There's a need to move Planning databases to another server. That is Content Store, Planning Store, Contributor application and publish databases. There's Analyst <> Contributor D-Links, Admin links and CAC Macros some of which start Analyst macros etc. and it would be great if they worked after the move. Planning version is 8.4.

What are the exact steps? Here's how I've figured it:

1.   Export applications (& Admin links & macros?) (CAC)
2.   Remove applications from PAD (CAC)
3.   Remove datastore (CAC)
4.   Copy & move Planning Store database to new server
5.   Copy & move Content Store database to new server
6.   Configure new Planning Store & Content Store (Cognos Configuration)
7.   Configure new datastore (CAC)
8.   Import applications (CAC)
9.   Add applications to monitored applications (CAC)

What to do with the links and macros?

Or,
is it an option to copy & move also the application databases to the new server and then in step 8 use "Link to existing applications" instead?

[ericlfg]

Hi MrJ,

I would say, right off, that the easiest way to accomplish this (albeit perhaps not the best approach) would be to setup an alias to take server requests intended for the old server and route them to the new server.  Sometimes in the short run this is the easiest / least invasive and I only include it for completeness.  If you want to move servers, and avoid the use of an alias, then the steps you outlined are mostly correct.  A few things:

1. I would use the deployment facility in cognos administration (web portal) to export the content store into a deployment package.  This would contain all relevant objects and packages, along with all of the namespace security (which is what you would want to preserve).  I would also use the CAC deployment export as you've outlined to package up the contributor applications, admin links, and macros.  You will also need to make note of the names of all publish containers currently in existence.  You will need to recreate these following the deployment import processes.

2. I would create a new CS and PS on the new DB server and populate these new containers with the exports you took.

3. When you perform the import of the CS, it will repopulate the blank CS with all of the security and package objects.  When you perform the import of the CAC deployment, it will bring in all of the applications, macros, and admin links.  The publish containers will need to be recreated with identical names from before (to preserve any reporting structures) as they are not portable.  The applications will be reattached to the PS and you will need to GTP them, but all security mappings, and specific application configurations will be intact.  The only thing that will need to be updated, from what I can recall, are the macros.  I can't remember, but I do not think the macros datastore will be automatically updated using the deployment.  I believe the admin links will, however, so long as the applications and admin links exist in the same deployment package.

4. TEST!  ..Seriously...  Build up a single server using a blank CS and PS and go through the migration process in this sandbox environment.  This way you can confirm your steps and address any complications that cannot be foreseen.  This is a major structural update, arguably the most important aspect of the environment (next to the actual cognos software itself), know how you're going to do it before you attempt to do it in production.

So steps:
*DISCLAIMER*
These are the steps I would follow, and it's completely possible I missed something as I was doing this completely from memory.  Follow at your own risk.

1. Export the CS (cognos administration) and PS (CAC).
2. Make note of the publish container names that you would need to recreate.
3. On the new DB server, create a new CS and PS database.
4. Go into Cognos Connection > Administer IBM Cognos > Security > Cognos > System Administrators > Add the Everyone group
5. Stop services on your present environment.
6. Update cognos configuration to point to the new databases.
7. Go to the filesys.ini and delete the instancelock line near the top (this is required when creating a new PS).
8. Startup services and assuming everything goes well, head into cognos administration and import the CS export.
9. Open the CAC, modify the filesys.ini line (if required) and allow the wizard to create the planning store tables.
10. When the CAC navigation on the left is presented (System Settings, Access rights, etc.) Add your new datastore server and create your job server cluster.  Add the planning store object to the cluster.
11. Import the CAC deployment that contains your applications, admin links and macros.
12. When this deployment import is completed, refresh the CAC console (Tools > Refresh console) and GTP each application.
13. Recreate the publish containers.
14. Rebuild the CAC macros.

I believe that is all. 

[mr j]

ericlfg,

Thank you again for the valuable input.

The use of an alias was actually just discussed with the it rep. If this approach was chosen, how about the version of SQL server, would that be an issue? Current live one is 9, new would be 10.5.

[ericlfg]

I'm not familiar with the versions of SQL server you just referenced, the versions available are 2005, 2008, and 2012..   but there shouldn't be a problem with setting up the alias regardless of the version.

[mr j]

Sorry, could've been more specific,
10.50.2500 is 2008 R2
9.0.3050 is 2005

I'm just thinking whether there would be some new elements in the new copied database because of the sql version which would confuse Planning.. I have a faint idea of coming across something like this, but really can't recall what it was.

I take your point regarding testing seriously. I tried in a completely other environment to remove an app from PAD, copy the database etc. (not the exact steps here), but now I'm struggling with creating CAC macros to run Analyst macros. It says "Unable to collect list of libraries". I'm not entirely sure whether it's related at all to the db move, but after trying almost all the tricks with ini-file, UNC paths, users.. it's not working. These are the kind of surprise elements that definitely burn your planned schedule for something seemingly simple. Not that moving databases is exactly that, as pointed out  :D

[ericlfg]

I thought you might have been talking about the driver versions, so thanks for confirming.

From an alias perspective, there wouldn't be any differences between 2k5 and 2k8, however, I wouldn't recommend using the SQL processes to upgrade the databases.  When planning creates the databases, it codes a DB version into a table, now I'm not sure what this is used for, but this value potentially wouldn't be upgraded using the SQL processes.  I've also never used the SQL upgrade processes to upgrade a planning application from 2k5 to 2k8 and I probably wouldn't recommend it.

As for the unable to collect list of libraries error, it's not related to the DB move, but likely the way the software was installed.  For the 2 IBM services, assign the account you used to perform the install to the IBM cognos service and IBM Cognos Planning 10 service on the machine running the CAC service (if you haven't already).  If you did the install as a personal account, and you want to use a service account, you will have to follow the steps in the TN below to import registry keys into the service account profile.

http://www-01.ibm.com/support/docview.wss?uid=swg21472649

[mr j]

Thanks a million again, but I got confused now.. maybe it's my understanding of English (and DBs  :O ), so please bear with me.

When you say you wouldn't use SQL process to create the databases how would you do it? I mean in my mind the use of an alias means you'd copy the databases as is and avoid the whole hassle in CAC with removing applications from PAD, exports/imports of model, rebuilding macros and so forth, but if there's need for Planning to create the databases (e.g. because of coding DB version into a table) aren't we on that path anyway? Or "Upgrade Planning Administration Domain" -wizard..?


Thanks also for the advice for the libraries error, I've realised that it must be the user accounts. It's a sandbox environment rarely used so I can't say for sure whether it really worked before the db move or not.

[ericlfg]

Sorry to confuse you. :)

My only concern is that if you physically move an .mdf or backup file of the sql 2k5 contributor applications to 2k8, I don't know if there would be problems, since I would expect that SQL would 'transform' the DB to make it compatible in the new version.  I'm not a DBA, so I don't know if that would be the case or not -- certainly worth testing out.

[mr j]

Right, right... got it now. It was more my reading in general than understanding of English or databases that got me confused  ;D

Have to see how to deal with this, availability of appropriate testing environments appears to be a problem..

This is what I tested recently, steps more or less as follows:
- Backup of a application datastore via SQL to .dat -file. Origin was db on laptop
- remove application from PAD on laptop
- restore application datastore to a test server we have
- Added the server in question to PAD
- used "Link to an existing application" in CAC and selected the new server & the restored app datastore
- GTP for the application, as there were D-Links pointing production.
- remapping of D-Links required and Analyst macros where there's DlinkActivateQueue. After this CAC macros utilising these Analyst macros were up and running without touching them otherwise. In my opinion rebuilding of CAC macros is more of a chore than remap of Analyst macros.

To be noticed is that the SQLs are the same version 2008 R2 and Content Store and Planning Store were not moved. But, if there were time and available servers with different versions I'd definitely test copying the Content and Planning Stores via SQL, remapping them in Cognos Configuration and then doing the above for Planning apps.

Thanks a lot for all the answers! Really appreciate it.

[mr j]

Hi again,

continuing the series of "In case someone is interested" which I wrote to another topic...

The move of databases is done now.

- use of aliases was not possible since there were other active databases on the old sql server.

- attempted to export Contributor models and data with CAC Deployment Wizard. This resulted in CAC Deployment Wizard "not responding" and after force kill via Task Manager a prompt "There was a problem running the Planning Network Transfer Wizard. Please see the error log for more information." Once again error log did not help. I tried other combinations of exports (ie. without data, only apps, only one app..) but as nothing worked and there was a timeframe given proceeded more or less as follows..

- removed applications from PAD, shut down services on Planning server. Yes, only one server.
- DB admin copied Content Store, Planning Store and application DBs and moved these to the new SQL server. Publish DBs were copied but not moved at this point.
- modified the connections to new CS and PS in Cognos Configuration.
- added new datastore in CAC.
- used "Link to an existing application" in CAC and selected the new server & a restored app datastore. This step produced a prompt that the publish databases can't be found and the application could not be added. Too bad I didn't make full notes what exactly happened, but there was no possibility to go back as Cancel was inactive. The only way was to kill CAC process via Task Manager BUT when I opened the CAC again, voilà, the application was there... this was the way then for all the applications. As there weren't too many we decided with DB admin to proceed without even testing to move the publish databases to the new server. (I've noticed in app database table "adminoption" there's entry "TABLE_PUBLISH" which contains the publish datasource info.. maybe copying the publish DBs and editing this entry to point to the new server...  :)
- so, publish databases needed to be recreated via CAC.
- added everything to Monitored Applications.
- GTP for the applications.
- D-Links from Analyst to Contributor had to be updated to point to the new applications, ie. open them in Analyst and when it prompts the app can't be found click yes to reselect the app, save, close... laborious, yes, other options I'm not aware of.
- CAC macros had to be edited to point to the new datastore. Laborious, yes, other options I'm not aware of.

Definitely not the best practise approach, but given the deployment failing.. Later I noticed some app had had reconciliation hanging for some reason and I've read somewhere this could cause the export to fail, but now that there's no such it still does not work.

Databases on the new server SQL 2008 R2 have compatibility setting to SQL 2005 which was the old server.

ericlfg,
in case you cared to read all the way here  ;)  you mentioned Planning codes DB version to a table, do you recall which table that is? I was not able to find it yet..