Transferring SQL Server manually

Portal Express, Version 6.0
Operating systems: i5/OS, Linux, Windows

Transferring SQL Server manually

This section provides information on how to manually transfer data to the SQL Server database you have installed and set up.

Before you begin
Before configuring databases, ensure that the following prerequisites are met:

Supported database software is installed.
Databases and users are correctly setup for use by IBM^® WebSphere^® Portal Express.
If you use a remote database server, the WebSphere Portal Express and Member Manager databases must exist on the server.

Password Considerations

For security reasons, you should not store passwords in the wpconfig.properties, wpconfig_dbdomain.properties, and wpconfig_dbtype.properties files. IBM recommends that you edit each of the properties files prior to running a configuration task, inserting the passwords needed for that task. After the task has run, you should delete all passwords from each file. For more information, see Deleting passwords from configuration scripts.
Alternatively, you can specify the password on the command line using the following syntax:

Linux:
./WPSconfig.sh task_name -Dpassword_property_key=password_value
Windows:
WPSconfig.bat task_name -Dpassword_property_key=password_value
As with other properties, each password property must have the -D prefix and be set equal to (=) a value. If you have multiple properties in a single command, use a space character between each -Dproperty=value setting. Tips:

The steps for transferring data to another supported database section provides instructions for manually transferring data. Instead of performing the following steps, you can use the configuration wizard, a graphical user interface, to transfer data to another supported database. For more information, refer to Configuring WebSphere Portal Express with the configuration wizard.
A configuration template might exist to support these instructions. Refer to the portal_server_root/config/helpers directory for available configuration templates. Use the configuration template to update the wpconfig.properties, wpconfig_dbdomain.properties, and wpconfig_dbtype.properties files, as described in Configuration program, according to the property descriptions and recommended values provided here. If you do not want to use a configuration template, follow theses instructions as written.

Steps for transferring data to another supported database
Follow these steps to transfer WebSphere Portal Express and Member Manager databases to Microsoft^® SQL Server Enterprise Edition.
If transfer should fail for any reason while moving JCR data, manually drop the tables and retry the database transfer. See Troubleshooting Databases for more information.
Locate the following files and create a backup copy of each before changing any values:

portal_server_root/config/wpconfig.properties
portal_server_root/config/wpconfig_dbdomain.properties
portal_server_root/config/wpconfig_dbtype.properties
Use a text editor to open the properties files and enter the values that are appropriate for your environment. Tip:

IBM recommends to print out the properties tables in this step for reference while modifying the properties files.
Make sure to enter the appropriate values for each instance of each property. In wpconfig_dbdomain.properties, most properties are repeated for each domain.

Notes:

The WebSphere Portal Express database can be used to hold information for applications such as Document Manager, Personalization, Feedback and LikeMinds. In this case, the user should use similar naming conventions for property values such as release.DbName, jcr.DbName, feedback.DbName, and likeminds.DbName. For example:

releaseDbName=wpsdb
jcr.DbName=jcrdb
feedback.DbName=fdbkdb
likeminds.DbName=lkmddb

Do not change any settings other than those that are specified in these steps. For instructions on working with these files, see Configuration properties reference for a complete properties reference, including default values.
If you are using a remote database, be sure to enter the values for the remote server.
Use / instead of \ for all platforms.
Some values, shown here in italics, might need to be modified to your specific environment.
There might be additional database properties other than those listed here. Only change the properties within this table; skip all other properties.
The value listed for each property in the following tables represents the specific information that is required to configure WebSphere Portal Express to your target database.
Depending on which database domain has to be configured, dbdomain may need to be replaced by:

release
customization
community
jcr
feedback
likeminds
wmm

The values for at least one of the following properties must be unique for the release, customization, community, and jcr domains:

dbdomain.DbType
dbdomain.DbName
dbdomain.DbUrl
dbdomain.DbSchema
If you use the same values for all four properties across the release, customization, community, and JCR domains, the database-transfer task will fail. If DbUser, DbUrl, and DbPassword are not the same across domains, the value for DataSourceName must differ from the DataSourceName of the other domains. In other words, this value must be unique for the database domain.
Do not modify the values for the sync and designer database domains. These domains are not used by WebSphere Portal Express and the values should remain pointing to the default database.

