Multiple Databases vs One

[Zirak]

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?

 

[willir]

Zirak

Very tough question, and importnat to determine...

Do you have a handle on the growth of each system, and will the your database engine be able to handle the growth, transactions, users, etc?

Since you will be using an enterprize system, I doubt if security will be an issue.

I suspect you can safely isolate HR from the others since there is rarely any overlap (perhaps in manning).

You need to determine the effects of down time and performance. For example, financials can usually be shut down for maintenance after hours. But production database tend to be critical 24x7. When they are down, it costs money.

You also need to determine the effects of the single enterprize database in terms of maintenance - longer backups, longer export / import / refresh, performance and bigger hardware.

Then balalnce the administration and resource issues with the amount of time and work to create your API's for communicating the information. What is the impact if an API or service and the information does not get passed in a timely manner?

We moved from a mutiple enterprize Oracle systems to a primary SAP system. I am not in a position to see the 10,000 foot picture, but in terms of impact to users - down time including the impact to production went way, way up. There might have been some cost savings on less DBA admin, but end user impct and intangiable cost of missed deliveries, proper shipping documentation and significant twidling of thumbs --- waiting, waiting, waiting, is high.

Bigger system requires much tighter mangement and expertize. Mutiple smaller systems - more admin, rock solid API's, but more flexibility.

 

[Zirak]

Thank you for your reply.
It was very helpfull.
Do you know where I can find some resources on this issue?

Thanks

 

[willir]

Zirak

I suspect you would want to look locally. You may want to consider looking at a contract / consultant services. Check their references !!! Don't just look at their past clients -- follow through and ask their past clients on service, knowledge, expertize, etc.

Some parting wisdom ...
- Often for a project such as this, the desire is to customize. Try and avoid customization as much as possible -- costs more, slows you down, and more importantly, causes problems and higher costs later on when you upgrade. Instead of asking "I want this application to do this?" (implies customization), rephrase it as "I need to get this task accomplished - what is the best way to do it?" (alternate solutions such as ODBC front end in an Access database or such.
- Invest, investigate and test work at the beginning of the project. This will save you money and time on the backend when the project is just about completed. Believe it or not, I have seen several multi-million projects go through where the prototype testing was glossed through - and the bugs, errors and incorrect assumptions became apparent when the system was put into production.
- Beware of conflict of intterest. Consultants may make decisions in their favour. For example, they may offer a customization solution and they do the customization. They are making a suggestion that results in them getting paid more. It may the correct decision, but there is that dreaded conflict of interest issue. In this case, like a docitor, a second opinion may be order.