locked
One database for 15 applications - crazy idea RRS feed

  • Question

  • Our Enterprise Architect is proposing one SQL Database to contain around 500 tables with same definition as already in place databases from other instances. He wants to use Informatica to load this database nightly from the other databases with the plan to eventually retire the other databases/instances.

    His plan is for the Developers to re-develop a service layer to point all existing and new apps to this ONE database and to handle all security/auditing requirements through the Service layer.

    This is a crazy idea as we've live applications already running off other Databases from several instances. but management are currently listening to him and not the rest of us.

    Reasons not to do this ?

    1. Single point of failure : One database for about 15 applications.

    2. No normalization with little referential integrity : Tables copied from other databases into one database with no regard for RI

    3. Performance problems : From point above and ??

    4. Maintenance to one app may need a server reboot or dbstart : This will affect all applications.

    5. Patching/upgrading : All applications will be down at the same time.

    6. Recovery : Will take longer.

    7. Problems with locking/deadlocking

    8. Transaction log problems??

    9. Security becomes more complex.

    10. Collation issues - as existing db's not all on same collation

    11. Duplication of data in live environments with different security models - source and target

    12. The Business need to sign off on sharing an environment with other applications

    Can anyone give me more reasons NOT to implement this so that I have a stronger case to present to management. 

    thank you.

     

     

    Tuesday, November 15, 2011 10:38 AM

Answers

  • OK, so this is different from the original report. There are changes to the schema, and removal of relational integrity. Forget the same database part, that is a Bad Idea. A database without RI is just a set of glorified flat files.

    Only one read only role, does not usually satisfy application requirements. But that is something the application owners would know.

    As for different versions of databases, requirements are what they are. Either it is supported, or it is not. The one database will either support all requirements, or it will not. Nothing to discuss, just the facts.

    If he doesn't want different schemata, he doesn't want built-in organization. Unless the TABLEs are closely related, different schemata is a preferred method.

    I would address this situation from the factual standpoint. Either it meets software requirements for support and resources, or it does not. Prepare a report and ask for his comments. No egos (should) get in the way when dealing with the facts.

    • Marked as answer by Stephanie Lv Wednesday, November 23, 2011 11:56 AM
    Tuesday, November 15, 2011 2:37 PM
    Answerer

