Recommended database maintenance for Content Store on SQL Server?

[cheriesheriff]

I have looked through the IBM documentation online and I haven't found any reference to best practices for database maintenance. 

I am specifically looking for database level indexing & database consistency checks. 

Anyone have some do's and don't's? 

Thank you. 

~cherie

[blom0344]

My advice: Leave it alone. The content store is about metadata storage, not data. The best maintenance would be to make sure it is backed up on a very regular basis

[Suraj]

CS stores all the information to operate the environment including 1000's of report outputs and if CS response is slow, it could be due to a lot of stored report outputs.

Here is an article on this.
**************
Does your Content Store need to go on a diet...?
As Cognos admins know, the Content Store is the central nervous system for any Cognos instance.
Among the many things stored in this repository are report specificiations, published models, and report outputs.
As the Cognos instance matures, the amount of data contained in the Content Store database can expand quite rapidly.
At a certain tipping point, Content Store bloat can adversely affect the overall performance and stability of the Cognos instance.
One best practice for getting the most out of your Cognos Instance is to keep your Content Store "lean and mean" by removing superfluous content.  Report outputs are almost always among the largest consumers of space in the Content Store.
Attached is a funny illustration.  ;D

[Suraj]

I am specifically looking for database level indexing & database consistency checks. 

About the consistency checks, we have 'Find and Fix' checks running weekly to remove any broken contents usually when users leave the company.
It does work and shows what it cleaned after the job is done.
You can set it up in Cognos adminstration-->configuration window in Cognos connection.

[Suraj]

Also, when I was reading features of 10.2 version, it mentions 10.2 supports fully integrated content archiving to a file system.
We have not upgraded to 10.2 yet but here it is if it's any helpful.

Content Archiving
Last but not least, Cognos 10.2 now natively supports fully integrated content archiving to a file system. This feature will be coveted by organizations that have content retention and audit requirements that are looking to keep their Cognos content store free from large report outputs to maximize their environmental performance. This new feature has much of the same archival functionality that IBM FileNet customers have enjoyed since the 10.1.1 release.

Prior versions using the Rational Archive Software

IBM Cognos Content Archival
Storing archived content in your external repository provides you with the ability to adhere to regulatory compliance requirements, and can enhance the scalability and performance of IBM® Cognos® products by reducing the size of content in the content store.
Administrators create a data source connection to an external repository to allow content to move from the content store to the repository. Users can view the archived content in the external repository from IBM Cognos Connection. By providing search results for recent and archived content, users can make critical comparisons between current data and historical data. This efficient mechanism allows your company to meet corporate and government requirements while providing a seamless user experience.
There are two workflow scenarios for archiving your content. The first workflow allows administrators archive packages and folders after installing IBM Cognos Content Archival software. The second workflow allows administrators to create repository connections for new packages and folders. The tasks for each workflow scenario are described below.

Workflow 1: Archiving content after installing connectivity software
Administrators can archive saved report output for specific packages and folders or all packages and folders after installing or upgrading IBM Cognos Business Intelligence. This workflow only needs to be completed once since all of your content is currently located in your content store.
•   Create a data source connection to the external repository.
•   In Cognos Connection, select repository connections for the packages and folders that need to be archived.
•   Create and run a content archival maintenance task to select folders and packages to archive in the external repository.
Once you set a repository connection for packages and folders, any new report output is automatically archived, which means that there is no need to run the content archival maintenance task again.
For information about installation and configuration tasks, see the IBM Cognos Business Intelligence Installation and Configuration Guide.

Workflow 2: Creating repository connections for new packages and folders
Administrators can create repository connections for new packages and folders by completing these tasks:
•   Create a data source connection to the external repository.
•   In Cognos Connection, select repository connections for the packages and folders that need to be archived.

Using content archival content maintenance tasks
The content archival content maintenance task creates a reference to the report versions in the folders and packages that you select and configure. Selecting folders and packages marks the content within and allows it to remain in the content store until it is archived in your external repository.
It is important to note that this task does not move your content from the content store to the external repository. You must select repository connections for your packages and folders in IBM Cognos Connection first (Specifying an external repository for report output). Report versions in folders and packages that are not marked for archiving are available for deletion from the content store.
Once the content is marked, the content archival task is complete. A background task in Content Manager finds the marked items and then copies and saves them in the external repository.
Importing content into a folder or package that is configured for archiving to an external repository does not automatically move and archive the imported content into the repository. An administrator must run a content archival content maintenance task for this folder or package to archive the imported content.

Background tasks
The background XML tasks used to move content from the content store to the external repository are archiveTask.xml and deleteTask.xml. The archiveTask.xml file moves marked content to an external ECM repository. You can also use this file to set thread execution times and archive outputs of selected formats. The deleteTask.xml file is a configuration file that retrieves and deletes marked version objects from the queue.
For more information, see the IBM Cognos Business IntelligenceInstallation and Configuration Guide.

Preserve Content IDs before you archive
If required, you can preserve content IDs before report output is archived.
Objects in the content store have content IDs that are deleted and replaced with new IDs by default when you run an import deployment and move content to a target environment. However, there may be situations when you must preserve content IDs, for example, when moving report output to a external report repository.

For more information, see Content ID assignment. http://publib.boulder.ibm.com/infocenter/rentrpt/v1r0m1/topic/com.ibm.swg.ba.cognos.ug_cra.10.1.1.doc/c_assigning_store_ids.html

•   External repository data sources
You specify connection parameters when you create a data source or modify a data source connection.
•   Creating a connection to a file system
Administrators create a data source connection to a file system in a test or proof-of-concept environment only. Connecting to a file system allows you to test or demonstrate IBM Cognos Content Archival.
•   Specifying an external repository for report output
Report output can be archived to an external report repository for long-term storage. You must specify a repository at the folder and package level before content can be archived to the repository.
•   Creating content archival content maintenance tasks
Create a content archival content maintenance task to move report output within folders and packages for archiving to your external repository.
•   Creating a retention rule update maintenance task
Create a retention rule update maintenance task to globally modify the number of report output versions, document content versions, and report history that are currently kept in the content store.
•   Creating a content removal content maintenance task
Create a new content removal content maintenance task to mark history objects and report output versions, which are in folders and packages, for deletion.
•   Finding content in your external repository
Your archived content can be viewed in IBM Cognos Connection or in your external repository.
•   Searching archived content
Retrieving report content is important wherever the location. In IBM Cognos Connection, you can access content stored in the IBM Cognos content store and in an external repository. By providing search results for recent and archived content, users can make critical comparisons between current data and historical data.

[blom0344]

My advice: Leave it alone. The content store is about metadata storage, not data. The best maintenance would be to make sure it is backed up on a very regular basis

The 'leave it alone part' part should have been aimed only at the idea of indexing - As Suraj points out with his material on maintenance.  I think  Cognos meant to use the contentstore by primary key indexing (implicit) to avoid duplicates and as a way to perform updates.  It is not a candidate for additional indexing as an afterthought/action as a regular database containing data

[Michael75]

Cherie,

This video is a few years old now:

http://download.boulder.ibm.com/ibmdl/pub/software/data/sw-library/cognos/webcasts/wa_cognosforum08_content_store_data/

but I suspect its recommendations are still largely applicable to the Cognos 10 Content Store.

Rgds, Michael