Multiple Apps in one Database

  • I’m involved in an application where a database is to be migrated over to a new server.

    I have been told that numerous applications each today with their own database are to be migrated to a common database thereby requiring that all table/object names be prefixed with a unique name for each application. My application tables will app be prefixed with AppName_ + existing table name. This is to be shared with 30 other applications in the same database.

    I understand the security login will be set up to automatically limit to just those database objects with the same prefix

    This is to be done using the latest SQL Server version

    My question is should I be concerned with this approach

    Can backups and restores occur within a selected prefixed set of tables.

    Can one tune with the same kind of controls?

    Is there additional maintenance in releasing new changes requiring an additional security step or can this be automated to have access to similarly prefixed tables

    Are there other SQL Server issues which I should be concerned?

    Do you know of a documented case which lists these pros/cons ?

    I'm trying to argue against this move, and need to limit to just database design reasons

    Thanks

  • I have been told that numerous applications each today with their own database are to be migrated to a common database thereby requiring that all table/object names be prefixed with a unique name for each application.

    That sounds like a horrific idea for numerous reasons; I don't even know where to start.

    The first question when somebody comes up with an idea like this is, "what problem is doing this going to solve?" There is no benefit that I can think of in doing this. Now for some of the con's for which there are more than I have time for:

    1. Backing up/restoring will become more complicated and tedious. When backing up/restoring transaction logs you now get to back up and restore transactions for all 30+ applications.

    2. You can't have different transaction log backup schemes for different applications.

    3. It will be much harder to determine what application is blowing up your data or log files.

    4. One application causes database corruption then guess what application is now attached to a corrupt database? All of them.

    5. Will changing the names of the tables in the way you described not break one of the 30+ applications? Ask yourself that question and see what your gut says.

    6. It will be harder to secure the applications.

    This is to be done using the latest SQL Server version

    7. And all 30 applications have been tested against the latest version of SQL Server? Which cumulative update? :hehe:

    Don't do it. Don't let them do it. Update your resume/CV in case they decide to do it. That's my suggestion.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • I agree with all that Alan B says about this idea, except that I don't think "horrific" is realluy strong enough to be accurate. It is a downright stupid idea. In the unlikely event that the initial transition to this lunatic scheme doesn't result in utter catastrophe, you will be stuck with a system which is has all the faults that Alan describes. So all the applications have the same backup, recovery, and disaster recovery requirements? If not, merging their databases is pure insanity. Would you really be happy if something in one application goes wrong in such a way as to seriously corrupt the database causes all the applications to stop working? If loss of one disc page (to hardware failure) causes all applications to cease functioning, instead of just one? And so on, and so forth.

    Unless there's something about this which isn't visible to us that mitigates the risks here, Alan's advice to get your resumé up to date if you can't persuade TPTB to abandon this is eminently sensible if you can't persuade them not to do it. If the company goes bust because it wrecks its IT capability you'll be looking for a job, and you would be better off looking before the collapse than after (when your prospects would be tainted by association with the failure).

    Tom

  • Alan.B (5/21/2015)


    I have been told that numerous applications each today with their own database are to be migrated to a common database thereby requiring that all table/object names be prefixed with a unique name for each application.

    That sounds like a horrific idea for numerous reasons...

    Don't do it. Don't let them do it. Update your resume/CV in case they decide to do it. That's my suggestion.

    I would have to agree. Why not let all of the applications continue to be held in separate databases but on the same instance?

    Or do they ascribe to the same definition of database as being the instance instead of a database within that instance?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • There are lots and lots of issues here, but speaking from personal experience, let me tell you what the #1 biggest problem is going to be.

    You're going to have X number of development teams with completely different requirements and completely different test and release cycles all attempting to get their code out the door. Meanwhile each of the teams is stepping on the other, referring to one of their tables or stored procs or views which then can't be touched without negotiations that would make the Portsmouth Treaty ending the Russo-Japanese War look like child's play.

    And yes, there are all sorts of performance issues, security concerns (tons of security concerns) and a whole bunch of other problems to boot.

    I'd need a massively huge justification for doing something like this and even then I think I'd fight it.

    Trust me, you don't want to do this. I'm saying this as someone who made the mistake of sticking five distinct applications into a single database, on purpose. Huge mistake.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Have to agree with every one else here.

    In short: No, just no

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • Sounds for me like some one is coming from oracle...

    Sorry have to explain...

    I often have the problem with wording if i speak to a solution architect who is more comfortable with oracle than ms sql.

    Here they they always use the word database instead of instance and schema instead of database...

    if this is not the case... Don't do something like this!

  • Quick question, is there a dinosaur in the decision making team, haven't seen this kind of architecture since AS/400?

    😎

  • The first question to ask them is "Have all the devs agreed to change their code so that every last function, proc, view, job, and ad hoc query will go to the proper new tables?"

    If not, then everything will break the instant you transfer over to this new schema.

    That being said... Yes, you could do backups for individual subsets of tables, assuming you put each applications schema on a different file. But as has been mentioned, backups and restores will be infinitely more complicated.

    Also, ditto on verifying if they are talking about the same db or just the same server / instance.

    I've actually worked in an environment that already had this sort of thing set up. It was more of a "same app, 30+ different clients" setup, though, as opposed to 30 different applications. I was a on a 6 month consultant contract to set up replication because this place didn't have a real DBA. The Dev manager did all the DBA work before I got there and after I left.

    From experience I can tell you that IF (notice the capital letters) they get it set up right, security is going to be a major headache. I cannot tell you the number of times that clients accidentally got access to another client's data because something the devs did referenced the wrong table or because someone gave the wrong security access to a client login.

    So, depending on your business, this whole setup not only could be a maintenance headache, but think of all the SOX / HIPAA / PII nightmares just waiting in the wings to swoop down and cost your business millions of dollars.

    Put a dollar figure on those security issues. Take that to your boss. Compare the cost savings of consolidation to the cost of fines and lawsuits and the business's reputation / brand destruction. I bet (s)he'll sit up and take notice if you can cite examples of how this can torpedo the business.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (5/22/2015)


    That being said... Yes, you could do backups for individual subsets of tables, assuming you put each applications schema on a different file. But as has been mentioned, backups and restores will be infinitely more complicated.

    You can do *backups* of subsets of tables. You cannot however do restores of subsets of tables.

    A filegroup restore has to be followed by log restores to bring the newly restored filegroup back up to the same point in time as the rest of the database.

    Hence, if all apps are in the same database, the question "Can you restore App3's data to yesterday 4pm as there was a mistake in the overnight processing" has the answer "No."

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (5/22/2015)


    Brandie Tarvin (5/22/2015)


    That being said... Yes, you could do backups for individual subsets of tables, assuming you put each applications schema on a different file. But as has been mentioned, backups and restores will be infinitely more complicated.

    You can do *backups* of subsets of tables. You cannot however do restores of subsets of tables.

    A filegroup restore has to be followed by log restores to bring the newly restored filegroup back up to the same point in time as the rest of the database.

    Hence, if all apps are in the same database, the question "Can you restore App3's data to yesterday 4pm as there was a mistake in the overnight processing" has the answer "No."

    All good points. Thank you for the clarification.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Thanks so much for all your all the responses. Very informative and now have some detail reasons/ concerns to pose. The migration effort is not an argument unfortunately because modifications will be mostly automated with find/replace effort. Retesting of course could be significant. However as an application developer I was lacking some of the details surrounding the database concerns. Thanks so much to all

  • mrl6254 (5/21/2015)


    I’m involved in an application where a database is to be migrated over to a new server.

    I have been told that numerous applications each today with their own database are to be migrated to a common database thereby requiring that all table/object names be prefixed with a unique name for each application. My application tables will app be prefixed with AppName_ + existing table name. This is to be shared with 30 other applications in the same database.

    ...

    My question is should I be concerned with this approach?

    ...

    What you described above is so colossally stupid from a design perspective; you should really be concerned about your oganization's viability going forward and your job. That's what's really at stake here.

    Everything the database architect is planning to do is wrong. Multiple application databases can be consolidated on one SQL Server instance; no problem. If you have a few related applications that you want consolidated on one common database, then the approach should be one schema for each application, perhaps with connom reference tables contained in a separate schema. Security should be implmented using roles and object permissions.

    I'm glad that a smart SQLServerCentral member like yourself would recognize the potential disaster about to happen.

    I'm curious, where did the architect of this plan these ideas?

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Other than reducing the number of databases to manage, I cannot see any benefits.

    This would mean fewer databases to manage, but this database could, if not would, be a nightmare to manage.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply