Hi,
If designing an Enterprise Resoucrce planning application, what is the pros and cons of having multiple databases set up (sql server 2000) that the appliation will use vs having one huge database that integrates all the data.

For example having a databases for Accounting, Operations, Human resources ... Or all tables in one place.

The obvious is that if we have multiple databases we can set up job and maintenance plans specifically for each databases according to the number of uses and types of transactions. On the other hand I don't know if its going to be possibe set up a replication architecture for multiple databases on one publication...

Any ideas?

Hi
I'm workign for a fairly big insurance company (1.7 milj. custmers) and we have implemented that one database princible. We are very happy with it, but of course there are some pros and cons:

PROS:
- No more data inconsistency - one data in a one place
- Very easy to build new modules and add new features. No need to wonder, where to fetch data.
- Easy to operate - it's either working or not. It's actually Mark Twain's Dumbhead Walton princible: "Put all eggs in same basket and then be extremely carefull".
- Performance is rather good.
- No need for any replication etc.

CONS:
- We need to have now and then total service breaks. Not possible just stop one application.
- Recovery from any problem (hardware, software) has dramatic effect. Any and all updates are rolled back. We have build some application logs to lessen this effect.
- Need to have powerfull hardware. For us, a IBM z/OS with sysplex is a must.

Cheers, Bill

Thanks for your reply.
It was very usfull.