Configuring WebLogic Portal for Microsoft SQL Server Databases

 

WebLogic Portal includes a set of scripts that create Microsoft SQL Server tables and other data objects for use in any WebLogic Portal environment. The scripts run only in a Windows environment. If you use WebLogic Portal on a UNIX host with a Microsoft SQL Server database located on a Windows host, you must install the contents of the PORTAL_HOME\db\sql_server\2000 directory and the PORTAL_HOME\db\create_all.bat script on a Windows machine with SQL Server database connectivity.

Depending on the database environment that you are defining, you can choose to load sample data that supports the reference applications. Usually, a development environment loads the sample data and a staging or production environment does not. In some cases, you may need to modify the default scripts so that they load non-operational sample data (such as data for the product catalog).

To use a Microsoft SQL Server database with a WebLogic Portal installation, complete the following tasks:

Step 1: Install the Client Software

Step 2: Create a User Database and Database Owner

Step 3: Configure Properties and Environment Variables for SQL Server

Step 4: Create the Schema Objects for MS SQL Server

Step 5: Load Additional Sample Data

Before you continue, follow the procedures in "Installing and Using WebLogic jDriver for Microsoft SQL Server" on the BEA WebLogic Server documentation site.

 

---

Step 1: Install the Client Software

On the WebLogic Portal host, use the SQL Server installation program to install the SQL Server client software and configure it to access your SQL Server Database.

For information on the SQL Server release that WebLogic Portal supports, refer to Supported Platforms in the Installation Guide.

After you install the client software, complete the following tasks:

• Configure Security Authentication

• Create and Test the ODBC Data Source

Configure Security Authentication

Verify that the security authentication settings for your SQL Server are set to "SQL Server and Windows." You cannot create the WebLogic Portal database if your SQL Server is configured for "Windows Only" authentication. To configure authentication settings, do the following:

1. From the SQL Server Enterprise Manager, right click your SQL Server.

2. From the shortcut menu, select Properties.

3. In the Properties window, on the Security Tab, select SQL Server and Windows.

Create and Test the ODBC Data Source

Before you can run create_database.sql, verify that an ODBC Data Source for Microsoft SQL Server is installed on the WebLogic Portal host.

Then use the osql utility to make sure that you can connect to the SQL Server database from the WebLogic Portal host.

 

---

Step 2: Create a User Database and Database Owner

You must create a user database and database owner exclusively for the WebLogic Portal schema. If you need to locate multiple WebLogic Portal databases on same database server, you must create a separate database and database owner combination for each database.

Note: We recommend that you locate the SQL Server database on a host other than the WebLogic Portal host.

WebLogic Portal includes a sample script, create_database.sql, for creating a user database named WLCS with an owner named WEBLOGIC.

To create a user database and database owner, complete the following tasks:

• Modify create_database.sql

• Run create_database.sql

• Change the Password

Modify create_database.sql

You must modify create_database.sql to substitute pathnames that match your environment. We recommend that you place the database data file and the log file on separate physical disks and away from any system database (i.e. master, msdb, tempdb) data or log files. The script provides initial sizes for these files, and you can adjust the values depending on your disk-space requirements and system usage.

To modify the script, do the following:

Note: If you use WebLogic Portal from a Unix host with a Microsoft SQL Server database located on a Windows host, edit the create_database.sql that you copied to the Windows machine.

1. Create a backup copy of PORTAL_HOME\db\sql_server\2000\admin\create_database.sql

   Note: In this document, PORTAL_HOME refers to the directory into which you installed WebLogic Portal.

2. Open create_database.sql in a text editor.

3. To specify a pathname for the data file, under the NAME=WLCS_DATA statement, change FILENAME='D:\DATAFILE\WLCS_DATA.mdf' to
   FILENAME = 'my-pathname\my-data-file.mdf'
   where my-pathname\my-data-file.mdf is the path and filename you want to use.

   If you create more than one WebLogic Portal database on the same SQL Server host, each data file must have a unique name.

4. To specify a pathname for the log file, under the NAME=WLCS_LOG statement, change FILENAME = 'E:\LOGFILE\WLCS_LOG.ldf'to
   FILENAME = 'my-pathname\my-log-file.mdf'
   where my-pathname\my-log-file.mdf is the path and filename you want to use.

   If you create more than one WebLogic Portal database on the same SQL Server host, each log file must have a unique name.

5. (optional) To change the name of the database-owner user ID, substitute all instances of WEBLOGIC with your database-owner user ID. For example,
   go
   exec sp_addlogin 'my-database-owner', 'PASSWORD', 'WLCS', 'us_english'
   go
   use WLCS
   go
   sp_changedbowner my-database-owner
   go

   where my-database-owner is the user ID of your database owner.

6. (optional) To change the name of the database, substitute all instances of WLCS with the name of your database. In addition, you can update WLCS_DATA and WLCS_LOG to reflect the new name of your database. For example,
   use master

   go
   create database my-database-name
   ON
   (NAME = my-database-name_DATA,
   FILENAME = 'D:\DATAFILE\WLCS_DATA.mdf',
   SIZE = 60)
   LOG ON

   (NAME = my-database-name_LOG,
   FILENAME = 'E:\LOGFILE\WLCS_LOG.ldf',
   SIZE = 20)

   go
   checkpoint
   go

   -- *** Create a WEBLOGIC login and user in the WLCS database
   use master
   go
   exec sp_addlogin 'WEBLOGIC', 'PASSWORD', 'my-database-name', 'us_english'
   go
   use my-database-name
   go
   sp_changedbowner WEBLOGIC
   go

   where my-database-name is the new name of your database.

7. Save your modifications to create_database.sql.

   Note: If you change the names of the database owner (WEBLOGIC by default) or the database (WLCS by default), you must review each script for the default names and modify them to match your new names.

Run create_database.sql

To run the create_database.sql script, do the following:

1. Open a MS-DOS Command Prompt window and cd to the following directory:

   PORTAL_HOME\db\sql_server\2000\admin

2. Enter the following command:

   osql -U sa -S SERVER -e -icreate_database.sql -ocreate_database.log

   where SERVER is the name of your SQL Server database server host.

3. When prompted, enter the password for the System Administrator (sa).

   The command logs on to the database server host as System Administrator, runs the create_database.sql script, and directs the output to a file named .\create_database.log.

4. View create_database.log to verify that there are no errors.

Change the Password

The create_database.sql script creates a generic password named password.

We recommend that you change the database-owner password to a new password of your choice.

To change the password, do the following from a DOS prompt:

1. Enter the following command:

   osql -U WEBLOGIC -P PASSWORD -S server-name

   where WEBLOGIC is the user name for the database owner and
   server-name is the name of your SQL Server database server host.

2. Enter the following command:

   1> sp_password PASSWORD,newpassword

   where newpassword is your new password.

3. Enter the following command:

   2> go

osql sets the new password and returns the following message:

Password correctly set.
(return status = 0)
1>

 

---

Step 3: Configure Properties and Environment Variables for SQL Server

When you install WebLogic Portal, it is configured to support a Cloudscape demonstration database. This section describes how to modify the default Cloudscape properties to support a SQL Server database.

To configure properties and environment variables for SQL Server, complete the following tasks in the order indicated below:

1. Start the Administration Console

2. Set Up JDBC Connection Pools and Data Sources

3. Update Settings for the RDBMS Security Realm

4. Configure the JDBC Helper Service

5. Stop the Server

6. Update Environment Variables for the Server

Start the Administration Console

To configure a server to support SQL Server, start the server that you want to configure and access the Administration Console for the server's domain.

For example, you want to configure myServer (which listens on port 7501). Start myServer on your local host and then enter the following URL in a browser:
http://localhost:7501/console

Set Up JDBC Connection Pools and Data Sources

Connection pools provide ready-to-use pools of connections to your RDBMS. The application server creates the pools during server startup, thus eliminating the overhead of your enterprise application having to establish database connections for each transaction. For more information about connection pools, refer to "Overview of Connection Pools" in the WebLogic Server Programming WebLogic JDBC guide.

A DataSource is an interface between your enterprise application and a connection pool. For more information, refer to "Overview of DataSources" in the WebLogic Server Programming WebLogic JDBC guide.

• Configure commercePool for SQL Server Databases

• Configure dataSyncPool for SQL Server Databases

Configure commercePool for SQL Server Databases

To configure commercePool for SQL Server, do the following:

