Steps to configure Portal v6.1.x with SQLServer 2005 using Microsoft JDBC drivers 

This guide will detail how to configure WebSphere Portal v6.1.0.x and 6.1.5.x with SQL Server 2005 databases. For more details on supported databases for WebSphere Portal, please review the detailed system requirements for WebSphere Portal:

WebSphere Portal Detailed System Requirements

Install SQL Server 2005 and the JDBC Driver

In this section we will install SQL Server 2005 and the Microsoft JDBC 1.2 Driver. All steps will be completed on Windows.

1. Install SQL Server 2005 following Microsoft documentation. Ensure you install these components:

• SQL Server Database Services
• Integration Services

2. Ensure that TCP/IP connectivity is enabled in the SQL Server Configuration Manager.

3. Download the Microsoft SQL Server JDBC drivers to a temporary directory on the SQL Server server. In this guide we will be using the JDBC 1.2 drivers.

NOTE: These drivers can be retrieved from the Microsoft support website.
NOTE: The JDBC 2.0 driver is only supported with WebSphere Portal version 6.1.0.3 or higher. If you try to use the JDBC 2.0 driver with WebSphere Portal v6.1.0.0, 6.1.0.1, or 6.1.0.2, the configuration will fail.

4. Extract the JDBC driver download into some directory on the server. For example, C:\SQLJDBCDriver\.

5. Locate the sqljdbc_xa.dll file within the JDBC Driver directory. For example, C:\SQLJDBCDriver\sqljdbc_1.2\enu\xa\x86.

6. Copy sqljdbc_xa.dll to the following directory on your SQL Server:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\

7. Ensure that the Distributed Transaction Service has been started in Windows Services.

8. Start the Microsoft SQL Server Management Studio and login as the system administrator.

9. Select File -> Open -> File and select the xa_install.sql file from the Driver directory you created in Step 4. For example, C:\SQLJDBCDriver\sqljdbc_1.2\enu\xa\xa_install.sql.

10. Execute the script.

NOTE: Ignore any warnings you see that say stored procedures cannot be found. This is expected.

11. Launch the Windows Registry Editor by clicking Start -> Run, then by typing regedit and clicking OK.

12. Navigate to the element HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\XADLL

13. From the menu, select Edit -> New -> String Value.

14. Create a new parameter called: sqljdbc_xa.dll

15. Change the value of the parameter to the path you placed sqljdbc_xa.dll in from Step 6.

16. Ensure XA Transactions are enabled in the Windows environment. Click Start -> Run, type dcomcnfg, and click OK to open Component Services.

17. Expand Component Services -> Computers. Right-click on My Computer and select Properties.

18. Click the MSDTC tab, and then click Security Configuration.

19. Ensure the Enable XA Transactions checkbox is checked.

20. Click the OK button and restart the SQL Server to ensure the changes are set.

21. Copy the database driver jar files from the SQL Server server to the WebSphere Portal server in some directory. For example,

C:\SQLJDBCDriver\sqljdbc_1.2\enu\sqljdbc.jar

Modifying the ConfigEngine properties

In this section, you will prepare the wkplc_comp.properties and wkplc_dbtype.properties files with the SQL Server information. Several of these properties, such as the Database Names, Users, and Schemas, do not exist yet in the SQL Server database. They will be created in the 'Creating Databases' and 'Setting up Databases' sections.

In Portal v6.1.x, there are six Portal database domains (where "domain" refers to a logical grouping of data):

Release
Community
Customization
JCR
Likeminds
Feedback

You can organize this data in however many databases you would like. The recommendation is to use six separate databases.

In this guide, we will be using six databases:

REL61DB
COM61DB
CUST61DB
JCR61DB
LM61DB
FB61DB

Complete the following steps:

1. Backup the following files from the <wp_profile>/ConfigEngine/properties directory:

wkplc_comp.properties
wkplc_dbtype.properties

2. Edit the <wp_profile>/ConfigEngine/properties/wkplc_comp.properties and update the following properties:

