Windows stand-alone: Configure the portal to use SQL Server

This section provides information on how to manually transfer data from the default database to the SQL Server database. Follow these steps to transfer Portal and Java Content Repository databases to SQL Server. As an alternative to the manual database transfer procedure described here, we can use the configuration wizard to complete the database transfer task.

1. Install SQL Server

2. Set up databases and users

3. Property files are modified.

4. Modify SQL Server database properties

5. Set up SQL Server automatically or manually

6. By default, open_cursors is set to 1500. You may need to increase this value based on the table count in the Java Content Repository schema.

7. Validate configuration properties.
   cd WP_PROFILE/ConfigEngine
   ConfigEngine.bat validate-database -DWasPassword=foo

   To specify the domains to validate...

   ConfigEngine.bat validate-database -DWasPassword=foo -DTransferDomainList=jcr

   To validate all domains, do not specify this parameter.

8. Stop the WebSphere_Portal server:
   cd WP_PROFILE\bin
   stopServer.bat WebSphere_Portal -username wpadmin -password foo

9. Transfer the database:

   Important: Do not execute the database-transfer task as a background process. This might cause the task to stall.

   cd WP_PROFILE\ConfigEngine.
   ConfigEngine.bat database-transfer -DWasPassword=foo

   We can include only the domains to transfer. For example, to transfer only the JCR domain:

   ConfigEngine.bat database-transfer -DTransferDomainList=jcr -DWasPassword=foo

   Log output is written to:

   WP_PROFILE\ConfigEngine\log\ConfigTrace.log

   If the configuration fails, verify the values in the wkplc.properties, wkplc_dbdomain.properties, and wkplc_dbtype.properties files and then repeat this step.

10. If dbdomain.DbRuntimeUser is set that user must have sufficient database user privileges:

    1. To manually grant database user privileges:

       1. Copy the appropriate template files to a work directory:

          • createRuntimeRoleForDifferentSchema.sql

            If the name of the database user and the schema name are not the same.

          • createRuntimeRoleForSameSchema.sql

            If the name of the database user and the schema name are the same.

          Files are in the following directories, where dbms is the database management system, and domain represents the database domains we are configuring (release, customization, community, jcr, feedback, and likeminds).

          • PORTAL_HOME\base\wp.db.impl\config\templates\setupdb\dbms\domain
          • PORTAL_HOME\pzn\prereq.pzn\config\templates\setupdb\dbms\domain

          JCR database domain: For the JCR database domain, copy grantPermissionsToRuntimeRoleStatic.sql from the following directory:

          PORTAL_HOME/jcr/wp.content.repository.install/config/templates/setupdb/dbms/jcr/grantPermissionsToRuntimeRoleStatic.sql .

       2. Replace all placeholder values with the values as defined in wkplc_dbdomain.properties. Placeholder values are surrounded by the character @.

       3. Execute the SQL statements.

    2. Grant database user privileges:

       1. Ensure the database administrator user ID is specified for domain.DBA.DbUser in WP_PROFILE/ConfigEngine/properties\wkplc_dbdomain.properties.

          For example, domain.DBA.DbUser=dbadmin.

       2. ConfigEngine.bat grant-runtime-db-user-privileges -DTransferDomainList=release,customization,community,etc...

          You only need to add -DTransferDomainList when granting privileges across specific domains.

11. Change to the directory WP_PROFILE\bin.

12. Start the WebSphere Portal server.

13. Update the SQL Server 2005 statistics for Portal, and JCR databases by opening SQL Server Management Studio, selecting New Query, and running: use db_name exec sp_updatestats @resample='resample';

If you have additional nodes already configured, compare the following file on all nodes with the file from the primary node. Ensure all instances of the file are identical: WP_PROFILE/PortalServer/jcr/lib/com/ibm/icm\icm.properties. If the files are not identical, copy icm.properties from the primary node on which you ran the database-transfer task to the node.

1. Stop the portal server on the secondary nodes.

2. Copy WP_PROFILE/PortalServer/jcr/lib/com/ibm/icm\icm.properties from the primary node and replace icm.properties on the secondary nodes.

3. Start the portal server on the secondary nodes.

Parent: Windows stand-alone: Set up a remote SQL Server database
Previous: Windows stand-alone: Set up SQL Server automatically or manually