Transfer SQL Server manually

Transfer SQL Server manually

+
Search Tips | Advanced Search

You are here

Planning for SQL Server
Installing SQL Server
Creating databases and users for SQL Server
Transfer SQL Server manually (Current task)or Transferring between databases manually
Verifying database connections

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.

If you use a remote database server, the WebSphere Portal 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 Delete passwords from configuration scripts. Alternatively, we can specify the password on the command line using the following syntax:

UNIX:

./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, we can use the configuration wizard, a graphical user interface, to transfer data to another supported database. For more information, refer to Configuring WebSphere Portal 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 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

Edit the properties files and enter the values that are appropriate for the environment.
Notes:

The WebSphere Portal database can be used to hold information for applications such as Document Manager, Personalization, Feedback and Likeminds. In this case, the user should 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.

If you are following the recommended architecture in Planning for DB2, the Value listed for each property in the following table represents the DB2 -specific information that is required to configure WebSphere Portal to DB2.

Depending on which database domain has to be configured, dbdomain may need to be replaced by:

release
customization
community
jcr
feedback
likeminds
wmm

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.
Value type: Alphanumeric text string Recommended value:

MS SQL Server: sqlserver

dbdomain.DbUrl
The database URL used to access the WebSphere Portal 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.
Value type: Alphanumeric text string, conforming to standard JDBC URL syntax Recommended value:

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 servers, this value must be set to FEEDBACK, which corresponds to the user FEEDBACK in the database. 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.

Value type: Alphanumeric text string Default:

Release: db2admin

Community: db2admin

Customization: db2admin

JCR: db2admin

WMM: db2admin

Feedback: db2admin

LikeMinds: db2admin

Recommended: wpsdbusr (for databases other than DB2 )
dbdomain.DbPassword
Description: The password for the database administrator.

A value must be set for this property; it cannot be empty.
Value type: Alphanumeric text string
Default value for all domains: ReplaceWithYourDbAdminPwd
dbdomain.DbNode
Description: The value for the node database.

Required for non-Windows platforms when using DB2 only.
Value 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 only, unless you are the system administrator, the values for dbdomain.DbUser and dbdomain.DbSchema must be the same.

For Oracle and SQL Server servers, the value must be set to FEEDBACK.

Value type: Alphanumeric text string
Default: dbdomain
dbdomain.DataSourceName
Description: The name of the data store that WebSphere Portal 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.

The value for dbdomain.DataSourceName should not be set to the same value as dbdomain.DbName.

Value type: Alphanumeric text string
Default values:

Release: wpsdbDS

Community: wpsdbDS

Customization: wpsdbDS

JCR: JCRDS

WMM: wpsdbDS

Feedback: feedback5

LikeMinds: lmdbDS

DB2Content Manager Runtime Edition Database properties
Property Value
jcr.DbSaPassword
The SQL Server System Administrator password.
Required for SQL Server only.
Default: 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
Default: myserver

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
Default: myserver

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
Default: false
sqlserver.DbDriver
The name of class SqlProcessor will use to import SQL files.
Recommended: com.microsoft.jdbc.sqlserver.SQLServerDriver
Windows and UNIX Default value: com.ibm.db2j.jdbc.DB2jDriver (IBM Cloudscape™ )
sqlserver.JdbcProviderName
The name of the JDBC provider that WebSphere Portal uses to communicate with its databases.
Recommended: wpdbJDBC_sqlserver
Default: wpdbJDBC_cloudscape
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

Default: microsoftJDBC/lib/jar
Notes:

In UNIX 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.
Value type: Alphanumeric text string
Recommended: Set this value according to your own environment.
Default: ReplaceWithYourWASUserPwd

Save the file.

Open a command prompt, and change to the directory portal_server_root/config.

Enter the following commands to validate configuration properties.

UNIX:

./WPSconfig.sh validate-database-connection-wps -DDbPassword=password

./WPSconfig.sh validate-database-connection-jcr -DJcrDbPassword=password

./WPSconfig.sh validate-database-connection-feedback -DFeedbackDbPassword=password

./WPSconfig.sh validate-database-connection-likeminds -DLikemindsDbPassword=password

./WPSconfig.sh validate-database-connection-wmm -DWmmDbPassword=password

./WPSconfig.sh validate-database-driver

Windows :

WPSconfig.bat validate-database-connection-wps -DDbPassword=password

WPSconfig.bat validate-database-connection-jcr -DJcrDbPassword=password

WPSconfig.bat validate-database-connection-feedback -DFeedbackDbPassword=password

WPSconfig.bat validate-database-connection-likeminds -DLikemindsDbPassword=password

WPSconfig.bat validate-database-connection-wmm -DWmmDbPassword=password

WPSconfig.bat validate-database-driver

Stop both server1 and the WebSphere Portal server.

Enter the following command:

UNIX:

./stopServer.sh server1 -user admin_userid -password admin_password

Windows:
stopServer.bat server1 -user admin_userid -password admin_password

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

...where server1 is the name of the WebSphere Application Server administrative server, and profile_root is the name given to the WebSphere Application Server profile in use.

Enter the following command:

UNIX:

./stopServer.sh WebSphere_Portal -user admin_userid -password admin_password

Windows:
stopServer.bat WebSphere_Portal -user admin_userid -password admin_password

i5/OS:
stopServer 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 is installed; for example, wp_profile.

To transfer the database, perform this step:

Enter the following commands to run the configuration task.

UNIX:

./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_dbdomain.properties file, and then repeat this step.

If the configuration fails, verify the values in the wpconfig.properties, wpconfig_dbdomain.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.

Enter the following command to restart the server: startServer WebSphere_Portal

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 steps

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. Value type: Alphanumeric text string Recommended value: MS SQL Server: sqlserver
dbdomain.DbUrl	The database URL used to access the WebSphere Portal 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. Value type: Alphanumeric text string, conforming to standard JDBC URL syntax Recommended value: 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 servers, this value must be set to FEEDBACK, which corresponds to the user FEEDBACK in the database. 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. Value type: Alphanumeric text string Default: Release: db2admin Community: db2admin Customization: db2admin JCR: db2admin WMM: db2admin Feedback: db2admin LikeMinds: db2admin Recommended: wpsdbusr (for databases other than DB2 )
dbdomain.DbPassword	Description: The password for the database administrator. A value must be set for this property; it cannot be empty. Value type: Alphanumeric text string Default value for all domains: ReplaceWithYourDbAdminPwd
dbdomain.DbNode	Description: The value for the node database. Required for non-Windows platforms when using DB2 only. Value 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 only, unless you are the system administrator, the values for dbdomain.DbUser and dbdomain.DbSchema must be the same. For Oracle and SQL Server servers, the value must be set to FEEDBACK. Value type: Alphanumeric text string Default: dbdomain
dbdomain.DataSourceName	Description: The name of the data store that WebSphere Portal 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. The value for dbdomain.DataSourceName should not be set to the same value as dbdomain.DbName. Value 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 Default: false
sqlserver.DbDriver	The name of class SqlProcessor will use to import SQL files. Recommended: com.microsoft.jdbc.sqlserver.SQLServerDriver Windows and UNIX Default value: com.ibm.db2j.jdbc.DB2jDriver (IBM Cloudscape™ )
sqlserver.JdbcProviderName	The name of the JDBC provider that WebSphere Portal uses to communicate with its databases. Recommended: wpdbJDBC_sqlserver Default: wpdbJDBC_cloudscape
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 Default: microsoftJDBC/lib/jar Notes: In UNIX 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.