Database Creation

Please review the Installation Process section of the documentation to see an overview of the steps required to properly install Process Director.

Create a database and optionally a new user. You don't have to create the tables in the database; the product will create the tables as part of the installation/initialization processes. Ensure the user that will access the database has read, write and create table permissions.

SQL Server

For SQL Server, ensure the user has db_datareader and db_datawriter, or db_owner, permission to the database.

Oracle

Create a user (e.g. named 'bplogix') in the Oracle database with the following privileges:

Roles: 'CONNECT' and 'RESOURCES'

System Privileges: 'UNLIMITED TABLESPACE' and 'CREATE ANY VIEW'

Example syntax in SQL Plus, where "password" is the actual BP Logix user password:

create user bplogix identified by password;grant connect,resource to bplogix;commit;

Database Backups:

You should schedule database backups according to your recovery point objectives (RPO).  Backups of the /website/App_Data/ and the /website/custom/ folder only need to occur after configuration changes are made to Process Director (e.g. custom vars changes, changes to the IT Admin area's Installation Settings section, etc.).

If you are storing document binaries on the file system instead of the database, ensure you are adding that file system folder to your backup plans.

If you are don't require data recovery at the point of failure, it is recommend  that you change the recovery mode to “Simple” in the Process Director database. This will allow the database to perform better, but only allows for the recovery of data from the point of the last [differential] backup.

Database Maintenance

BP Logix recommends that you create a database maintenance plan that rebuilds indexes and statistics. This maintenance plan should run weekly and be configured to process all tables in the Process Director databases.

There are some additional considerations to address for Microsoft SQL Server. First, we recommend that you change the database auto growth settings in SQL Server. When an SQL Server data file becomes full, auto grow is triggered based on the auto grow settings of the database. This auto grow function causes the database to pause while the files are extended on disk. This pause in transactions can have a negative impact on performance. Auto grow is designed to be a contingency for unexpected growth. As a best practice, we recommend that when you create the database initially, that you size the database to account for any expected growth during the lifetime of the SQL instance. This means setting the initial size of the database as large as you think the database will eventually be, so that it never encounters auto grow.

We also recommend that the default SQL Server autogrowth setting be changed from a percentage (e.g. 10%) to a fixed amount, e.g. 200mb, to handle larger databases.

Also, if the transaction log file is growing too large, ensure that your backups are including the log files, or change the recovery mode to Simple.

Database Connection Strings #

The database connection string is a very important part of the installation as it connects the server to the database. Construct your connection string as you see fit. We have provided some samples below:

SQL Server

Process Director supports a Microsoft SQL Server 2005 or higher database.

Provider

System.Data.SqlClient

Default connection string

SQL Server User:Data Source=HOSTNAME;Initial Catalog=DB_NAME;User ID=USERID;Pwd=PASSWORD

Windows User

Data Source=HOSTNAME;Initial Catalog=DB_NAME;Trusted_Connection=Yes

Please note: If you are using a Windows user, the user will need to be assigned as the Identity to the Application pool of the IIS website. The Default Application Pool User ID is called “IIS APPPOOL\{app pool name}”. It is a virtual user so it won't appear in permissions dialogs when seeing a list of users, but it can be typed in.

This may be needed for customers that are using windows authentication to access their SQL Server, or are trying to access a file system (e.g. using the Export Items Custom Task) and don't have permissions. You can try adding permissions for the virtual user or you can change the application pool identity (user). Here are some helpful links:

Microsoft: Securing Resources

ServerFault: How to assign permissions to ApplicationPoolIdentity account

Oracle

Oracle requires that the DBA create the database (user schema in Oracle). To connect to Oracle the ODP.NET drivers must be installed on the web server where Process Director is located. These are available from Oracle. Process Director supports Oracle 10g or higher. The Oracle 11 ODP.NET drivers can be used with an Oracle 10 database.

Provider

Oracle 10/11: Oracle.DataAccess.Client

Oracle 12: System.Data.OracleClient

Connection string

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=HOSTNAME)(PORT=1521)))( CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DB_NAME)));User Id=USER_SCHEMA;Password=PASSWORD;