domain.DbType - The type of database you are configuring. This value should be 'sqlserver2005'.
domain.DbName - The name of the database for this domain. This database will be created in the 'Creating Databases' section.
NOTE: This value should be present in the DbUrl.
domain.DbSchema - The schema identifier for objects within the database for this domain. The schema will be created in the 'Setting Up Databases' section.
NOTE: If you are using a single database or have combined domains into a single database, this value MUST be unique for each domain.
NOTE: This value should equal the domain.DbUser value.
domain.DataSourceName - The name of the datasource that will be created in the WebSphere Application Server configuration.
NOTE: Do NOT use any of these values: releaseDS, communityDS, customizationDS, jcrDS, likemindsDS, feedbackDS
domain.DbUrl - The url used to access your database.
NOTE: The URL must use this exact syntax for SQL Server: jdbc:sqlserver://<sqlserver_hostname>:<port>;SelectMethod=cursor;DatabaseName=<DbName>
Any other syntax will cause the database transfer to fail.
NOTE: The value you set for DbName must be used in this url.
domain.DbUser - The name of the user that will access this database. This user does not exist yet and will be created in the 'Setting Up Databases' section.
NOTE: This value should equal the domain.DbSchema.
domain.DbPassword - The password of the DbUser.

For this guide, the following values were used:

feedback.DbType=sqlserver2005
feedback.DbName=fb61db
feedback.DbSchema=fbdbusr
feedback.DataSourceName=wpdbDS_fdbk
feedback.DbUrl=jdbc:sqlserver://sqlserver.ibm.com:1433;SelectMethod=cursor;DatabaseName=fb61db
feedback.DbUser=fbdbusr
feedback.DbPassword=password

likeminds.DbType=sqlserver2005
likeminds.DbName=lm61db
likeminds.DbSchema=lmdbusr
likeminds.DataSourceName=wpdbDS_lmdb
likeminds.DbUrl=jdbc:sqlserver://sqlserver.ibm.com:1433;SelectMethod=cursor;DatabaseName=lm61db
likeminds.DbUser=lmdbusr
likeminds.DbPassword=password

release.DbType=sqlserver2005
release.DbName=rel61db
release.DbSchema=reldbusr
release.DataSourceName=wpdbDS_reldb
release.DbUrl=jdbc:sqlserver://sqlserver.ibm.com:1433;SelectMethod=cursor;DatabaseName=rel61db
release.DbUser=reldbusr
release.DbPassword=password

community.DbType=sqlserver2005
community.DbName=com61db
community.DbSchema=comdbusr
community.DataSourceName=wpdbDS_comdb
community.DbUrl=jdbc:sqlserver://sqlserver.ibm.com:1433;SelectMethod=cursor;DatabaseName=com61db
community.DbUser=comdbusr
community.DbPassword=password

customization.DbType=sqlserver2005
customization.DbName=cus61db
customization.DbSchema=cusdbusr
customization.DataSourceName=wpdbDS_cusdb
customization.DbUrl=jdbc:sqlserver://sqlserver.ibm.com:1433;SelectMethod=cursor;DatabaseName=cust61db
customization.DbUser=cusdbusr
customization.DbPassword=password
 
jcr.DbType=sqlserver2005
jcr.DbName=jcr61db
jcr.DbSchema=icmadmin
jcr.DataSourceName=wpdbDS_jcrdb
jcr.DbUrl=jdbc:sqlserver://sqlserver.ibm.com:1433;SelectMethod=cursor;DatabaseName=jcr61db
jcr.DbUser=icmadmin
jcr.DbPassword=password 

3. (Optional) Edit the following properties in <wp_profile>/ConfigEngine/properties/wkplc_comp.properties if you intend to transfer the Portal databases from any database other than Derby.

NOTE: These values by default are set for the out-of-the-box derby configuration. If you are configuring Portal for an external database for the first time, you should NOT modify these properties. However if you have already configured Portal for another database, such as DB2, and you want to transfer to SQL Server, then update the source properties to reflect your current database settings.

source.domain.DbType - The type of database you are currently configured to. This is 'derby' by default.
source.domain.DbName - The name of the database this domain is currently using.
source.domain.DbSchema - The current schema identifier for objects within the database for this domain.
source.domain.DataSourceName - The name of the datasource that is currently in use in the WebSphere Application Server configuration.
source.domain.DbUrl - The url currently used to access your database.
source.domain.DbUser - The name of the user that currently accesses this database.
source.domain.DbPassword - The password of the source DbUser.

In this guide, we will not be editing these properties.

4. Save the wkplc_comp.properties file.

5. Edit the wkplc_dbtype.properties file and modify the following property to point to the location that you copied the jar files from Step 21 in the 'Installing SQL Server 2005' section:

sqlserver2005.DbLibrary=C:\SQLJDBCDriver\sqljdbc_1.2\enu\sqljdbc.jar

6. Save the wkplc_dbtype.properties file.

Creating Databases

In this section, you will create the databases in the SQL Server environment to be used by WebSphere Portal. For this guide, six databases will be created:

REL61DB
COM61DB
CUST61DB
JCR61DB
LM61DB
FB61DB

This can be done either automatically with a WebSphere Portal ConfigEngine script, or you can do this manually.

Automatically Create the Databases

WebSphere Portal provides a ConfigEngine script to automatically create the databases called 'create-database'.

1. Edit the <wp_profile>/ConfigEngine/properties/wkplc_comp.properties file and set the following properties for each domain:

domain.DBA.DbUser - The DBA user, for example 'sa'.
domain.DBA.DbPassword - The DBA password
domain.DbHome - The root of your SQL Server database file structure, for example C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL
NOTE: This path must use double backslashes (\\) instead of single forward slashes (/)
domain.AdminUrl - The SQL Server URL without the database attached. For example, jdbc:sqlserver://sqlserver.ibm.com:1433

2. For the feedback and likeminds domains, you must set an additional property:

domain.DbHostName - The hostname of the SQL Server. For example, sqlserver.ibm.com.

3. Change directories to the /ConfigEngine directory, and execute the following command:

Windows: ConfigEngine.bat create-database
UNIX: ./ConfigEngine.sh create-database

Manually Create the Databases

1. Start the Microsoft SQL Server Management Studio and login as the system administrator.

2. Expand the tree in the Object Explorer window to see Databases.

3. Right-click on Databases and select "New Database..."

4. Enter the database name. This should match the name you provided for domain.DbName in the 'Modifying the ConfigEngine properties' section.

5. On the Options section, set the Collation value so that it matches your environment. For English systems, select SQL_Latin1_General_CP1_CS_AS:

NOTE: Ensure this is set to "Case-sensitive" (CS).

6. Click OK to save the database changes.

7. Repeat Steps 3-6 for each database you need to create.

Setting up the databases

In this section we will prepare the databases for configuration with WebSphere Portal. This can be done either automatically or manually.

Automatically set up the databases

WebSphere Portal provides a ConfigEngine script to automatically setup the databases called 'setup-database'. This script does all of the following:

• Creates the users defined for domain.DbUser for each domain
• Creates the schemas defined for domain.DbSchema for each domain.
• Sets the users as dbadmins for their respective databases.
• Enables XA authority for the users

This script requires DBA access to the databases.

1. Edit the <wp_profile>/ConfigEngine/properties/wkplc_comp.properties file and set the following properties for each domain:

domain.DBA.DbUser - The DBA user, for example 'sa'.
domain.DBA.DbPassword - The DBA password
domain.DbHome - The root of your SQL Server database file structure, for example C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL
NOTE: This path must use double backslashes (\\) instead of single forward slashes (/)
domain.AdminUrl - The SQL Server URL without the database attached. For example, jdbc:sqlserver://sqlserver.ibm.com:1433

2. For the feedback and likeminds domains, you must set an additional property:

domain.DbHostName - The hostname of the SQL Server. For example, sqlserver.ibm.com.

3. Change directories to the /ConfigEngine directory, and execute the following command:

Windows: ConfigEngine.bat setup-database
UNIX: ./ConfigEngine.sh setup-database

Manually set up the databases

Complete the following steps to manually set up the databases.

1. Start the Microsoft SQL Server Management Studio and login as the system administrator.
2. Expand the tree in the Object Explorer window to see Databases -> releaseDatabase -> Security -> Schemas
3. Right-click on your Schemas and select "New Schema..."

4. Create a new schema with the value you provided for release.DbSchema in wkplc_comp.properties (from the 'Modifying the ConfigEngine Properties' section).

5. Repeat Steps 2-4 for the community, customization, jcr, likeminds, and feedback domains. Ensure you create the schemas in the appropriate databases.

6. Expand the tree in the Object Explorer window to see Databases -> System Databases -> master -> Security -> Schemas

7. Right click on Schemas and select "New Schema..."

8. Create a schema here for the release domain. Use the same value you provided for release.DbSchema in wkplc_comp.properties (from the 'Modifying the ConfigEngine Properties' section).

9. Repeat Steps 6-8 for the community, customization, jcr, likeminds and feedback schemas. These all must be created in the "master" database.

10. Expand the tree in the Object Explorer window to see Security -> Logins.

11. Right-click on Logins and select "New Login..."

12. Provide the name of the release user. Use the same value you provided for release.DbUser in wkplc_comp.properties (from the 'Modifying the ConfigEngine Properties' section).

13. If you are using SQL Server Authentication, select the radio button for this and enter the password. This should be the same password you set for release.DbPassword.

14. Set the "default database" to the release database.

15. Click the link on the left hand side for "Server Roles". Ensure that NO server roles are checked:

16. Click the link on the left hand side for "User Mapping".

17. Check the box for the release database and set the schema to the release schema name.