Update the following properties in the file wpconfig_dbdomain.properties.

Database domain properties
Property Value
dbdomain.DbType The type of database used to store information for WebSphere Portal Express.

Type: Alphanumeric text string

Recommended:

SQL Server: sqlserver

dbdomain.DbName The name of the WebSphere Portal Express domain database and schema.

This value is also the database element in the dbdomain.DbUrl property.

Type: Alphanumeric text string

If you change the name of the WebSphere Portal Express data source due to a database migration, manually update this property in the portal_server_root/config/wpconfig_dbdomain.properties file to maintain the proper resource reference mapping.

Recommended value for all domains:

Release: release
Community: comm
Customization: cust
JCR: jcrdb
WMM: wmmdb
Feedback: fdbkdb
LikeMinds: lmdb

dbdomain.DbUrl The database URL used to access the WebSphere Portal Express database with JDBC. The value must conform to standard JDBC URL syntax.

For Oracle, Oracle RAC, and SQL Server, hostname is the name of the remote server and port is the port where the appropriate database instance is listening.

The database element of this value should match the value of dbdomain.DbName.
Type: Alphanumeric text string, conforming to standard JDBC URL syntax

Recommended:

SQL Server: jdbc:microsoft:sqlserver://YourDatabaseServer:1433;DatabaseName=wpsdb

dbdomain.DbUser The user ID for the database administrator.

Notes:

For SQL Server and non-wmm databases only, unless you are the system administrator, the values for dbdomain.DbUser and dbdomain.DbSchema must be the same.
For Oracle and SQL Server, if the user you are using is an administrative user that has authority over the FEEDBACK schema, the administrative user should be entered for the dbdomain.DbUser property.
For Oracle only: For non-feedback domains, DbSchema and DbUser MUST be the same. For Feeback domains, the default schema name is FEEDBACK. If the value is set to something besides FEEDBACK, you also have to set the schemaName property in <wps_home>/shared/app/config/services/FeedbackService.properties to the new schema.

Type: Alphanumeric text string

Recommended:

Release: releaseusr
Community: communityusr
Customization: customizationusr
JCR: icmadmin
WMM: wmmdbusr
Feedback: feedback
LikeMinds: lmdbusr

dbdomain.DbPassword
The password for the database administrator.

A value must be set for this property; it cannot be empty.
Type: Alphanumeric text string

Default for all domains: ReplaceWithYourDbAdminPwd

dbdomain.DbNode
The value for the node database.

Required for non-Windows platforms when using DB2 only.
Type: Alphanumeric text string
Default values:

Release: wpsNode
Community: wpsNode
Customization: wpsNode
JCR: wpsNode
WMM: wpsNode
Feedback value: pznNode
LikeMinds value: pznNode

dbdomain.DbSchema The schema name of the database domain.

Notes:

This property is not used on Member Manager, which only uses the dbdomain.DbUser name as the schema.
Follow the documentation of the target database management system in order to define a valid schema name as restrictions apply for some database management systems.
For SQL Server and non-wmm databases only, unless you are the system administrator, the values for dbdomain.DbUser and dbdomain.DbSchema must be the same.
For Oracle only: For non-feedback domains, DbSchema and DbUser MUST be the same. For Feeback domains, the default schema name is FEEDBACK. If the value is set to something besides FEEDBACK, you also have to set the schemaName property in <wps_home>/shared/app/config/services/FeedbackService.properties to the new schema.

Type: Alphanumeric text string

dbdomain.DataSourceName
The name of the data store that WebSphere Portal Express uses to communicate with its databases.

Notes:

If the DbUser, DbUrl, and DbPassword are not the same across domains, this value must differ from the DataSourceName of the other domains. In other words, this value must be unique for the database domain.
The value for dbdomain.DataSourceName should not be set to the same value as dbdomain.DbName.
i5/OS: The value for dbdomain.DataSourceName must match the postfix of dbdomain.DbSchema name. For example, if release.DbSchema=REL60, then release.DataSourcename=REL60. This name must be unique for each database schema.
Follow the documentation of the target database management system in order to define a valid schema name, as restrictions apply for some database management systems.

Type: Alphanumeric text string
Default values:

Release: wpsdbDS
Community: wpsdbDS
Customization: wpsdbDS
JCR: JCRDS
WMM: wpsdbDS
Feedback: feedback5
LikeMinds: lmdbDS

DB2^® Content Manager Runtime Edition Database properties
Property Value
jcr.DbSaPassword The SQL Server System Administrator password.

This property is optional for SQL Server and SQL Server 2005
Default value: ReplaceWithYourDbAdminPwd

Personalization and Feedback Database properties
Property Value
feedback.DbHostName The fully-qualified host name of the database server that is hosting the Feedback database.

Required for:

SQL Server

Recommended: YourDatabaseServer

LikeMinds Database properties
Property Value
likeminds.DbHostName The fully-qualified host name of the database server that is hosting the LikeMinds database.

Required for:

SQL Server

Recommended: YourDatabaseServer

Update the following properties in the file wpconfig_dbtype.properties.
Database type properties
Property Value
DbSafeMode This only applies to database-specific tasks. If this property is set to true, database-specific tasks, such as create/modify/drop database, will not be performed. The property should be used if a pre-configured database (from a previous installation) already exists. If the property is set to false, the database will be updated and the pre-existing database configuration will be overwritten.

Recommended: false

sqlserver.DbDriver The name of class SqlProcessor will use to import SQL files.

Recommended: com.microsoft.jdbc.sqlserver.SQLServerDriver

sqlserver.JdbcProviderName The name of the JDBC provider that WebSphere Portal Express uses to communicate with its databases.

Recommended: wpdbJDBC_sqlserver
sqlserver.DbLibrary
The directory and name of the .zip or .jar file containing the JDBC driver class.

Recommended:
C:/Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/mssqlserver.jar;C:/Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/msbase.jar;C:/Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/msutil.jar
Notes:

In Linux environments, use a colon (:) instead of a semi-colon (;) to separate multiple paths.
If you are pointing to a remote server, be sure to reference the correct path.
Update the following properties in the file wpconfig.properties.

WebSphere Application Server properties
Property Description
WasPassword The password for WebSphere Application Server security authentication.

If a value is specified for WasPassword, a value must also be specified for WasUserid. If WasPassword is left blank, WasUserid must also be left blank.
Type: Alphanumeric text string

Recommended: Set this value according to a environment.

Default: ReplaceWithYourWASUserPwd

Update the following properties in the file wpconfig_sourceDb.properties

Property Description
source.dbdomain.DbPassword The password for the source database.

Type: Alphanumeric text string

Recommended: Set this value according to a environment.
This value is the same as the administrator password if the default user was used during install.
Save the file.
Open a command prompt, and change to the directory portal_server_root/config.
Enter the following commands to validate configuration properties.

Linux:

./WPSconfig.sh validate-database-connection-wps -Drelease.DbPassword=password -Dcustomization.DbPassword=password -Dcommunity.DbPassword=password -Djcr.DbPassword=password
./WPSconfig.sh validate-database-connection-jcr -Djcr.DbPassword=password
./WPSconfig.sh validate-database-connection-feedback -Dfeedback.DbPassword=password
./WPSconfig.sh validate-database-connection-likeminds -Dlikeminds.DbPassword=password
./WPSconfig.sh validate-database-connection-wmm -Dwmm.DbPassword=password
./WPSconfig.sh validate-database-driver

Windows:

WPSconfig.bat validate-database-connection-wps -Drelease.DbPassword=password -Dcustomization.DbPassword=password -Dcommunity.DbPassword=password -Djcr.DbPassword=password
WPSconfig.bat validate-database-connection-jcr -Djcr.DbPassword=password
WPSconfig.bat validate-database-connection-feedback -Dfeedback.DbPassword=password
WPSconfig.bat validate-database-connection-likeminds -Dlikeminds.DbPassword=password
WPSconfig.bat validate-database-connection-wmm -Dwmm.DbPassword=password
WPSconfig.bat validate-database-driver

