Skip to main content

Software  >  WebSphere  >  WebSphere Portal Family  >  

Support statement for using WebSphere Portal v6.0.1.4 with Microsoft SQL Server 2005
 Technote (FAQ)
 
Question
Can Microsoft® SQL Server 2005™ be used with IBM® WebSphere® Portal version 6.0.1.4?
 
Answer
This document explains and provides official support for Microsoft SQL Server 2005 Enterprise Edition as the database for WebSphere Portal and Workplace Web Content Management™ version 6.0.1.4.

Table of Contents:

1.0 Minimum Software Requirements
Installation Steps:
2.0 IBM WebSphere Portal Machine – Required Steps
3.0 Microsoft SQL Server 2005 Machine – Required Steps
4.0 IBM WebSphere Portal Database Transfer
5.0 General Tuning Guidelines

1.0 Minimum Software Requirements:
  • IBM WebSphere Portal version 6.0.1.4
  • PK70141: Unable to create a draft from publish content with Web Content Management due to JCR exception. This problem occurs with SQL Server 2005 only.
  • Minimum Supported WebSphere Application Server Level: fix pack level 6.0.2.27 with IBM Java™ SDK J2RE 1.4.2 (SR10)
  • Database: Microsoft SQL Server 2005 SP2 (SQL Server Express version is not supported. Enterprise is recommended and Standard edition is also supported but should be used with care)
  • JDBC driver: Microsoft SQL Server 2005 JDBC driver version 1.2 (no other JDBC drivers are tested or supported at this time)

1.1 Testing Infrastructure:
Prior to announcing this support, IBM Quality Engineering teams have verified the functional aspects of this configuration in the following environment:
  • Microsoft Windows Server 2003® (32-bit only) running WebSphere Portal version 6.0.1.4 and SQL Server 2005 on a remote Windows Server 2003 instance (32-bit or 64-bit).
This support is also available for any operating system already supported with IBM WebSphere Portal version 6.0.1.4 for which Microsoft provides the Microsoft SQL Server 2005 JDBC driver version 1.2 JDBC driver.

Installation steps:
You must follow these steps exactly and in this order to meet with success.

2.0 IBM WebSphere Portal Machine – Required Steps

Please ensure that the minimum software requirements from Section 1 are met before running any of the following tasks on the IBM WebSphere Portal machine.


(1) Update wps_inittables.sql to match the SQL Server 2005 syntax as noted here:
- Edit the file wps_inittables.sql located in: <PORTAL_HOME>/config/templates/db/sqlserver/.

Remove all the redundant "@DbSchema@.<DB_TABLE>." prefixes for the attributes of the table <DB_TABLE> from the “CONSTRAINT <CONSTRAINT_NAME> CHECK” clauses in wps_inittables.sql.

For example:
Update

CONSTRAINT CC20A CHECK (((@DbSchema@.APP_DESC.WEB_MOD_OID IS NULL) AND (@DbSchema@.APP_DESC.WEB_MOD_SL IS NOT NULL)) OR ((@DbSchema@.APP_DESC.WEB_MOD_OID IS NOT NULL) AND (@DbSchema@.APP_DESC.WEB_MOD_SL IS NULL)))

to

CONSTRAINT CC20A CHECK (((WEB_MOD_OID IS NULL) AND (WEB_MOD_SL IS NOT NULL)) OR ((WEB_MOD_OID IS NOT NULL) AND (WEB_MOD_SL IS NULL)))


(2) Download the Microsoft SQL 2005 JDBC driver version 1.2 (sqljdbc.jar) into a directory on the Portal machine (referred to in the next Step (4) as <MSJDBC_DIR>)


(3) Modify the following Portal properties files for SQL Server 2005:
- These files are located in <PORTAL_HOME>/config/
- wpconfig_dbtype.properties
- wpconfig_dbdomain.properties

- Update to the following values shown below:

<PORTAL_HOME>/config/wpconfig_dbtype.properties
# DbDriver: The name of class SqlProcessor will use to import SQL files
sqlserver.DbDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver

# DbLibrary: The directory and name of the zip/jar file containing
# JDBC driver class
# Please use the system specific file separator names, e.g. for windows
# semicolon and for unix colon.
# Replace <MSJDBC_DIR> with the directory of the MS JDBC driver
sqlserver.DbLibrary=<MSJDBC_DIR>/sqljdbc.jar

<PORTAL_HOME>/config/wpconfig_dbdomain.properties
1. Add the following new lines in any place in the wpconfig_dbdomain.properties file:
jcr.database.url=jdbc:sqlserver://<SQL_SERVER_HOST_NAME>:<PORT>;databaseName=
<JCR_DB_NAME>;SelectMethod=cursor
jcr.database.pool.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

2. Change the jcr.dbport=446 to jcr.dbport=<PORT>

