Using SQL Server Express as the Cognos 8 Content Store
Microsoft SQL Server is fully supported by Cognos 8 Business Intelligence Server, but the Express Edition is not officially supported for use as the Cognos 8 content store, as per the support website. If you try anyway with a default install of SQL Server Express, you will see an error like the following in the cogserver.log file when the Cognos service is started:
192.168.0.103:9300 5496 2008-06-19 15:16:24.390 -5 Initialization_SESS Initialization_REQ Thread-16 CM 888 1 Audit.cms.CM StartService Warning CM-CFG-5063 A Content Manager configuration error was detected while connecting to the content store. CM-SYS-5003 Content Manager is unable to access the content store. Verify your database connection parameters, and then contact your database administrator for assistance. Failed Logon:com.jnetdirect.jsql.JSQLException: TCP/IP connection failed to host:localhost java.net.Con nectException: Connection refused: connect url:jdbc:JSQLConnect://localhost:1433/cognos83
This is a generic error that states that the Cognos 8 application cannot connect to the content store database. However, with a few configuration tweaks, this can be resolved. By default, the SQL Server Express networking protocols are disabled, which means that it will not accept TCP/IP connections. This is why Cognos cannot make the connection and throws the error. To resolve the problem, we need to do two things with SQL Server Express: first, enable TCP/IP, and then second, specify a port number to listen on. Here are the steps:
- Launch the SQL Server Configuration Manager.
- In the left hand window tree view, expand SQL Server 2005 Network Configuration.
- Select “Protocols for SQLEXPRESS”.
- In the right hand window, right click on “TCP/IP” in the list of protocols and choose “Enable”.
- Right click on TCP/IP again and select properties.
- Click on the tab labelled “IP Addresses”
- Scroll down to the bottom to view the section titled “IPALL”
- Clear the value for “TCP Dynamic Ports” so that it is blank.
- Enter TcpPort number to use when making remote connections, for example, “1433″.
Restart the SQL Server service. Now, when you start Cognos 8, the connection will be made and the content store tables will be created in the database. You can verify this by watching for xml files being created in the logs directory (with names like 001.CMCreateWSRPObjects_results.xml), or by refreshing the tables folder in the SQL Server. I saw 116 tables created, but I know that can vary, according to the version of Cognos 8.
Caveat emptor: Keep in mind that SQL Server Express is not supported by IBM Cognos for use as the content store database.
Thanks to the SQL Server Express Weblog for the pointers that helped me get this working.
Free Database Roundup
- IBM DB2 Express-C - Find a review of it here. I am currently running it on my laptop, primarily for use with IBM Cognos 8 Business Intelligence. I have posted an install guide, and a guide to setting up the Cognos 8 samples on it.
- Oracle 10g Express - A starter Oracle database. Resources and comparisons with the other free databases can be found here on TechRepublic.
- Sybase ASE Express - The free version of Sybase ASE is only available for Linux. The developer edition is available for other platforms, but it has much more restrictive terms of use.
- SQL Server 2005 Express - A light version of SQL Server for desktops, small servers and for embedding into applications. Lots of good resources here on the Microsoft site.
Open source databases
- Apache Derby - A small Java relational database which can be embedded or used in client - server mode. IBM Cognos 8 BI Server ships with Apache Derby for use as the default content store, which shortens the time to getting a demo or dev server up and running.
- Berkeley DB - Now distributed Oracle after their acquisition of SleepyCat. Berkeley DB is an embedded database with three separate editions: the original relational database, written in C, a Java
version and an XML edition. - MySQL - The “M” in LAMP. This database is behind many web applications, including WordPress and Wikipedia. MySQL is now owned by Sun Microsystems.
- PostgresSQL - A mature and well supported open source relational database.
- SQLite - As the name implies, a featherweight and self-contained database. It is designed for embedding into applications, in particular applications on mobile devices.
Other free database resources:
- Azzurrie Clay - a data modeling plugin for Eclipse.
- Freebyte’s guide to free databases - a more comprehensive list of open source databases and free database programming libraries.
- Getting started with the NetBeans Database Explorer.
- Free data models to help get started on database schema development.
- QuantumDB - an open source Eclipse plugin for database development.
- SQL Explorer - another Eclipse plugin for database development.
Teradata Express Edition
I don’t know for how long this has been available, but it is worth pointing out now: a free developer edition of Teradata, known as Teradata Express Edition. Teradata is the most scalable database on the market, making it a popular choice for massive data warehouses. The express edition is limited to 4GB of data and it is not for production use. However, I am a strong proponent of these express editions. They are a great way for IT professionals to have an in depth look at a technology without committing anything to the vendor’s sales team. For the vendor, having an express edition is great marketing, attracting companies, who they probably would not have reached anyways, to have a look at the product in their own environment and on their own time.