Database Administration Guide

Using a Microsoft SQL Server Database

This section describes the steps necessary to use a Microsoft SQL Server database with WebLogic Portal 8.1, and includes information on the following subjects:

Review this entire chapter and any release notes before proceeding. The steps in this chapter should be performed by a database administrator.

Note: For additional database setup information, see "Managing WebLogic Platform Database Resources" at http://download.oracle.com/docs/cd/E13196_01/platform/docs81/db_mgmt/db_resource_mgmt.html.

Configuring a Microsoft SQL Server Database

Before proceeding, be sure you have read Overview of Database Configuration for WebLogic Portal.

To configure a SQL Server database:

This step is required only if you are not planning to use the Configuration Wizard to create the database objects for a new domain.

Install the SQL Server client on the WebLogic Platform host and do the following:

Configure it for access to your SQL Server database.

Ensure that you can connect to your SQL Server database using the OSQL utility.

See your SQL Server documentation for details.

Verify that security authentication settings for the SQL Server are set to "SQL Server and Windows."

From Enterprise Manager, right-click the desired SQL Server.

Select Properties, then select the Security tab.

Under authentication, ensure that SQL Server and Windows is selected.

Prepare the SQL Server database. The database creation scripts install domain-specific tables. It is recommended that you work with a SQL Server system or database administrator to adjust the sample scripts and create database devices, file groups, databases, and database users for your SQL Server environment.

Notes: Multiple databases are required if you have multiple domains, or to run multiple environments using the same SQL Server instance (for example, if you want to run development and system test from a single SQL Server installation).

Be sure to back up your database(s) before installing any new database objects. See your database documentation for details.

Review and modify the provided sample scripts to suit your environment. These scripts are available in the WL_HOME\portal\db\sql_server\2000\admin directory.

The following table describes the script names and the usage notes for each script.

Script Name

Description

create_database.sql

Creates the WEBLOGIC database, the WEBLOGIC_INDEX file group, and WEBLOGIC database owner (dbo) user login. An alias is created to make WEBLOGIC the dbo user in the database. Sets the WebLogic database as the default database for the WebLogic user.

Usage Notes: Edit the script to change database names, database owner user, and password.

The default names are the following:

database name: WEBLOGIC

database owner user: WEBLOGIC

password: WEBLOGIC

You also need to edit the script to reflect valid disk locations for DATA devices, LOG devices, and the WEBLOGIC_INDEX file group; you may also need to adjust file sizes. Put DATA and LOG files on separate physical disks and away from any system database files, unless you are using RAID devices.

statistics.sql

Runs sp_updatestats to compute database statistics needed for the database optimizer. You should update database statistics periodically. (This is done by default for SQL Server databases with the AUTO_UPDATE_STATISTICS database option.)

When set to ON (default), existing statistics are automatically updated when the data in the tables has changed.

When set to OFF, existing statistics are not automatically updated. You must manually update statistics.

The AUTO_UPDATE_STATISTICS option setting is stored in the IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.

install_report.sql

Builds an informational installation report about the database objects created in the WEBLOGIC schema.

bt_create_database.sql

Create the WEBLOGIC_EVENT database and WEBLOGIC_EVENT database owner user login. An alias is created to make WEBLOGIC_EVENT the database owner (dbo) user in the database.

Usage Notes: Edit the script to change database names, database owner user, and password.

The default names are the following:

database name: WEBLOGIC_EVENT

database owner user: WEBLOGIC_EVENT

password: WEBLOGIC_EVENT

You also need to edit the script to reflect valid disk locations for the DATA and LOG devices, or to adjust file sizes. Put DATA and LOG files on separate physical disks and away from any system database files.

Run create_database.sql using OSQL as a user with System Administrator privileges (that is, the sa user). For example:

osql -Usa -SSQLSERVER -e -icreate_database.sql -ocreate_database.log
The output from running create_database.sql is written to create_database.log. Verify that there are no errors in the log file before proceeding.

Manually Creating Database Objects

You can either manually create database objects or use the Configuration Wizard. For details, see Overview of Database Configuration for WebLogic Portal.

Note: If you choose to use the WebLogic Configuration Wizard to configure and connect to the database that you will use to support WebLogic Portal, see http://download.oracle.com/docs/cd/E13196_01/platform/docs81/confgwiz/index.html.

The scripts to create Microsoft SQL Server database objects were designed to run in a Windows environment (they use the OSQL utility to create Microsoft SQL Server database objects). If you are using UNIX version of WebLogic Server with a Microsoft SQL Server database and do not have WebLogic products also installed on Windows, contact BEA support for assistance.

To manually create WebLogic Portal database objects, use the following steps:

Verify that you can connect to the target database server with a valid user ID and password. For example:

osql -SSQLSERVER -Uuserid -Ppassword

Open your domain's db_settings.properties file for edit and comment out the database settings for PointBase.

Uncomment the database settings for SQL Server and update the following settings for your database:

server=
dblogin=
password=

Initialize the database with the new settings.

For Windows, navigate to the BEA_HOME\user_projects\domains\portalDomain directory and double-click the create_db.cmd file.

Verify the results in the create_db.log file.

Note: If you are using the sample domain, run the create_db.cmd file from the following directory: WL_HOME\samples\domains\portal.

Follow the steps in Manually Configuring Your Domain's JDBC Driver Settings.