Stop both server1 and the WebSphere Portal Express server.

Enter the following command:

Linux:
./stopServer.sh server1 -user admin_userid -password admin_password
Windows:
stopServer.bat server1 -user admin_userid -password admin_password
i5/OS:
stopServer.sh server1 -profileName profile_root -user admin_userid -password admin_password
where profile_root is the name of the WebSphere Application Server profile where WebSphere Portal Express is installed; for example, wp_profile.

server1 is the name of your WebSphere Application Server administrative server.
Enter the following command:

Linux:
./stopServer.sh WebSphere_Portal -user admin_userid -password admin_password
Windows:
stopServer.bat WebSphere_Portal -user admin_userid -password admin_password
i5/OS:
stopServer.sh WebSphere_Portal -profileName profile_root -user admin_userid -password admin_password
where profile_root is the name of the WebSphere Application Server profile where WebSphere Portal Express is installed; for example, wp_profile.

To transfer the database, perform this step:

Change to the directory portal_server_root /config.
Enter the following commands to run the configuration task.

Linux:
./WPSconfig.sh database-transfer -Drelease.DbPassword=password-Dcustomization.DbPassword=password -Dcommunity.DbPassword=password -Djcr.DbPassword=password -Dwmm.DbPassword=password -Dfeedback.DbPassword=password -Dlikeminds.DbPassword=password
Windows:
WPSconfig.bat database-transfer -Drelease.DbPassword=password -Dcustomization.DbPassword=password-Dcommunity.DbPassword=password -Djcr.DbPassword=password -Dwmm.DbPassword=password -Dfeedback.DbPassword=password -Dlikeminds.DbPassword=password

After running this task, a message indicating success should result. Check the log files to verify that this task was successful. If the configuration fails, verify the values in the wpconfig.properties, wpconfig_dbdomain.properties, wpconfig_sourceDb.properties, and wpconfig_dbtype.properties files, and then repeat this step.

If the task continues to fail, refer to Unable to transfer database to SQL Server in the Troubleshooting databases page.
Change to the directory was_profile_root/bin.
Restart the WebSphere Portal Express server:

Enter the following command:

Linux:
./startServer.sh WebSphere_Portal
Windows:
startServer.bat WebSphere_Portal
i5/OS:
startServer.sh WebSphere_Portal -profileName profile_root
where profile_root is the name of the WebSphere Application Server profile where WebSphere Portal Express is installed; for example, wp_profile.

Update MS SQL Server statistics for Portal, JCR and WMM databases by executing the following query in Query Analyzer: use db_name exec sp_updatestats @resample='resample';
Next stepsYou have completed this step. Continue to the next step.

Verifying database connections

Parent topic:
Transferring all domains

