This chapter discusses configuring the IBM Websphere portal v7.0 to use the SQL Server 200x database. It contains the following topics:
Section 6.4, "Using the ConfigEngine Task to Automatically Set Up a Database"
Section 6.5, "Manually Transferring Data from the Default Database to the SQL Server Database"
Before beginning the database configuration, verify that a supported version of Microsoft SQL Server Database software is installed. Refer to the Minimum Technical Requirements for this information. In addition, we strongly recommend you visit the IBM WebSphere Portal Version v7.0 Information Center and review the "Configuring WebSphere Portal to use a database" section:
For advanced configurations, refer to the IBM WebSphere Portal Version v7.0 Information Center.
View the steps to install SQL Server for use with WebSphere Portal. Before you begin this task, complete the following prerequisites:
You should have completed reviewing the Planning for SQL Server topic.
You must install SQL Server separately from WebSphere Portal.
You can obtain the Microsoft SQL Server JDBC Driver from Microsoft. See the Microsoft SQL Server product documentation for installation details (sqljdbc4.jar)
Note:
The driver must be a JDBC 4.0 compliant driver.This section provides instructions for installing SQL Server for use with IBM® WebSphere® Application Server and WebSphere Portal. These steps are the same for both the DataDirect and Microsoft drivers unless noted.
Install SQL Server and all required patches.
Select the Mixed Mode (Windows Authentication and SQL Server Authentication) authentication mode for this installation.
Important:
Mixed Mode authentication allows either a Windows user or an SQL Server user, or both, to log in to the SQL Server; however, WebSphere Portal requires the user to be an SQL Server user.In the SQL Server Setup panel, Components to Install, select the following component, which is required services for WebSphere Portal:
SQL Server Database Services
Complete the installation using SQL Server as per documentation.
Enable TCP/IP connectivity in the SQL Server Configuration Manager.
Installing Microsoft SQL Server JDBC drivers:
Download and install the Microsoft SQL Server JDBC driver; see Microsoft Download Center for information.
Start the database server.
Start the Microsoft SQL Server Management Studio and connect to the local database engine as the system administrator, sa.
Select File -> Open -> File and select xa_install.sql from the subdirectory of the downloaded and extracted JDBC driver.
Execute the script by selecting Query -> Execute.
Note:
Any warnings that appear in the messages section of the application window that say that stored procedures cannot be found can be safely ignored.Start SQL Server.
You must modify the approriate properties files before transferring your data from the default database to the SQL database.
Multiple databases can be used to hold information for applications such as Feedback and LikeMinds. For example, you could use the following property values:
release.DbName=reldb
jcr.DbName=jcrdb
feedback.DbName=fdbkdb
likeminds.DbName=lmdb
community.DbName=commdb
customization.DbName=custdb
If you are using a remote database, enter the values for the remote server.
Regardless of the operating system, use a forward slash (/) instead of a backslash (\) in the property files for file system paths.
There might be additional database properties other than those listed here. Only change the properties within this task and skip all other properties.
Some values, shown here in italics, might need to be modified to your specific environment.
The recommended value listed for each property represents the specific information that is required to configure WebSphere Portal to your target database.
Depending on which database domain has to be configured, replace dbdomain with:
release
customization
community
jcr
feedback
likeminds
The values for at least one of the following properties must be unique for the release, customization, community, and JCR domains:
dbdomain.DbName
dbdomain.DbUrl
dbdomain.DbSchema
If you use the same values for all three properties across the release, customization, community, and JCR domains, the database-transfer task fails due to ambiguous database object names.
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.
Locate the following files and create a backup copy of each before changing any values:
wp_profile_root/ConfigEngine/properties/wkplc.properties
wp_profile_root/ConfigEngine/properties/wkplc_dbdomain.properties
wp_profile_root/ConfigEngine/properties/wkplc_dbtype.properties
If you are transferring from a database other than Derby: wp_profile_root/ConfigEngine/properties/wkplc_sourceDb.properties
Default values are listed in these files. Unless otherwise noted, all values are of type alphanumeric text string. Print out the steps below for reference before modifying the properties files. Make sure to enter the appropriate values for each instance of each property. In wkplc_dbdomain.properties, most properties are repeated for each domain.
Use a text editor to open the properties file wkplc_dbdomain.properties and modify the values to correspond to your environment.
For dbdomain.DbType, type sqlserver2005.
For dbdomain.DbName, type the name of the WebSphere Portal domain database.
Note:
This value is also the database element in the dbdomain.DbUrl property.For dbdomain.DbSchema, type the schema name of the database domain.
Note:
Review your target database management system documentation to define a valid schema name. Some database management systems have schema name restrictions that you need to understand.For dbdomain.DataSourceName, type the name of the data source that WebSphere Portal uses to communicate with its databases.
Do not use the following reserved words:
releaseDS
communityDS
customizationDS
jcrDS
lmdbDS
feedback
For dbdomain.DbUrl, type the database URL used to access the WebSphere Portal database with JDBC. The value must conform to the JDBC URL syntax specified by the database.
Note:
The database element of this value should match the value of DbName.For dbdomain.DbUser, type the user ID for the database configuration user.
For dbdomain.DbPassword, type the password for the database configuration user.
For dbdomain.DbConfigRoleName, type the name of the group for database configuration users. Database rights are granted to this group instead of individuals. The user specified for dbdomain.DbUser must be assigned to this group.
Optional: For dbdomain.DbRuntimeUser, type the user ID of the database user that should be used by WebSphere Portal to connect to the database at runtime. If no value is specified for this setting, the database configuration user will be used to connect to the databases at runtime.
If dbdomain.DbRuntimeUser is specified, you must set dbdomain.DbRuntimePassword to be the password of the runtime database user.
For dbdomain.DbRuntimeRoleName, type the name of the group for database runtime users. Database rights are granted to this group instead of individuals. The user specified for dbdomain.DbRuntimeUser must be assigned to this group.
Optional: For dbdomain.DBA.DbUser, type the database administrator user ID for privileged access operations during database creation. If you do not need this parameter, you can either accept the default value or leave blank.
Optional: For dbdomain.DBA.DbPassword, type the database administrator password for privileged access operations during database creation. If you do not need this parameter, you can either accept the default value or leave blank.
For dbdomain.DbHome, type the root location for the database.
Note:
This value is the location to store the database files locally.This path must use two backslashes (\\) instead of a forward slash (/).
For dbdomain.AdminUrl, type the sqlserver URL without a database attached.
This value is used to connect to the server for database administration operations.
For dbdomain.DbHostName, type the hostname of the database.
Save and close the file.
Update the following properties in the file wkplc_dbtype.properties.
For sqlserver2005.DbDriver, type the name of the JDBC driver class.
For sqlserver2005.DbLibrary, type the directory and name of the .zip or .jar file that contains the JDBC driver class.
For sqlserver2005.JdbcProviderName, type the name of the JDBC provider that WebSphere Portal uses to communicate with its databases.
For sqlserver2005.DbConnectionPoolDataSource, type the name of the implementation class of the connection pool data source.
Save and close the file.
Update the WasPassword value in the wkplc.properties file. This value is the password for the WebSphere Application Server security authentication used in your environment.
Save and close the file.
Use this alternative method for creating databases if you have problems running the create-database task that is documented for setting up a remote SQL Server database on Windows for a stand-alone production server.
Before you begin, you must create the databases.
Expand the nodes to see Databases.
Right-click on Databases to see New database.
Enter the database name. For information on the recommended database architecture and the databases you will need to create, see Planning for SQL Server
On the Options page, select Collation Name from the drop-down list that matches your environment. Set the collation to case-sensitive. For example, on an English system: SQL_Latin1_General_CP1_CS_AS
Note:
For LikeMinds, CI is the default setting; however, CS can also be used.Click OK to save the database changes
This topic provides instructions on automatically setting up your database using the ConfigEngine task. As an alternative to automatically setting up the database, you can manually create users and grant privileges.
Before you begin, ensure that the database management system software is installed.
Change to the directory wp_profile_root/ConfigEngine
To create the databases, type the following command:
./ConfigEngine.sh create-database -DWasPassword=password
To create the database users, type the following command:
Note:
The task setup-database assigns the minimum database privileges to the database configuration and runtime database users../ConfigEngine.sh setup-database -DWasPassword=password
Important:
After setting up your databases, enable the autogrowth feature for the log associated with the JCR database. This will ensure that uploading large files (approximately 100 MB or larger) to Web Content Management works properly.Start the Microsoft SQL Server Management Studio.
Expand the Databases folder.
Right-click on the JCR database and select Properties.
Click Files.
Locate the database log row and click the details button (...) located immediately after the Autogrowth field.
Check the Enable Autogrowth check box and set the autogrowth properties as needed. When using Restricted File Growth, set the value to at least 100 MB.
Click OK to save your changes to the Change Autogrowth screen.
Click OK to save your changes to the Database Properties screen.
Exit the Microsoft SQL Server Management Studio.
This section provides information on how to manually transfer data from the default database to the SQL Server database. Follow these steps to transfer WebSphere Portal and Java Content Repository databases to SQL Server. As an alternative to the manual database transfer procedure described here, you can use the configuration wizard to complete the database transfer task.
Before you begin, make sure that the following prerequisites are met:
Supported database software is installed.
Databases and users are set up.
Property files are modified.
Tip:
If you are transferring from Oracle or Oracle RAC, the open_cursors setting should be set to 1500 by default. However, you might need to increase this value based on the table count in the Java Content Repository schema.Open a command prompt and change to the directory wp_profile_root\\ConfigEngine.
Enter the ./ConfigEngine.sh validate-database -DWasPassword=password command to validate configuration properties.
Tip: Add the -DTransferDomainList parameter to the above validating task to specify the domains you want to validate; for example: -DTransferDomainList=jcr. If you want to validate all domains, you do not need to specify this parameter on the command line.
From the same command prompt as the previous steps, change to the directory wp_profile_root\\bin.
Stop both the server1 and WebSphere_Portal servers:
./stopServer.bat server1 -username admin_userid -password admin_password
./stopServer.bat WebSphere_Portal -username admin_userid -password admin_password
Transfer the database:
Change to the directory wp_profile_root\\ConfigEngine.
Enter the following command:
./ConfigEngine.sh database-transfer -DWasPassword=password
Note:
To select specific database domains to transfer, modify the -DTransferDomainList specified in the command to include only the domains that you want to transfer. For example, to transfer only the JCR domain you can enter the following command:./ConfigEngine.sh database-transfer -DTransferDomainList=jcr -DWasPassword=password
After running the task, a message is added to the following log file for you to verify the task ran successfully:
wp_profile_root\\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.
Optional: If you specified a runtime database user for the dbdomain.DbRuntimeUser parameter, that user must have sufficient database user privileges. To grant the database user privileges, choose either the manual steps or the command line steps:
Complete these steps to manually grant database user privileges:
Copy the appropriate template files to a work directory. Choose one of the following template files:
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.
JCR database domain: For the JCR database domain, you must also copy grantExtendedPermissionsToRuntimeRole.sql.
Locate these files in the following directories:
PortalServer_root\\base\\wp.db.impl\\config\\templates\\setupdb\\dbms\\domain
PortalServer_root\\pzn\\prereq.pzn\\config\\templates\\setupdb\\dbms\\domain
Replace all placeholder values with the values as defined in wkplc_dbdomain.properties. Placeholder values are surrounded by the character @.
Run these statements.
Complete these steps to grant database user privileges with the ConfigEngine task:
Ensure the database administrator user ID is specified for domain.DBA.DbUser inwp_profile_root\\ConfigEngine\\properties\\wkplc_dbdomain.properties. For example, domain.DBA.DbUser=dbadmin.
Run the following task: ./ConfigEngine.sh grant-runtime-db-user-privileges -DTransferDomainList=comma_separated_list_of_domains
Run the ./ConfigEngine.sh create-jcr-jms-resources-post-dbxfer -DWasPassword=password command to create JMS resources in the new database.
Note:
Regardless of the method used to transfer data (configuration wizard or the steps in this topic), you must run this task to create JMS resources.Change to the directory wp_profile_root\\bin.
Start the Administrative Server (server1) and WebSphere Portal server.
Update the SQL Server 2005 statistics for Portal, and JCR databases by opening SQL Server Management Studio, selecting New Query, and running the following query:
use db_name exec sp_updatestats @resample='resample';
This section provides information on testing your database connection to ensure that it operates correctly.
After you configure IBM® WebSphere® Portal to work with your database, test the database connection to ensure that it operates correctly. Then verify that all database transactions work properly within the WebSphere Portal environment. For example, all portal pages should display without HTTP 404 errors, and there should be no database layer-related exceptions in the SystemOut.log and SystemErr.log files.
You can verify the database connection using IBM WebSphere Application Server or by opening WebSphere Portal in a browser.
To verify that the WebSphere Portal application server is running by using WebSphere Application Server, complete these steps:
Open the WebSphere Application Server administrative console by entering the following address in a browser:
http://hostname.example.com:10001/ibm/console
where hostname.example.com is the fully qualified host name of the machine where WebSphere Portal is running and 10042 is the default transport port that is created by WebSphere Application Server.
Log into the administrative console.
Click Resources, JDBC, JDBC Providers.
Select all scopes (the default setting) or select a specific cell, node, or node/server.
Select the scope that corresponds to your instance of WebSphere Portal.
The view refreshes.
Select the name of the data source that is defined in wkplc_dbdomain.properties.
The default data source is wpdbDS.
Select the name of the JDBC provider that is specified in wkplc_dbtype.properties.
The default JDBC provider is wpdbJDBC_dbtype, where dbtype is replaced by the value that matches your environment.
Click Test Connection to verify the database connection.
If configuration parameters have been changed, you might need to restart WebSphere Application Server for the test to complete.
To verify that the WebSphere Portal application server is running by opening WebSphere Portal in a browser, enter the following URL in a supported browser:
http://hostname.example.com:10039/wps/portal
where hostname.example.com is the fully qualified host name of the machine where WebSphere Portal is running and 10039 is the default transport port that is created by WebSphere Application Server.