3. <DOMAIN>.DbType=sqlserver
<DOMAIN>.DbUrl=jdbc:sqlserver://<SERVER_NAME>:<PORT>;DatabaseName=<DB_NAME>


3.0 Microsoft SQL Server 2005 Machine – Required Steps

(1) Follow these steps to use Microsoft JDBC driver on SQL Server 2005:

a. Copy: <Microsoft SQL Server 2005 JDBC driver>\sqljdbc_1.2\enu\xa\x86\sqljdbc_xa.dll file to <Microsoft SQL Server Database>\MSSQL.1\MSSQL\Binn directory on the SQL Server 2005 machine.

Note: Restarting the SQL server at this point is recommended to ensure the updates to the sqljdbc_xa.dll file are picked up.

b. Copy: <Microsoft SQL Server 2005 JDBC driver>\sqljdbc_1.2\enu\xa\xa_install.sql to <temp> on the SQL Server 2005 database machine.

c. Run the xa_install.sql from <temp> on the SQL Server 2005 database from the Microsoft SQL Server Management Studio:
- Start -> Programs -> MS SQL Server Management Studio
- File -> Open: <temp>/xa_install.sql
- Parse the file to check its content
- Execute the file

Note: Parse and Execute buttons are shown in the following screen shot for clarification


(2) On the Microsoft SQL Server 2005 machine, create registry values for XA DLLs:

- Use the registry editor and navigate to the registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\XADLL

- Create a new String Value registry named:
- Name: sqljdbc_xa.dll
- Value: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqljdbc_xa.dll


(3) Enable XA Transactions in Windows Component Services:

a. Navigate to: Start -> Settings -> Administrative Tools --> Component Services.

b. Expand the tree view to locate the computer where you want to turn on support for XA transactions (for example, My Computer).

c. Right click the computer name, and then click Properties.

d. Click the Options tab and tune the Transaction Timeout to a value which suits your environment (Recommended minimum is 180 seconds).
e. Click the MSDTC tab, and then click Security Configuration.

f. Under Security Settings, select the check box for XA Transactions to turn on this support.

g. Click OK, and then click OK again.



(4) Create databases, users, and schemas on SQL Server 2005 using the following steps:

a. Start the SQL Server Management Studio from Windows system menu:
Start >All Programs >Microsoft SQL Server 2005 >SQL Server Management Studio

b. Databases should be created as CASE SENSITIVE.

c. Execute the following script to create databases, users, and schemas:

use master;
go

create database RELEASE collate SQL_Latin1_General_CP1_CS_AS;
create database COMMUNITY collate SQL_Latin1_General_CP1_CS_AS;
create database CUSTOMIZATION collate SQL_Latin1_General_CP1_CS_AS;
create database WMM collate SQL_Latin1_General_CP1_CS_AS;
create database JCRDB collate SQL_Latin1_General_CP1_CS_AS;
create database FDBKDB collate SQL_Latin1_General_CP1_CS_AS;
create database LMDB collate SQL_Latin1_General_CP1_CS_AS;

use master;
go

CREATE LOGIN RELEASEUSR WITH PASSWORD = '<PASSWORD>';
CREATE LOGIN COMMUNITYUSR WITH PASSWORD = '<PASSWORD>';
CREATE LOGIN CUSTOMIZATIONUSR WITH PASSWORD = '<PASSWORD>';
CREATE LOGIN WMMDBUSR WITH PASSWORD = '<PASSWORD>';
CREATE LOGIN ICMADMIN WITH PASSWORD = '<PASSWORD>';
CREATE LOGIN FEEDBACK WITH PASSWORD = '<PASSWORD>';
CREATE LOGIN LMDBUSR WITH PASSWORD = '<PASSWORD>';
go

use RELEASE;
exec sp_grantdbaccess @loginame = 'RELEASEUSR';
exec sp_addrolemember @rolename = 'db_owner' , @membername = 'RELEASEUSR';
go

use COMMUNITY;
exec sp_grantdbaccess @loginame = 'COMMUNITYUSR';
exec sp_addrolemember @rolename = 'db_owner' , @membername = 'COMMUNITYUSR';
go

use CUSTOMIZATION;
exec sp_grantdbaccess @loginame = 'CUSTOMIZATIONUSR';
exec sp_addrolemember @rolename = 'db_owner' , @membername = 'CUSTOMIZATIONUSR';
go

use WMM;
exec sp_grantdbaccess @loginame = 'WMMDBUSR';
exec sp_addrolemember @rolename = 'db_owner' , @membername = 'WMMDBUSR';
go

use JCRDB;
exec sp_grantdbaccess @loginame = 'ICMADMIN';
exec sp_addrolemember @rolename = 'db_owner' , @membername = 'ICMADMIN';
go

use FDBKDB;
exec sp_grantdbaccess @loginame = 'FEEDBACK';
exec sp_addrolemember @rolename = 'db_owner' , @membername = 'FEEDBACK';
go

