Database support: Which database choice will give us the best level of support?

From RiskWiki

Jump to: navigation, search



Does the Choice of Database impact the level of support?

No. All BPC RiskManager V6 systems use SQL Server 2000, 2005 or 2008. It is reasonable to expect during the product version release life of V6 all future releases of MS SQL Server will also be supported. Most remaining RiskManager Express clients (those that have not upgraded to V6) use Oracle, but it also supports all current versions of MS SQL Server. BPC RiskManager V6 has considerably more data-elements than Express and is not back-wards compatible with BPC RiskManager Express (RiskMan) databases.

All V6 customers should choose the latest possible version of the SQL Server database engine that has been released for at least 6 months. V5 customers may also choose Oracle 10 and 11 series databases but are strongly encouraged to choose MS SQL Server equivalents if available in your organisation as this is our primary development database platform.

Selection between SQL Standard/Enterprise versus SQL Express alternatives entirely at your discretion and will be determined by your data volume and user connection needs. The selection of RiskManager V6 or RiskManager Express V5 does not impact the version of SQL installed by your.

We maintain concurrent development tracks for both V6 and Express V5 systems.

Does concurrent development of V6 and V5 (with its Oracle user base) impact support with respect to database version?

This is a good question. At this point no – because RM 6 clients are all SQL Server and Express Clients are virtually all Oracle (and include some of our oldest, most loyal clients).

Updates for BPC RiskManager Express V5 are released on Oracle and SQL Server concurrently.

Going forward (assuming you request, or we decide, to release an Oracle version for V6)…the honest answer is yes and no. I expect we will always develop on SQL Server for V6 and future versions (although this depends on which system has the larger client base), and release beta versions on SQL Server. We will then release the Oracle port of the same solution (this may be only a week apart – but the order will most likely be SQL Server first).

Once a BPC RiskManager V6 Oracle version is in production there will be no difference in support, appearance or capabilities of RiskManager V6 on Oracle versus SQL Server. The current release of the application server can talk simultaneously with databases from multiple database servers all running different models and versions of database engines as long as it has an appropriate available ADO Driver library.

Does (or would) the choice of database impact the system capabilities?

No - aside from the obvious fact that Oracle is not available as a current choice for V6 (but is of V5 Express). In the event that additional brands of database engines were adopted for V6 the user and administration experience is identical across all databases.

The client and business logic are separated from the database layer using a three stage database virtualization layer in both V6 and Express:

  1. The lowest is MS ADO which provides a common database interface layer in terms of database connectivity)
  2. Classic areas of incompatibility across databases lie in the use of identity (auto-incrementing) data fields which are supported in SQL Server but not in Oracle – we do not use them, but rather have reproduced that functionality using triggers which are database independent and maintain our own auto-incrementation field table, - and in the syntax of table joins which we handle through a preprocessing layer which automatically adjusts the syntax of joins depending on the database. In spite of the fact that V6 actually only currently deals with SQL Server – it still applies this step to join syntax.
  3. Lastly the data manipulation and multi-user data integrity reconciliation is handled in the application layer and records are reconciled at the field level (rather than row level at the database level) so if two users update the same record but different fields the reconciliation layer is smart enough to generally work out the correct combined update.

These methods were all developed for RM version 2 which had a mixed Oracle and SQL Server client base. Hence the brand of database has very little impact on the operation of the system, nor the skills required of the support team. Database specific issues are almost never the cause of support related issues.

If cross database support is so easy now why do you anticipate 2 months to release an Oracle version?

Essentially because of 2 reasons:

  • The use of blobs is considerably greater in RM6 than in Express including a few places where multiple blob fields are present in the one table. Blobs are traditionally handled differently across the different database brands and require specific attention to ensure correct operation.
  • The use of dynamically created SQL statements is greater in RM6 and the probability that some of those SQL statements are not passed through the syntax standardisation layer is greater than otherwise. As this layer adjusts between join types, statements that are not passed through but should have been, will simply not work because they will fail at the syntax level rather than work incorrectly and in any case, all database interaction is held in only a few code modules so it is a reasonably mechanical process to check and fix.
  • There are potentially some SQL constructs used dynamically that must be presented differently in Oracle
  • There are many more stored procedures and some complex structures like NSTree generators and recursive association tree walkers that may have to be preconceived or for which their are built in capabilities in current oracle systems that should be used instead.


CopyRight Bishop Phillips Consulting Pty Ltd 1997-2012 ( Database support: Which database choice will give us the best level of support? )
Personal tools