Dear tech guru,

Is it possible to split a BaanERP database to multiple BaanERP databases all on single database server for performance and data security sake? Our single database scheme now has grown so big that we have a 35 GB database file. Another way would be to split it up on database server level by distributing that single database to multiple files. Thanks for any input

Rgds,
Paul

Paul,

Yes it is very much possible atleast in BaaN-V+ series. We have 35+ databases and few of our biggest databases are over 60 Gb.

Here are some threads which would be useful for you -

Two databases under the same instance baan (http://www.baanboard.com/baanboard/showthread.php?t=13159&highlight=multiple)

2-tier architecture (http://www.baanboard.com/baanboard/showthread.php?t=22492&highlight=tier)

Baan5c 3-Tier Install on MS SQL2000 (http://www.baanboard.com/baanboard/showthread.php?t=12943&highlight=tier)

BaaN Infrastructure (http://www.baanboard.com/baanboard/showthread.php?t=10326&highlight=tier)

How to Configure a Remote user (http://www.baanboard.com/baanboard/showthread.php?t=448&highlight=tier)

You can also have multiple application servers (multiple BaaN instances) communicating to one or more database servers. Ofcourse, you have to consider load balancing, fail-over, DR, code changes/deployments, OS/BaaN maintenance issues, EDI-in/out (data feeds/extracts), Job Schedulers etc.

Dear tech guru,

Is it possible to split a BaanERP database to multiple BaanERP databases all on single database server for performance and data security sake? Our single database scheme now has grown so big that we have a 35 GB database file. Another way would be to split it up on database server level by distributing that single database to multiple files. Thanks for any input

Rgds,
Paul

Keep in mind that you cannot split up a single company across multiple
databases - or if you write transactions between companiess you cannot either.

Dave

Hi dave,

What is the reason you cannot split single company over multiple databases?
Is it a restriction of the database driver or database driver level type?

Thanks in advance

Prashanth, thanks for the pointers. But it seems like all those are about multiple database servers, or did I read them wrongly (please bear with me as I'm primarily functional consultant :) ) ? I'm actually wondering whether it is possible to have multiple databases on a single database server pointing to a single BaanERP application server.
The built in help are hopeless, and I can't seem to find useful guides in Baan Support. I can ask help from them, but usually the fellows here in this board are quicker to answer :)
Thanks for any help, guys

Rgds,
Paul

Hi dave,

What is the reason you cannot split single company over multiple databases?
Is it a restriction of the database driver or database driver level type?

Thanks in advance

Hi Nandan,

techically you can split it, but it is not advised at all. If a transaction updates more than one table within a company (which is the common scenario), then an abort will not be able to rollback cleanly anymore in all situations. So transaction safety is endangered.

The same reason applies, if you have transactions accross several companies.

Regards

Markus

I'm actually wondering whether it is possible to have multiple databases on a single database server pointing to a single BaanERP application server.
Yes. I found some good information in other Oracle forums-
On 1 server you can install the Oracle binaries as example in
c:\oracle\8174 directory.
Then you can create a database with all of the required control files, log files, datafiles and call this instance PROD1. You can then create another database with all of the required control files, log files, datafiles and call this instance PROD2. The instances are separate and do not share anything between them selves. If you would like PROD1 table employee to be accessed by PROD2 then you create a database link between the 2 instances.
If you are able to create only 1 instance such as PROD1 and the tables on PROD1 and PROD2 have different names then you use roles assigned to the users id's that allow access to certain tables only.
As example: tables EMP,DEPT,CITY,STATE are created on 1 instance but userid PETE is only allowed to use EMP, and DEPT where TOM is allowed to use CITY, and STATE and MARY is allowed to use ALL tables, create roles that allow the desired access and assigh the roles to the users.
Create role ROLE1; grant all on EMP to role1; grant all on DEPT to role1; grant role1 to PETE; and so forth.
With only 1 instance PROD1 then you need one tnsnames.ora entry to access the database.
We have 1 instance that is a datawarehouse and has 11 small applications also. Some of the tables are used exclusively by the warehouse and others are exclusive to the applications and some are used by the warehouse and applications. Roles are created to allow different actions against the tables and the roles are granted to the different userid's depending on the user needs.
This way, when there is a change in the user requirements I only need to change the roles not all of the userid's.

If you have multiple instanceson the server each instance has it's own set of datafiles and are not shared. Think of the Oracle binaries as a deck of cards and each suit as an instance, and each card a datafile. The different cards do not fit in the different suits( different datafiles are not part of the different instances.

Also refer to the threads -

Rick -- Thanks for the question regarding "sharing an instance vs using separate instances?", version 7.3.4.2, 8.0.5, 8.1.6 (http://asktom.oracle.com/pls/ask/f?p=4950:8:14448912351646521543::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:897828464193)

You can also search on -

http://technet.oracle.com or Oracle Metalink (http://metalink.oracle.com)

Thanks, guys, for all pointers, and Prashanth for going all the way to Oracle forums to find help. We found out from Baan Support that we can't have multiple database or database servers for our BaanERP if we're using SQL Server (they said that BaanERP SQL database driver doesn't support that)

Rgds,
Paul