All replies

  • If there is no relation between those applications then yep that is not good idea
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 15, 2011 10:54 AM
  • Thanks Uri, 

    Any other reasons why not ? So that I can "beef up" my case against this ?

    Tuesday, November 15, 2011 10:58 AM
  • For better performance of the application, if it is resides on different databases then control will be more for any DBA to troubleshoot any issues in future.
    ~ Nareshkar Pakanati // www.mssqlfix.com // www.nareshkar.com // @mssqlfix
    Tuesday, November 15, 2011 11:02 AM
  • I think you should politely ask him to why he thinks it should be merged into one database. He should probably have lots of reason and ideas on to why he wants to do it, some maybe valid, some not logical, then from it you can debate with him tell him your point.  It's always nice to exchange ideas with each other. And since he's an architect he should have lots of experience with db matters and an open minded individual.


    Randy Aldrich Paulo

    MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog

    Tuesday, November 15, 2011 12:48 PM
  • Personally, i dislike it when companies create multiple databases. It causes all sorts of problems regarding who has which data, and which is the correct one. Not to mentioned having to correlate different databases with different schemes and update schedules. A nightmare so easily avoided with simple collaboration.

    In my ideal world, there is one database with a separate schema for the each separate area.

    The question i would ask is, "why do you want a different database?" If a good reason is served, a new database would be created.

    Nonetheless, if different databases were already in place, the question becomes "why do you want one database?"

    -----

    OK, enough of that. :) Now to talk about your points.

    1. One database for 15 applications is ideal. as much as it is a single point of failure, it is also up-to-date and working together. A backup server can handle failure issues.
    2. If the TABLEs are copied, why is there no RI? Was there no RI to begin with, or is the RI being done away with. This, in itself, has no bearing on how many databases are being used.
    3. By performance problems, i assume you mean resource utilization. That is simple enough to chart and see the objective facts. They don't lie.
    4. An app needing a server restart? Is the app server and DB server the same server? If so, you have bigger problems. :) As for DB start, when is that needed in your line of business? It either is a requirement or it is not.
    5. In most places i know, patches work on databases at the same time, which BTW, is a server issue, not a database issue. Besides, if there is a backup server, the issue is avoided entirely. Indeed, one database is much easier to manage in this regard.
    6. Recovery, absolutely, a very valid point.
    7. If the data are different, and uses different TABLEs, why would there be locking issues?
    8. If the data are different, why?
    9. Why does security become more complex? If anything, it gets easier with the singular user pool.
    10. A valid issue. Sounds like a show stopper as the single database cannot meet the demands of the existing applications.
    11. This needs to be delineated and shown where the issues are.
    12. That is a political issue, not a real one. It's not one we can answer here. :)

    Just thoughts on your thoughts. I'm curious to know how this turns out.


    Tuesday, November 15, 2011 1:34 PM
    Answerer
  • I have asked .... but Im not getting clear answers.

    he took over the project after the original Architect left.

    They started the idea of putting together a Conceptual Model which isnt a bad idea, this developing further into ..."why dont we try put the Conceptual Model into a Physical Model" without discussing it with DBA's, Developers.

    He doesnt have "lots of experience" and isnt an "open minded individual" and in a recent meeting the latest arguement was "well, we've secured the Budget for this and started the project, so we have to contiune now".....  Seriously :(

    I've just generated an ERD, there is currently 319 tables. This is horrible :(

    Tuesday, November 15, 2011 1:37 PM
  • Thanks Brian, interesting to read the pros of this approach and your points have given me more to think about, thank you.

     

    The different schema per dataset is a good idea ... 

     

     

    1. One database for 15 applications is ideal. as much as it is a single point of failure, it is also up-to-date and working together. A backup server can handle failure issues.

     

    Longer recovery time ?

     

    2. If the TABLEs are copied, why is there no RI? Was there no RI to begin with, or is the RI being done away with. This, in itself, has no bearing on how many databases are being used.

    I'm not seeing RI in the new model :(  no bearing agreed, but will lead to performance issues.

     

    3. By performance problems, i assume you mean resource utilization. That is simple enough to chart and see the objective facts. They don't lie.

     

    True, agreed

     

    4. An app needing a server restart? Is the app server and DB server the same server? If so, you have bigger problems. :) As for DB start, when is that needed in your line of business? It either is a requirement or it is not.


    Different App servers, db starts are needed. Ever worked with Lombardi ?

     

    5. In most places i know, patches work on databases at the same time, which BTW, is a server issue, not a database issue. Besides, if there is a backup server, the issue is avoided entirely. Indeed, one database is much easier to manage in this regard.

     

    Not all apps support the same version of SQL.  Our BI APPS from Oracle version not supported on 2008 R2.

     

    6. Recovery, absolutely, a very valid point.

     

    7. If the data are different, and uses different TABLEs, why would there be locking issues?

    There will be new apps to share the same data and they are not planning for this design wise.

     

    8. If the data are different, why?

    Apps sharing data

     

    9. Why does security become more complex? If anything, it gets easier with the singular user pool.

    He doesnt want schemas, I DO .... and he wants one Readonly role and one write role that all apps will access :(:(     

    Tuesday, November 15, 2011 1:55 PM
  • OK, so this is different from the original report. There are changes to the schema, and removal of relational integrity. Forget the same database part, that is a Bad Idea. A database without RI is just a set of glorified flat files.

    Only one read only role, does not usually satisfy application requirements. But that is something the application owners would know.

    As for different versions of databases, requirements are what they are. Either it is supported, or it is not. The one database will either support all requirements, or it will not. Nothing to discuss, just the facts.

    If he doesn't want different schemata, he doesn't want built-in organization. Unless the TABLEs are closely related, different schemata is a preferred method.

    I would address this situation from the factual standpoint. Either it meets software requirements for support and resources, or it does not. Prepare a report and ask for his comments. No egos (should) get in the way when dealing with the facts.

    • Marked as answer by Stephanie Lv Wednesday, November 23, 2011 11:56 AM
    Tuesday, November 15, 2011 2:37 PM
    Answerer
  • Cheers Brian, I appreciate your comments.

    I'm leaving my ego at the door for this one, however those that have "authorised" the budget for this need to defend their desicions and wont back down easily. 

    A set of glorified flat files indeed :( 

    I'll prepare a report and submit it and will update this post when I have news. 

    thanks

     

    Tuesday, November 15, 2011 2:52 PM