Manually Configuring Your Domain's JDBC Driver Settings

You can either manually configure your domains JDBC driver settings using the WebLogic Server Console, or use the Configuration Wizard. For more information, see Overview of Database Configuration for WebLogic Portal.

To manually configure your JDBC driver settings using WebLogic Server Console:

Start the WebLogic Server for your domain.

Configure your new connection pools.

Go to Services —> JDBC —> Connection pools.

Click Configure a new connection pool.

Select the appropriate database type and non-XA database driver from the drop-down list boxes and click Continue. For more information, see the Supported Configuration documentation for JDBC drivers supported by WebLogic Portal, http://download.oracle.com/docs/cd/E13196_01/platform/docs81/support/supp_plat.html#1085671.

For an XA configuration, see "Creating XA Domains Using Configuration Templates" in the "Creating WebLogic Configurations Using the Configuration Wizard" documentation, http://download.oracle.com/docs/cd/E13196_01/platform/docs81/confgwiz/index.html.

Choose a name for the new connection pool (For example: cgPoolN) and fill in the blanks for your vendor database. Click Continue.

Test your connection to verify that you can successfully connect to your database.

Create and deploy your new connection pool.

Note: You must maintain a one-to-one mapping of JDBCTxDataSource to JDBC connection pool in the domain's config.xml file. Create one new JDBC connection pool for each JDBCTxDataSource and another JDBC connection pool for the domain's JDBCDataSources.

Update your data sources.

From Services —> JDBC —> Data Sources, click each data source and switch each to the newly created connection pool. Make sure that you apply each change.

Verify that each data source is changed by clicking on Data Sources and then verifying that Pool Name has been set to the new connection pool for each.

From Services —> JMS —> Stores —> cgJMSStore, switch cgJMSStore to use the new connection pool.

Stop your domain's WebLogic Server, then restart it.

In the WebLogic Server Console, delete the original connection pools.

Go to Services —> JDBC —> Connection Pools.

Right-click each connection pool and select Delete.

Creating a Separate Database for Behavior Tracking Events

For improved performance, you might want to store behavior tracking events in a different location from other WebLogic Portal database objects. For more information about behavior tracking, see http://download.oracle.com/docs/cd/E13218_01/wlp/docs81/adminportal/help/SA_BehavTrackServ.html.

Note: By default, behavior tracking database objects are created in the same database as other WebLogic Portal database objects. You need to perform these steps only if you are configuring a separate database for behavior tracking events.

Edit the bt_create_database.sql file for your environment, as indicated in the instructions contained in the file.

Run bt_create_database.sql using OSQL as a user with system administrator privileges. For example:

osql -Usa -SSQLSERVER -e -ibt_create_database.sql -obt_create_database.log

The output from running bt_create_database.sql is written to bt_create_database.log. Verify that there are no errors in the log file before proceeding.

Navigate to the appropriate database directory based on your environment: WL_HOME\portal\db\sql_server\2000

Connect as the user WEBLOGIC_EVENT and run the following scripts:

bt_create_tables.sql
bt_create_fkeys.sql
bt_create_indexes.sql
bt_create_views.sql
bt_create_triggers.sql

Run the following script from the path WL_HOME\portal\db\data\required:

bt_insert_system_data.sql

Configure a connection pool to access your behavior tracking database and associate the p13n_tracking JDBC data source with that connection pool. Follow the steps in Manually Configuring Your Domain's JDBC Driver Settings.

Script Name	Description
create_database.sql	Creates the `WEBLOGIC` database, the `WEBLOGIC_INDEX` file group, and `WEBLOGIC` database owner (dbo) user login. An alias is created to make `WEBLOGIC` the dbo user in the database. Sets the WebLogic database as the default database for the WebLogic user. Usage Notes: Edit the script to change database names, database owner user, and password. The default names are the following: database name: WEBLOGIC database owner user: WEBLOGIC password: WEBLOGIC You also need to edit the script to reflect valid disk locations for `DATA` devices, `LOG` devices, and the `WEBLOGIC_INDEX` file group; you may also need to adjust file sizes. Put `DATA` and `LOG` files on separate physical disks and away from any system database files, unless you are using RAID devices.
statistics.sql	Runs `sp_updatestats` to compute database statistics needed for the database optimizer. You should update database statistics periodically. (This is done by default for SQL Server databases with the AUTO_UPDATE_STATISTICS database option.) When set to ON (default), existing statistics are automatically updated when the data in the tables has changed. When set to OFF, existing statistics are not automatically updated. You must manually update statistics. The AUTO_UPDATE_STATISTICS option setting is stored in the IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
install_report.sql	Builds an informational installation report about the database objects created in the `WEBLOGIC` schema.
bt_create_database.sql	Create the `WEBLOGIC_EVENT` database and `WEBLOGIC_EVENT` database owner user login. An alias is created to make `WEBLOGIC_EVENT` the database owner (dbo) user in the database. Usage Notes: Edit the script to change database names, database owner user, and password. The default names are the following: database name: WEBLOGIC_EVENT database owner user: WEBLOGIC_EVENT password: WEBLOGIC_EVENT You also need to edit the script to reflect valid disk locations for the `DATA` and `LOG` devices, or to adjust file sizes. Put `DATA` and `LOG` files on separate physical disks and away from any system database files.