1. In the left pane of the Administration Console, click Services -> JDBC -> Connection Pools.

2. Under the Connection Pools folder, click commercePool.

3. On the General tab, enter the following values (See Figure 10-1):

   In this box...	Enter this value...
   URL	jdbc:weblogic:mssqlserver4:yourServerName:port-number Where yourServerName is the name of the SQL Server database server that hosts the WebLogic Portal database and port-number is port that the database server is configured to listen on.
   Driver Classname	weblogic.jdbc.mssqlserver4.Driver
   Properties	user=@SQL_SERVER_USER@ server=@SQL_SERVER_NAME@ weblogic.t3.waitForConnection=true weblogic.t3.waitSecondsForConnection=999999999999 weblogic.jts.waitSecondsForConnectionSecs=999999999999 verbose=false where @SQL_SERVER_USER@ is the name of the database owner and @SQL_SERVER_NAME@ is the name of the SQL Server database server that hosts the WebLogic Portal database.
   ACLName	no value

   Figure 10-1 The General Tab for JDBC Connection Pools

   
    

4. Click Apply.

5. Next to Password, click change.

6. On the Change Password page, enter and confirm the password of the user account. Then click Apply.

   The WebLogic Server Administration Console keeps this password in an encrypted format.

7. To save your updated password, on the commercePool page, click Apply.

Configure dataSyncPool for SQL Server Databases

To configure dataSyncPool for SQL Server, do the following:

1. In the left pane of the Administration Console, click Services -> JDBC -> Connection Pools.

2. Under the Connection Pools folder, click dataSyncPool.

3. On the General tab, enter the following values:

   In this box...	Enter this value...
   URL	jdbc:weblogic:mssqlserver4:yourServerName:port-number Where yourServerName is the name of the SQL Server database server that hosts the WebLogic Portal database and port-number is port that the database server is configured to listen on.
   Driver Classname	weblogic.jdbc.mssqlserver4.Driver
   Properties	user=@SQL_SERVER_USER@ server=@SQL_SERVER_NAME@ weblogic.t3.waitForConnection=true weblogic.t3.waitSecondsForConnection=999999999999 weblogic.jts.waitSecondsForConnectionSecs=999999999999 verbose=false where @SQL_SERVER_USER@ is the name of the database owner and @SQL_SERVER_NAME@ is the name of the SQL Server database server that hosts the WebLogic Portal database.
   ACLName	no value

4. Click Apply.

5. Next to Password, click change.

6. On the Change Password page, enter and confirm the password of the user account. Then click Apply.

   The WebLogic Server Administration Console keeps this password in an encrypted format.

7. To save your updated password, on the dataSyncPool page, click Apply.

Update Settings for the RDBMS Security Realm

If you are using the RDBMS security realm, you must change the RDBMSRealm settings to match the database type that stores the user information. If you are using LDAP or some other security realm, you can ignore these settings.

To change these settings, do the following:

1. In the left pane of the WebLogic Server Administration Console, click Security -> Realms -> wlcsRealm.

   Note: If you named your RDBMS realm something other than wlcsRealm, click the realm that you created.

2. In the right pane, click the Database tab and enter the following values:

   In this box...	Enter this value...
   Driver	weblogic.jdbc.mssqlserver4.Driver
   URL	jdbc:weblogic:mssqlserver4:yourServerName:port-number Where yourServerName is the name of the SQL Server database server that hosts the WebLogic Portal database and port-number is port that the database server is configured to listen on.
   User Name	The name of the database owner.

3. To save your entries, click Apply.

4. Next to Password, click change.

5. On the Change Password page, enter and confirm the password of the user account. Then click Apply.

   The WebLogic Portal Administration Tools keeps this password in an encrypted format.

6. To save your password, on the wlcsRealm page, click Apply.

7. Click the Schema tab and enter the following properties:

   weblogic.allow.reserve.weblogic.jdbc.connectionPool.commercePool=wlcs
   weblogic.allow.reset.weblogic.jdbc.connectionPool.commercePool=wlcs
   weblogic.allow.shrink.weblogic.jdbc.connectionPool.commercePool=wlcs
   server=@SQL_SERVER_NAME@ 

   where @SQL_SERVER_NAME@ is the name of the SQL Server database server that hosts the WebLogic Portal database.

8. Click Apply.

Configure the JDBC Helper Service

The JDBC Helper Service enables services to explicitly establish a database connection and to coordinate the processing of CLOB data. To configure the JDBC Helper service for SQL Server, do the following for each application that you have deployed:

Note: Since the data synchronization framework uses the Personalization enterprise application, you will need to use the following procedure for the Personalization application as well as all other deployed applications.

1. In the left pane of the WebLogic Server Administration Console, click Deployments -> myApplication -> Service Configuration -> JDBC Helper Service.

2. In the right pane, on the Configuration tab, enter the following values:

   In this box...	Enter this value...
   Maximum Number of Retries	The maximum number of times the service attempts to connect to the database. A value of -1 instructs the service to retry an unlimited number of times.
   Maximum Wait Time	The number of milliseconds to wait for a database connection. When the time limit expires, the service attempts another connection, up to the maximum number of retries. A value of -1 instructs the service to wait infinitely.
   Delegate Class Name	com.bea.p13n.util.jdbc.internal.GenericJdbcHelperDelegate

3. To save your entries, click Apply.

4. Repeat these steps for each application that you have deployed in this server instance.

Stop the Server

Stop the server to complete the remainder of Step 3: Configure Properties and Environment Variables for SQL Server.

For information on stopping the server, refer to Shutting Down a Server.

Update Environment Variables for the Server

To update environment variables, do the following on your WebLogic Portal host:

1. In a text editor, open PORTAL_HOME\bin\platform-type\set-environment.bat (set-environment.sh on UNIX)

2. Under the section titled
   -------- Specify which database to use --------,
   activate the line that sets the DATABASE variable to SQL Server and deactivate any other line. For example, Listing 10-1 shows a Windows environment that uses SQL Server.

   Listing 10-1 Specify the Database (Example for a Windows Environment)

   REM ----------- Specify which database to use -----------

   REM SET DATABASE=CLOUDSCAPE
   REM SET DATABASE=ORACLE
   REM SET DATABASE=ORACLE_OCI_815

   SET DATABASE=MSSQL

 

---

Step 4: Create the Schema Objects for MS SQL Server

WebLogic Portal includes a script, create_all.bat, that calls a series of other scripts to create the WebLogic Portal schema and loads sample data. You can modify the script to create the database without loading sample data.

This step includes the following tasks:

• Set Variables in databaseload.properties

• Set Variables in create_all.bat

• Run create_all.bat

Set Variables in databaseload.properties

To set up databaseload.properties, do the following on your WebLogic Portal host:

Note: If you use WebLogic Portal from a Unix host, edit the databaseload.properties that is located on the UNIX host.

1. Create a backup copy of PORTAL_HOME\db\databaseload.properties.

2. Open databaseload.properties in a text editor.