18. For Database Role Membership, check the boxes for "db_owner" and "public".

19. Still on the 'User Mapping' window, check the box next to 'master' and set the schema name to the release schema.

20. For Database Role Membership, check the boxes for 'public' and 'SqlJDBCXAUser'

21. Click 'OK' to create the user.

22. Repeat 10-21 to create the users for the other domains: community, customization, jcr, likeminds and feedback.

When you are finished, each user should be the db_owner of its respective database AND have SqlJDBCXAUser access to the 'master' database. This is very important for Portal functionality after you configure Portal to use SQL Server 2005.

Assigning Custom Tablespaces

This section is optional.

In this section, you have the option to assign custom tablespaces to the domains. By default, the tables that are created by the configuration are created within the default tablespace of the user. For example, if your release domain user had a default tablespace of 'Users', then all of the release tables will be created in the 'Users' tablespace. If you would like to customize where these tables are created, complete the following steps:

1. Create all of the tablespaces you would like to use

2. In the <wp_profile>/PortalServer/config/tablespaces directory, edit each of these files to assign a custom tablespace to each individual table and index:

release.space_mapping.properties
community.space_mapping.properties
customization.space_mapping.properties
jcr.space_mapping.properties
likeminds.space_mapping.properties
feedback.space_mapping.properties

3. For each entry in these properties files, at your tablespace name. IMPORTANT: For SQL Server, prepend "ON" to each entry. For example if I wanted my release tables in a tablespace called RELSPACE, my entries would look like:

release.ACTION_DESC.tablespace=ON RELSPACE
release.ACTION_DESC_LOD.tablespace=ON RELSPACE
release.ACTION_SET.tablespace=ON RELSPACE
.
.
.

4. Save the files when you have completed updating all of them.

Configuring WebSphere Portal to use SQL Server

In this section, you will transfer the WebSphere Portal server data that exists in the current database (Derby by default) to the SQL Server server. This is accomplished by a ConfigEngine script called 'database-transfer'. This script does all of the following:

• Connects to the target database (SQL Server in this case) and drops all objects within them to ensure we have a clean database to transfer to.
• Creates the necessary tables, indexes, etc in the target databases for each domain.
• Transfers the data from the source database (Derby by default) to the target database (SQL Server) for each domain.
• Creates a JDBC Provider and datasources in the WebSphere Application Server configuration
• Maps the datasources to the appropriate applications. For example the release, customization, community and jcr datasources are mapped to wps.ear.

Complete the following steps:

1. Stop the WebSphere_Portal server by running the following command from the <wp_profile>/bin directory:

Windows: stopServer.bat WebSphere_Portal -user wasadmin -password password
UNIX: ./stopServer.sh WebSphere_Portal -user wasadmin -password password

2. Change directories to the <wp_profile>/ConfigEngine directory

3. Enter the following command to validate the database driver, where WasPassword is your WebSphere Application Server administrator password:

Windows: ConfigEngine.bat validate-database-driver -DWasPassword=password
UNIX: ./ConfigEngine.sh validate-database-driver -DWasPassword=password

4. Enter the following command to validate the database connection, where WasPassword is your WebSphere Application Server administrator password:

Windows: ConfigEngine.bat validate-database-connection -DWasPassword=password
UNIX: ./ConfigEngine.sh validate-database-connection -DWasPassword=password

5. Enter the following command to transfer the database configuration to SQL Server, where WasPassword is your WebSphere Application Server administrator password:

IMPORTANT: Do not run this script as a background process. The action-validate-database sub-task may hang if executed as a background process.

Windows: ConfigEngine.bat database-transfer -DWasPassword=password
UNIX: ./ConfigEngine.sh database-transfer -DWasPassword=password

NOTE: This command by default transfers all six domains. If you would like to transfer individual domains only or a subset of the six domains, you can specify the -DTransferDomainList parameter in the command with a list of domains you want to transfer. For example if you only wanted to transfer release, community, and customization:

ConfigEngine.bat database-transfer -DWasPassword=password -DTransferDomainList=release,community,customization

6. Start the WebSphere Portal server from the <wp_profile>/bin directory:

Windows: startServer.bat WebSphere_Portal
UNIX: ./startServer.sh WebSphere_Portal

7. Clustered environments only. If you have performed these steps in a clustered environment, you must also complete the following steps to ensure the secondary nodes are updated properly:

• Copy the wp_profile_root/PortalServer/jcr/lib/com/ibm/icm/icm.properties file from the primary node and replace the icm.properties file on each secondary node with the new file from the primary node.
• Restart the secondary cluster members.