Database domain properties
Property	Value
dbdomain.DbType	The type of database used to store information for WebSphere Portal Express. Type: Alphanumeric text string Recommended: SQL Server: sqlserver
dbdomain.DbName	The name of the WebSphere Portal Express domain database and schema. This value is also the database element in the dbdomain.DbUrl property. Type: Alphanumeric text string If you change the name of the WebSphere Portal Express data source due to a database migration, manually update this property in the portal_server_root/config/wpconfig_dbdomain.properties file to maintain the proper resource reference mapping. Recommended value for all domains: Release: release Community: comm Customization: cust JCR: jcrdb WMM: wmmdb Feedback: fdbkdb LikeMinds: lmdb
dbdomain.DbUrl	The database URL used to access the WebSphere Portal Express database with JDBC. The value must conform to standard JDBC URL syntax. For Oracle, Oracle RAC, and SQL Server, hostname is the name of the remote server and port is the port where the appropriate database instance is listening. The database element of this value should match the value of dbdomain.DbName. Type: Alphanumeric text string, conforming to standard JDBC URL syntax Recommended: SQL Server: jdbc:microsoft:sqlserver://YourDatabaseServer:1433;DatabaseName=wpsdb
dbdomain.DbUser	The user ID for the database administrator. Notes: For SQL Server and non-wmm databases only, unless you are the system administrator, the values for dbdomain.DbUser and dbdomain.DbSchema must be the same. For Oracle and SQL Server, if the user you are using is an administrative user that has authority over the FEEDBACK schema, the administrative user should be entered for the dbdomain.DbUser property. For Oracle only: For non-feedback domains, DbSchema and DbUser MUST be the same. For Feeback domains, the default schema name is FEEDBACK. If the value is set to something besides FEEDBACK, you also have to set the schemaName property in <wps_home>/shared/app/config/services/FeedbackService.properties to the new schema. Type: Alphanumeric text string Recommended: Release: releaseusr Community: communityusr Customization: customizationusr JCR: icmadmin WMM: wmmdbusr Feedback: feedback LikeMinds: lmdbusr
dbdomain.DbPassword	The password for the database administrator. A value must be set for this property; it cannot be empty. Type: Alphanumeric text string Default for all domains: ReplaceWithYourDbAdminPwd
dbdomain.DbNode	The value for the node database. Required for non-Windows platforms when using DB2 only. Type: Alphanumeric text string Default values: Release: wpsNode Community: wpsNode Customization: wpsNode JCR: wpsNode WMM: wpsNode Feedback value: pznNode LikeMinds value: pznNode
dbdomain.DbSchema	The schema name of the database domain. Notes: This property is not used on Member Manager, which only uses the dbdomain.DbUser name as the schema. Follow the documentation of the target database management system in order to define a valid schema name as restrictions apply for some database management systems. For SQL Server and non-wmm databases only, unless you are the system administrator, the values for dbdomain.DbUser and dbdomain.DbSchema must be the same. For Oracle only: For non-feedback domains, DbSchema and DbUser MUST be the same. For Feeback domains, the default schema name is FEEDBACK. If the value is set to something besides FEEDBACK, you also have to set the schemaName property in <wps_home>/shared/app/config/services/FeedbackService.properties to the new schema. Type: Alphanumeric text string
dbdomain.DataSourceName	The name of the data store that WebSphere Portal Express uses to communicate with its databases. Notes: If the DbUser, DbUrl, and DbPassword are not the same across domains, this value must differ from the DataSourceName of the other domains. In other words, this value must be unique for the database domain. The value for dbdomain.DataSourceName should not be set to the same value as dbdomain.DbName. i5/OS: The value for dbdomain.DataSourceName must match the postfix of dbdomain.DbSchema name. For example, if release.DbSchema=REL60, then release.DataSourcename=REL60. This name must be unique for each database schema. Follow the documentation of the target database management system in order to define a valid schema name, as restrictions apply for some database management systems. Type: Alphanumeric text string Default values: Release: wpsdbDS Community: wpsdbDS Customization: wpsdbDS JCR: JCRDS WMM: wpsdbDS Feedback: feedback5 LikeMinds: lmdbDS

Database type properties
Property	Value
DbSafeMode	This only applies to database-specific tasks. If this property is set to true, database-specific tasks, such as create/modify/drop database, will not be performed. The property should be used if a pre-configured database (from a previous installation) already exists. If the property is set to false, the database will be updated and the pre-existing database configuration will be overwritten. Recommended: false
sqlserver.DbDriver	The name of class SqlProcessor will use to import SQL files. Recommended: com.microsoft.jdbc.sqlserver.SQLServerDriver
sqlserver.JdbcProviderName	The name of the JDBC provider that WebSphere Portal Express uses to communicate with its databases. Recommended: wpdbJDBC_sqlserver
sqlserver.DbLibrary	The directory and name of the .zip or .jar file containing the JDBC driver class. Recommended: C:/Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/mssqlserver.jar;C:/Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/msbase.jar;C:/Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/msutil.jar Notes: In Linux environments, use a colon (:) instead of a semi-colon (;) to separate multiple paths. If you are pointing to a remote server, be sure to reference the correct path.