A Using Microsoft SQL Server as the Dehydration Store

This appendix describes how to use a Microsoft SQL Server database as the dehydration store with Oracle BPEL Process Manager.

This appendix contains the following topics:

• Overview of Requirements

• Step 1: Install and Configure the Microsoft SQL Server Database

• Step 2: Install Oracle BPEL Process Manager

• Step 3: Create the Oracle BPEL Process Manager Tables in the Microsoft SQL Server Database

• Step 4: Download and Configure the JDBC Driver

• Step 5: Configure Microsoft SQL Server as the Dehydration Store

Note: This appendix describes installing and configuring Oracle BPEL Process Manager on Windows. If installing Oracle BPEL Process Manager on a UNIX operating system, substitute the appropriate directory paths throughout this appendix.

A.1 Overview of Requirements

Table A-1 describes the supported Microsoft SQL Server database, JDBC driver, and application server to use with Oracle BPEL Process Manager. For details about Oracle BPEL Process Manager memory, disk space, swap space, and monitor requirements, see Table 1-2.

Table A-1 Microsoft SQL Server Database Requirements

Component	Requirement
Database	MS SQL Server 2000 with Service Pack (SP) 4
JDBC driver	Oracle&DataDirect JDBC Driver 10.1.2.0.2
Applications server	Oracle Application Server 10.1.2.0.2

A.2 Step 1: Install and Configure the Microsoft SQL Server Database

1. Go to the host on which to install Microsoft SQL Server (for this example, the host is named myDB_Host). Substitute your own host name as necessary.

2. Install and configure your Microsoft SQL Server database with the following details:

   Element Type	Element Example Name
   Database name	orabpeldb (recommended name to use)
   Database login user name	orabpel (recommended name to use)
   Database login password	orabpel_pword

   
   

   These element names are used throughout this chapter. If you use different names, substitute those names as necessary.

3. Use the username and password created in Step 2 to test your connection to the Microsoft SQL Server database.

4. If you have difficulty connecting, contact your Microsoft SQL Server database administrator.

A.3 Step 2: Install Oracle BPEL Process Manager

1. Follow the instructions in Chapter 2, "Oracle BPEL Process Manager Installation" to install the 10.1.2.0.2 installation type appropriate to your environment:

   • Oracle BPEL Process Manager for Developers

   • Oracle BPEL Process Manager for OracleAS Middle Tier

2. If installing Oracle BPEL Process Manager for OracleAS Middle Tier, select Non-Oracle Database on the Choose the Dehydration Database type screen during Oracle Universal Installer installation.

   This enables you to skip additional installation screens and complete the installation process (such as the Specify Dehydration Store Database Information screen).

A.4 Step 3: Create the Oracle BPEL Process Manager Tables in the Microsoft SQL Server Database

1. Download OracleMetaLink patch number 5099565 from the following location:

   http://metalink.oracle.com
   
   

   This patch includes the following Oracle BPEL Process Manager table scripts:

   • server_sqlserver.ddl

   • domain_sqlserver.ddl

   • workflow_sqlserver.sql

   • sensor_sqlserver.sql

2. Copy these files to a directory on a host on which the isql SQL utility is installed (for example c:\temp):

   The isql SQL utility is required for running these scripts.

3. Use isql to create the Oracle BPEL Server database tables:

   c:\temp>isql -U orabpel –P orabpel_pword –S myDB_Host -d orabpeldb -i server_sqlserver.ddl
   
   c:\temp>isql -U orabpel –P orabpel_pword –S myDB_Host -d orabpeldb -i domain_sqlserver.ddl
   
   

   where:

   • orabpel is the user name specified in "Step 1: Install and Configure the Microsoft SQL Server Database"

   • orabpel_pword is the password specified in "Step 1: Install and Configure the Microsoft SQL Server Database"

   • myDB_Host is the name of the host on which the Microsoft SQL Server database is installed

   • orabpeldb is the database name specified in "Step 1: Install and Configure the Microsoft SQL Server Database"

4. Create the Oracle BPEL Process Manager workflow database tables:

   c:\temp>isql -U orabpel –P orabpel_pword –S myDB_Host -d orabpeldb -i workflow_sqlserver.sql
   
   

5. Create the Oracle BPEL Process Manager sensor database tables:

   c:\temp>isql -U orabpel –P orabpel_pword –S myDB_Host -d orabpeldb -i sensor_sqlserver.sql
   

A.5 Step 4: Download and Configure the JDBC Driver

1. Download the DirectData JDBC driver from the following URL:

   http://www.oracle.com/technology/software/products/ias/htdocs/utilsoft.html 
   
   

2. Review the Oracle Technology Network Developer License Terms and accept all conditions of use.

   The Oracle Application Server 10g page appears.

3. Go to the Version 10.1.2.0.2 header.

4. Click DirectData JDBC Drivers to download the driver.

5. Create a directory on the Oracle BPEL Process Manager host in which to place the JDBC driver. For this example, the following directory name is used:

   C:\dataDirectOc4jJDBCDriver
   
   

6. Add the JDBC driver JAR files into the application.xml file for your installation type:

   • For Oracle BPEL Process Manager for Developers

     Oracle_Home\integration\orabpel\system\appserver\oc4j\j2ee\home\config\application.xml

   • For Oracle BPEL Process Manager for OracleAS Middle Tier

     Oracle_Home\j2ee\OC4J_BPEL\config\application.xml

   ...
   <!-- ORABPEL -->
   ...
   <library path="C:\dataDirectOc4jJDBCDriver\lib\YMbase.jar"/>
   <library path="C:\dataDirectOc4jJDBCDriver\lib\YMsqlserver.jar"/>
   <library path="C:\dataDirectOc4jJDBCDriver\lib\YMutil.jar"/>
   ...
   

A.6 Step 5: Configure Microsoft SQL Server as the Dehydration Store

1. Open the data-sources_sqlserver.xml file included in the OracleMetaLink patch that you downloaded in "Step 3: Create the Oracle BPEL Process Manager Tables in the Microsoft SQL Server Database".

   The information in this file enables you to configure the following settings:

   • The JDBC driver

   • The host name and port on which the Microsoft SQL Server database was installed in "Step 1: Install and Configure the Microsoft SQL Server Database".

   • The user name and password settings created in "Step 1: Install and Configure the Microsoft SQL Server Database".

2. Open the data-sources.xml file located in the following directory for your installation type:

   • For Oracle BPEL Process Manager for Developers

     Oracle_Home\integration\orabpel\system\appserver\oc4j\j2ee\home\config\data-sources.xml

   • For Oracle BPEL Process Manager for OracleAS Middle Tier

     Oracle_Home\j2ee\OC4J_BPEL\config\data-sources.xml

3. Copy and paste the following sections of the data-sources_sqlserver.xml file into the data-sources.xml file.

4. Change the host name, port, password, and user name (only if you changed it from orabpel) to the correct values for your environment in the following sections.

   <?xml version="1.0" standalone='yes'?>
   <!DOCTYPE data-sources PUBLIC "Orion data-sources" 
   "http://xmlns.oracle.com/ias/dtds/data-sources-9_04.dtd">
   
   <data-sources>
   <!--
   
   An example/default DataSource that uses
   Oracle JDBC-driver to create the connections. 
   This tag creates all the needed kinds
   of data-sources, transactional, pooled and EJB-aware sources.
   The source generally used in application code is the "EJB"
   one - it provides transactional safety and connection
   pooling. Oracle thin driver could be used as well,
   like below.
   url="jdbc:oracle:thin:@host:port:sid"
   -->
   
   <!-- Use this datasource to connect to Microsoft SQL Server -->
   
   <data-source class="com.evermind.sql.DriverManagerDataSource"
        name="BPELServerDataSource"
        location="jdbc/BPELServerDataSourceWorkflow"
        xa-location="BPELServerDataSource"
        ejb-location="jdbc/BPELServerDataSource"
        connection-driver="com.oracle.ias.jdbc.sqlserver.SQLServerDriver"
        max-connections="50"
        min-connections="10"
        connection-retry-interval="30"
        max-connect-attempts="10"
   url="jdbc:oracle:sqlserver://
   [DB_HOST]:[PORT];SelectMethod=cursor;User=orabpel;Password=orabpel_pword"/>
   
   <data-source class="com.evermind.sql.DriverManagerDataSource"
        name="BPELSamplesDataSource"
        location="jdbc/BPELSamplesDataSource"
        xa-location="BPELSamplesDataSource"
        ejb-location="jdbc/BPELSamplesDataSource"
        connection-driver="com.oracle.ias.jdbc.sqlserver.SQLServerDriver"
        max-connections="50"
        min-connections="10"
        connection-retry-interval="30"
        max-connect-attempts="10"
   url="jdbc:oracle:sqlserver:
   //[DB_HOST]:[PORT];SelectMethod=cursor;User=orabpel;Password=orabpel_pword"/>
   
   <data-source class="com.evermind.sql.DriverManagerDataSource"
                name="AdminConsoleDateSource"
                location="jdbc/AdminConsoleDateSource"
                xa-location="AdminConsoleDateSource"
                ejb-location="jdbc/AdminConsoleDateSource"
                connection-driver="com.oracle.ias.jdbc.sqlserver.SQLServerDriver"
                max-connections="80"
                min-connections="2"
        connection-retry-interval="30"
        max-connect-attempts="10"
   url="jdbc:oracle:sqlserver:
   //[DB_HOST]:[PORT];SelectMethod=cursor;User=orabpel;Password=orabpel_pword"/>
   
   

5. Comment out the Oracle Database Lite section of data-sources.xml:

   <!-- Use these datasources to connect to Oracle Lite -->
   <!-- 
   <data-source class="com.evermind.sql.DriverManagerDataSource". . .
   . . .
   . . .
   <data-source class="com.evermind.sql.DriverManagerDataSource". . .. . .<data-source class="com.evermind.sql.DriverManagerDataSource". . .. . .     url="jdbc:polite4@127.0.0.1:100:orabpel"/>
    -->
   
   

6. Save your changes in the data-sources.xml file.

7. If you installed Oracle BPEL Process Manager on Windows, remove or comment out the following Oracle Database Lite lines in these files:

   • In Oracle_Home\integration\orabpel\bin\startorabpel.bat:

     @rem start /d "Oracle_Home\integration\orabpel\bin" /min /realtime start_olite.bat
     
     

   • In Oracle_Home\integration\orabpel\bin\shutdowntorabpel.bat:

     @rem start /min /d "Oracle_Home\integration\orabpel\bin\kill_olite.bat"
     
     

8. Microsoft SQL Server database configuration is now complete.