Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved


Chapter 30 JDBC Backing Store Setup : DBMS Software Requirements and Installation

DBMS Software Requirements and Installation
Database Location
Instructions in this chapter assume you are working with a local database for testing purposes. Adapt the instructions if you are working with a remote database. For example, in production environments, you might have to ask a database administrator to create a database user for you. You should then be able to run the other SQL scripts yourself, logged on as the user created by the administrator.
Minimum User Permissions
By default the TIBCO BusinessEvents user permissions are set to DBA privileges. At a minimum, the user must be able to create tables and views. For example for an Oracle database you could use the following:

 
DROP USER BE_USER CASCADE;
CREATE USER BE_USER IDENTIFIED BY BE_USER;
GRANT CONNECT TO BE_USER;
GRANT RESOURCE TO BE_USER;
GRANT CREATE ANY VIEW TO BE_USER;
GRANT CREATE ANY TABLE TO BE_USER;

 
 
Task A Ensure DBMS Software and Drivers are in Place
Before you begin to configure the backing store, you or your DBA must do the following:
Copy the appropriate JDBC drivers file to BE_HOME/lib/ext/tpcl. You must restart BusinessEvents Studio Explorer after copying the drivers file. This step is required before you can use the design-time Test Connection feature. It is also required for runtime.
The remainder of this section provides a few tips for each supported DBMS.
SQL Server
Here are a few helpful points about SQL Server:
SQL Server authentication for non-production purposes
It is convenient to use SQL Server authentication so you can create database users as needed. Select this option when you install Microsoft SQL Server. With Windows Authentication, on the other hand, you may have difficulties creating users without help from others in your enterprise.
Availability Group
An availability group must be ready and dedicated to TIBCO BusinessEvents so that the TIBCO BusinessEvents database(s)can be added to that group.
SQL Server AlwaysOn Availability Groups
The AlwaysOn Availability Groups feature in SQL Server 2012 Enterprise Edition is a high-availability and disaster-recovery solution used by TIBCO BusinessEvents. To implement this feature, make sure that your SQL Server setup is up and running properly as per the Microsoft documentation.
Connection Properties to the SQL Server Cluster
Use the availability group listener defined at the SQL Server cluster level in the TIBCO BusinessEvents JDBC resource. The availability group listener enables clients to connect to a SQL Server replica without knowing the name of the physical instance of the SQL Server, for example:
jdbc:sqlserver://sqlserver-group-listener:1433;databaseName=be_user
To allow faster TCP connection retries, use the property multiSubnetFailover=true even if your cluster is on the same subnet as recommended by Microsoft,
Set this property in the BusinessEvents JDBC resource in the URL field, for example:
jdbc:sqlserver://sqlserver-group-listener:1433;databaseName=be_user;multiSubnetFailover=true
To support Windows authentication, use property integratedSecurity=true
See SQL Server authentication vs. Windows authentication for details.
Windows Authentication
This authentication is only supported on Microsoft Windows operating systems.
In order for TIBCO BusinessEvents to support Windows authentication when accessing SQL Server database, follow these steps:
1.
2.
tibco.env.PATH C:/sqljdbc_4.0/enu/auth/x64%PSP%%BE_HOME%/hotfix/bin%PSP%.
3.
-Djava.library.path=C:/sqljdbc_4.0/enu/auth/x64
4.
jdbc:sqlserver://sqlserver-group-listener:1433;databaseName=be_user;integratedSecurity=true
5.
Replace the script in <tibco_be_home>/bin/initialize_database_sqlserver.sql with the following script that creates a user associated to Windows login:
use master
go
drop database be_user
go
create database be_user
go
drop login [domain\user]
go
create login [domain\user] from windows with default_database = be_user
go
use be_user
create user [domain\user] for login [domain\user]
go
grant control, alter, connect to [domain\user]
go
alter role [db_owner] add member [domain\user]
go
SQL Server authentication vs. Windows authentication
As mentioned in the Microsoft documentation, you should use Windows domain logins to access databases that are members of availability groups.
Database users that are associated to domain login added to a database in a primary replica are propagated to secondary databases, and then continue to be associated with the specified domain login.
In the other hand, database users associated with an SQL Server login will be propagated to the secondary databases without a login. The user will not be able to access data from any secondary database.
To overcome this behavior, give the SQL Server login a higher permission at the server level such as sysadmin on all the SQL Server replicas, or use Windows login. For Windows login refer to SQL Server AlwaysOn Availability Groups.
Datatype and Driver Information
Microsoft SQL Server 2008 has added a new data type, datetime2, which has a date range of 0001/01/01 through 9999/12/31. Therefore, if you are using Microsoft SQL Server 2008, then you can manually change the generated SQL script (DDL) for your backing store, and replace any affected columns’ data type from datetime to datetime2.
Oracle Database
To install an OCI driver for the Oracle database, follow these steps:
1.
2.
Configuration while creating the file tnsnames.ora should match the database server configuration (database server is running on a remote machine).
3.
Install TIBCO BusinessEvents and add the file ojdbc6.jar from the client installation to the folder BE_Home/lib/ext/tpcl.
4.
The connect URL used in the TIBCO BusinessEvents is jdbc:oracle:oci:@DBServerHostName:1521:DBInstance.
This URL can be found in the file tnsnames.ora.
5.
Other Helpful Information
Here are a few helpful points about Oracle database and drivers:
Use ojdbc6.jar drivers file.You can download this file from the following location: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-111060-084321.html
For example, if you are getting "No data found" error when the batch size is 10000, a batch size of 5000 might resolve the error, that is,
<property name="be.engine.cluster.recovery.batchsize" value="5000"/>

Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved