The correct distribution of files on P4 & RDBMS

[tommy]

Have anybody found the golden wisdom about what is the correct and most efficient and most secure distribution of the files over P4 and RDBMS?

Only pushing some files to RDBMS would cause challenges with regards to backups due to the nature of the way SC handles "transactions".

[PaulG]

Here is our analysis:

Constraints:
1) We gotta have efficient reporting through SQL tools
2) backups are important enough to do our best


So, we think the right way to go is to push all data tables to a RDBMS, and leave all system files on P4. We'd then back up P4 when needed, and leave our RDBMS do the rest.

We have Oracle, which is bad for SC. Every "long" field in P4 becomes a new table in Oracle, leading to 1800 tables or so if you want to have full access for reporting. With DB2 or other systems, we'd be at 400 tables or so. Still too much, but somewhat manageable, at least.

[imcclell]

We use oracle as well, but our information is pushed out to oracle as it's appropriate field or in the case of unlimited text-fields, pushed out as long-raw.

[saburns]

we use UDB, and same strategy as Paul G. UDB admins have configured the system to do 'hot backup' each night; we have SC do same for P4 each night.

UDB is much friendlier, though, it lets us put multiple long varchars in 1 table. Also, we have converted many of those to varchar() with great success (SC still perceives them to be array with no issue!).