4 Using Microsoft SQL Server

This chapter describes the steps necessary to use a Microsoft SQL Server database with WebLogic Portal, and includes the following sections:

• Section 4.1, "Configuring Microsoft SQL Server Databases"

• Section 4.2, "Configuring the Main WebLogic Portal Database"

• Section 4.3, "Manually Creating a Separate Database and Database Objects for Behavior Tracking"

• Section 4.4, "Database Scripts and Usage Notes"

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

4.1 Configuring Microsoft SQL Server Databases

Before proceeding, read Section 2.1, "Overview of Enterprise-Quality Database Configuration for WebLogic Portal."

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

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). Any additional content management repositories require a separate database user.

Note:

The SQL Server JDBC driver requires additional steps to configure stored procedures for JTA and to place a required DLL on the SQL Server database host. For instructions, see "Using WebLogic-branded DataDirect Drivers" in Oracle Fusion Middleware Programming JDBC for Oracle WebLogic Server.

To configure a SQL Server database:

1. Be sure to back up your database before installing any new database objects. See your database vendor documentation for details.

2. Review the provided sample scripts, located in the <WLPORTAL_HOME>\portal\db\sql_server\admin directory. See Table 4-1 and the comments in the scripts for additional information.

3. Copy and modify the sample scripts appropriately for your environment to create each of the following databases:

   1. Follow the steps in Section 4.2, "Configuring the Main WebLogic Portal Database" to create the main WebLogic Portal database and database objects.

   2. If you want to create a separate behavior tracking database, follow the steps in Section 4.3, "Manually Creating a Separate Database and Database Objects for Behavior Tracking."

4.2 Configuring the Main WebLogic Portal Database

To configure the main WebLogic Portal database, follow these steps:

1. Copy the create_database.sql script and modify it appropriately for your environment. See Table 4-1 and the comments in the script for additional information.

2. Run the modified create_database.sql script as a user with System Administrator privileges (normally the sa user). For example, from osql:

   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.

Follow the remaining steps only if you want to create database objects manually rather than using the Configuration Wizard. To perform the remaining steps using the Configuration Wizard, see Oracle Fusion Middleware Creating Domains Using the Configuration Wizard.

1. Open your domain's database.properties file for edit.

2. Set database=sql_server.

3. Update the following settings (by replacing the @ symbols and the text between the symbols with the correct values) for your main WebLogic Portal database:

   sql_server.user=@DB_USER@
   sql_server.password=@DB_PASSWORD@
   sql_server.url=jdbc:bea:sqlserver://@DB_HOST@:@DB_PORT@;DatabaseName=@DB_NAME@
   

4. Create the database objects.

   1. Navigate to the <MW_HOME>\user_projects\domains\myPortalDomain directory.

   2. Double-click create_db.cmd.

      If any error messages are displayed, check the create_db.log file for additional information.

      Note:

      After running the create_db script, you need to update the database administrator password. See Section 3.8, "Note About Creating or Refreshing Database Objects" for detailed information.

5. Replace the JDBC data sources in your domain, which point to Derby by default, with data sources that point to SQL Server. You can configure them using the WebLogic Server Administration Console or choose from the samples provided and update them for your database environment. Sample jdbc.xml definition files for each database and driver that Oracle supports are available in the <WLPORTAL_HOME>\portal\db\jdbc\database_driver directory; for example, sql_server_bea. Follow the instructions in the <WLPORTAL_HOME>\portal\db\jdbc\README.txt file.

4.3 Manually Creating a Separate Database and Database Objects for Behavior Tracking

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 "Setting Up Events and Behavior Tracking" in the Oracle Fusion Middleware Interaction Management Guide for Oracle WebLogic Portal.

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.

To create a separate database for behavior tracking:

1. Modify the bt_create_database.sql file for your environment, as indicated in the instructions contained in the scripts and in Table 4-1.

2. Run bt_create_database.sql as a user with system administrator privileges. For example, from osql:

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

   If any error messages are displayed, check the bt_create_database.log file for additional information.

3. Navigate to the appropriate database directory based on your environment; for example, <WLPORTAL_HOME>\p13n\db\sql_server.

4. 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_triggers.sql

5. Run the following scripts from this directory: <WLPORTAL_HOME>\portal\db\data\required:

   • bt_insert_system_data.sql

   • bt9_insert_system_data.sql

6. Use the WebLogic Server Administration Console to configure a non-XA JDBC data source to access your behavior tracking database. Associate the JNDI name p13n.trackingDataSource with that data source and then remove p13n.trackingDataSource from p13nDataSource.

4.4 Database Scripts and Usage Notes

Table 4-1 describes the scripts that enable you to configure a Microsoft SQL Server database.

Table 4-1 Database Scripts and Usage Notes

Script Name	Description
create_database.sql	Creates a database. You must create a main WebLogic Portal database. Make a copy of this script and edit it to replace <<WEBLOGIC>> with the appropriate database name, database owner user, and password for each database you create. You must also edit the script to reflect valid disk locations for DATA devices, LOG devices, and the WEBLOGIC_INDEX file group; you may also need to modify file sizes. Put DATA and LOG files on separate physical disks and away from any system database files, unless you are using RAID devices. Note: Do not change the name of the WEBLOGIC_INDEX file group. Typical names for the main WebLogic Portal database are: Database name: WEBLOGIC Database owner user: WEBLOGIC Password: WEBLOGIC When you run the script with these values, it 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. It also sets the WEBLOGIC database as the default database for the WEBLOGIC user. Note: If you decide to create an additional content management repository, you must create a database with different user names for it. For additional information, see the Oracle Fusion Middleware Content Management Guide for Oracle WebLogic Portal.
statistics.sql	Runs sp_updatestats to compute database statistics needed for the database optimizer. Update statistics periodically and whenever any significant changes in database data occur. (This is done by default for SQL Server databases with the AUTO_UPDATE_STATISTICS database option enabled.) When set to ON (the 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 set 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	Creates 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. You must edit the script to reflect valid disk locations for the DATA and LOG devices, or to modify file sizes. Put DATA and LOG files on separate physical disks and away from any system database files. The default names are the following: Database name: WEBLOGIC_EVENT Database owner user: WEBLOGIC_EVENT Password: WEBLOGIC_EVENT