3. Disable the Cloudscape statements by placing a pound character (#) at the beginning of each line:

   #------Cloudscape--------------------------------#
   #jdbcdriver=COM.cloudscape.core.JDBCDriver
   #connection=jdbc:cloudscape:Commerce
   #dblogin=none
   #dbpassword=none 

4. Enable the statements for SQL Server by removing the pound character (#) from the beginning of each line:

   #------MS SQL Server-----------------------------#

   jdbcdriver=weblogic.jdbc.mssqlserver4.Driver
   connection=jdbc:weblogic:mssqlserver4:@MSSQL_SERVER@:@MSSQL_PORT@
   dblogin=@MSSQL_USER@
   dbpassword=@MSSQL_PASSWORD@ 

5. Replace @MSSQL_SERVER@:@MSSQL_PORT@ with the name and listen port for your SQL Server.

   Replace @MSSQL_USER@ with the name of the database owner and
   @MSSQL_PASSWORD@ with the database owner's password.

   The user name and password must match the user name and password you used in Set Up JDBC Connection Pools and Data Sources and Update Settings for the RDBMS Security Realm.

6. Save databaseload.properties.

Set Variables in create_all.bat

To set variables in create_all.bat, do the following:

Note: If you use WebLogic Portal from a Unix host with a Microsoft SQL Server database located on a Windows host, edit the create_all.bat that you copied to the Windows machine.

1. Create a backup copy of PORTAL_HOME\db\create_all.bat.

2. Open create_all.bat in a text editor.

3. Find the following statements, which are near the beginning of the file:

   REM To be set by user
   set SAMPLEDATA=Y
   set USER_ID=myUser
   set PASSWORD=myPassord
   set SERVER=myServer
   REM End of set by user

4. Supply the following values for each statement:

   For this statement...	Enter this value...
   SAMPLEDATA	Y if you want to load sample catalog, customer, and user data. The reference applications use this data to demonstrate WebLogic Portal features. N if you do not want to load sample data. The reference applications do not function properly without this sample data. Do not enter N if you are setting up an environment in which you want to run the sample applications.
   USER_ID	The name of the SQL Server database owner.
   PASSWORD	The database owner's password.
   SERVER	The name of the SQL Server database server that hosts the WebLogic Portal database.

5. In create_all.bat, find the section that is illustrated below. Replace the @WL_PORTAL_HOME@ variable with the absolute pathname of the directory in which you installed WebLogic Portal. Remove the echo statements and EXIT command from the code.

   echo ***************************************************************

   echo    Please replace the reference of @WL_PORTAL_HOME@
   echo    with the actual WebLogic Portal path 
           ( e.g.,C:\BEA\WLPORTAL4.0 )

   echo    Once changed, be sure and remove these echo statements   
           and the
   echo    EXIT command from the batch file.
   echo ***************************************************************

       EXIT

   CALL @WL_PORTAL_HOME@\bin\win32\set-environment.bat

6. If you use WebLogic Portal from a Unix host, in create_all.bat, set the MSSQL_UNIX switch to Y (it is set to N by default). For example:

   REM To be set by user for Microsoft SQL Server
   REM set MSSQL_VERSION=7
   set MSSQL_VERSION=2000

   REM set MSSQL_UNIX=N
   set MSSQL_UNIX=Y 

7. Save create_all.bat.

Run create_all.bat

Caution: Before it creates tables, create_all.bat runs SQL statements that drop any existing WebLogic Portal tables. If you run create_all.bat for a database that already contains WebLogic Portal data, you will lose any WebLogic Portal data that is in the database.

To run this script, do the following:

1. Open a MS-DOS Command Prompt window and cd to the following directory:

   PORTAL_HOME\db\

2. Enter the following command:

   create_all.bat

   The script creates log files under PORTAL_HOME\db.

   The script logs its actions in several log files, which it locates in the PORTAL_HOME\db directory.

3. View the following log files to verify that there are no errors:

   • drop_all.log. If you run create_all.bat for a database that contains tables that are not part of the WebLogic Portal schema, the log file contains messages that state Cannot drop the table.... You can ignore these messages.

   • create_all.log

   • insert_all.log

   • statistics.log

   • install_report.log

4. If you use WebLogic Portal from a Unix host, run the following scripts from a Unix shell to insert both required and sample database bootstrap into the database tables:

   • PORTAL_HOME/db/loadbootstrap.sh

   • PORTAL_HOME/db/loadsample.sh

For a description of the tables, indexes, and constraints, see the following topics:

• "The Personalization Server Schema" in the Guide to Building Personalized Applications

• "The Portal Management Database Schema" in the Guide to Developing and Managing Portals

• "Product Catalog Database Schema" in the Guide to Building a Product Catalog

• "Order Processing Database Schema" in the Guide to Managing Purchases and Processing Orders

• "Persisting Behavior Tracking Data" in the Guide to Events and Behavior Tracking

• "The Campaign Manager Database Schema" in the Guide to Developing Campaign Infrastructure

 

---

Step 5: Load Additional Sample Data

You can load additional sample data that demonstrates ad placeholders and other Campaign services. To load additional sample data, do the following from the WebLogic Portal host:

1. Start the server that you want to use the sample data. For information on starting servers, refer to Starting and Shutting Down a Server..

2. Open a command shell and enter the following command:
   PORTAL_HOME\bin\platform-type\loadSampleData.bat (loadSampleData.sh on UNIX)

3. Shut down WebLogic Portal. For information on stopping the server, refer to Shutting Down a Server.

The loadSampleData script does the following:

• Synchronizes sample data with the p13nApp application and the wlcsApp application.

• Calls loaddocs.bat, which loads documents that the p13nApp application displays.

• Calls loadads.bat, which loads ads to demonstrate ad placeholders in the wlcsApp reference application.