use LMDB;
exec sp_grantdbaccess @loginame = 'LMDBUSR';
exec sp_addrolemember @rolename = 'db_owner' , @membername = 'LMDBUSR';
go


d. Be sure that no Server Role is selected.

e. Execute the following script to grant database access and to add role members: (Run this step only after Step b. has completed fully – do not combine with Step b.)

use master
go
exec sp_grantdbaccess 'FEEDBACK', 'FEEDBACK'
go
exec sp_addrolemember [SqlJDBCXAUser], 'FEEDBACK'

use master
go
exec sp_grantdbaccess 'LMDBUSR', 'LMDBUSR'
go
exec sp_addrolemember [SqlJDBCXAUser], 'LMDBUSR'

use master
go
exec sp_grantdbaccess 'WMMDBUSR', 'WMMDBUSR'
go
exec sp_addrolemember [SqlJDBCXAUser], 'WMMDBUSR'

use master
go
exec sp_grantdbaccess 'ICMADMIN', 'ICMADMIN'
go
exec sp_addrolemember [SqlJDBCXAUser], 'ICMADMIN'

use master
go
exec sp_grantdbaccess 'COMMUNITYUSR', 'COMMUNITYUSR'
go
exec sp_addrolemember [SqlJDBCXAUser], 'COMMUNITYUSR'

use master
go
exec sp_grantdbaccess 'RELEASEUSR', 'RELEASEUSR'
go
exec sp_addrolemember [SqlJDBCXAUser], 'RELEASEUSR'

use master
go
exec sp_grantdbaccess 'CUSTOMIZATIONUSR', 'CUSTOMIZATIONUSR'
go
exec sp_addrolemember [SqlJDBCXAUser], 'CUSTOMIZATIONUSR'


(5) Restart Microsoft SQL Server 2005

4.0 IBM WebSphere Portal Database Transfer

The following actions should occur from the “<PORTAL_HOME>/config” directory

(1) Run the following validate commands:
- WPSconfig.bat validate-database-driver
- WPSconfig.bat validate-database-connection-wmm
- WPSconfig.bat validate-database-connection-wps
- WPSconfig.bat validate-database-connection-jcr
- WPSconfig.bat validate-database-connection-feedback
- WPSconfig.bat validate-database-connection-likeminds


(2) Execute the database-transfer task:
- WPSconfig.bat database-transfer




(3) After the above actions, update the datasource implementation class:

a. Log in to the WebSphere Application Server Administrative Console.

b. Change the “Implementation class name” in the corresponding section:
Resources->JDBC Provides-> Cell level -> wpdbJDBC_sqlserver

to:

com.microsoft.sqlserver.jdbc.SQLServerXADataSource

(See the sample screenshot below: )



(4) Restart the Portal server.


5.0 General Tuning Guidelines:


(1) On the Microsoft SQL Server 2005 machine:

- Update Microsoft SQL Server 2005 statistics for all Portal databases by executing the following query in SQL Server Query Analyzer:

use db_name exec sp_updatestats @resample='resample';

Note: Replace db_name with all database names and run it.

(2) Modify JCR Connection Pool by logging into WebSphere Application Server Administrative Console:

Navigate to:

Resources > JDBC Providers > wpdbJDBC_sqlserver > Data sources > JCRDBDS > Connection pool properties

- Modify the Maximum Connections to 150
(This is a recommended value and may vary depending on your environment.)



(3) Follow these links for more information regarding the tuning of your Portal server, Web server, LDAP server, database server and operating system:

http://www.ibm.com/support/docview.wss?rs=688&uid=swg27008511

http://www.ibm.com/support/docview.wss?rs=688&uid=swg27008780

http://www.ibm.com/developerworks/websphere/library/techarticles/0701_devos/0701_devos.html


Note: Tuning can vary depending on many factors and all of above links are based on the tests that were done on particular systems with a certain load.

 
 
 

Copyright and trademark information
IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.
Rate this page
Please take a moment to complete this form to help us better serve you.
This material provides me with the information I need.




This material is clear and easy to understand.




Did the information help you to achieve your goal?
What updates, improvements, or related information would you like to see in this document?
Your response will be used to improve our document content. Requests for assistance, if applicable, should be submitted through your normal support channel as we cannot respond from this site.
Input the verification number to submit feedback:
Document information
 Product categories:
 Software
 Organizational Productivity, Portals & Collaboration
 Portals
 WebSphere Portal
 Database Connectivity
 Operating system(s):
  AIX, HP-UX, Linux, Solaris, Windows, i5/OS
 Software version:
  6.0.1.4
 Software edition:
  Enable, Extend, Server
 Reference #:
  1316191
 IBM Group:
 Software Group
 Modified date:
 2008-09-15

Translate My Page
 
 

Rate this page

Help us improve this page. Your response will be used to improve our document content. Requests for assistance, if applicable, should be submitted through your normal support channel as we cannot respond from this site.