Reasons to use/not use a centralised DB

Question

We have a large ERP style central system and we're looking at moving many small Access databases into it's rather inflexible structure. In most instance the data can be stored but the process is tortuous, breaks the existing data model and makes the system harder to use. Can anyone point me to good resources to explain the pros and cons of this 'centralised all in one DB' approach.

EDIT -> when I say inflexible structure what I mean is we cannot change the schema, and therefore have to store data in ledgers. I get the benefits of the centralised approach from an admin perspective - I'm the DBA trust me I know the hassles of out of control Access. However, I'm not keen on breaking all the tenants of DB design and making it hard for our users just to make sure I can run a backup more easily.

Answer 1

I think it's going to be difficult to find a good reason not to centralize your data access.

Any database technical source will help you see the benefits - especially when it comes to administration. The main downside will be reworking your current data access to using the new structure. This may be quite involved.

The benefits are huge, though - including:

• Better scalability
• Maintainability
• Centralized management
• Centralized backups
• Ability to add meaningful security policies

Answer 2

Choose between being forced to cram your data into an arbitrary schema unrelated to your requirements, and having uncontrolled Access databases floating around? Wow, what a choice, rather in the category of "would you prefer to have someone cut off your fingers, or to rip off your ears?"

Personally, with that choice, I think I'd prefer the uncontrolled Access database. (Wow, I never thought I would use the words "prefer" and "Access" in the same sentence!) Important as all the advantages of a centrally managed database are, having a rational schema is almost certainly more important.