locked
One database vs. multiple-- which is better? RRS feed

  • Question

  • Is it better to separate concerns if I'm using several dozen tables, into separate databases, or to keep all tables together in a single database?

    I have a few dozen tables in a single database, with a few clearly identifiable systems:

    -membership system

    -large collection of text data (a collection of reading samples and English questions)

    -management (buildings / classrooms / books to order and so on)

    -media organization

    Currently, they are all related-- they all serve a single business.  However, I'm going to add new apps that will only use one or more of these systems.  For example, I'll be adding a bus system that will only need to use membership, but none of the other things.  Or I'll make a game that uses the text data and the membership system, but none of the other things.

    Is it better to have a membership database, a media database, a management database, all separate, and use cross-db queries?  Or is it better to keep everything together?

    Monday, August 10, 2020 11:36 PM

Answers

  • Hi bennyboy1973,

    Suggest you  separate databases. There are many good reasons to do that :

    1.Most importantly Log : SQL Server uses transaction log to be able to allow a point-in-time recovery in case of any disaster, provided that log backups are regularly taken when the database is online.

    2.Full Backups : Easy to manage per application and can be prioratize based on critical to less critical application databases.

    3.DR situation : Easy to manage in scenarios like application crash, server down or failover scenarios. You have the flexiblity of just performing failover of 1 application or all - depending on if you want to test DR for just 1 application or all.

    4.Performance consideration and Problem Isolation : When the application gets busy, it can easily be migrated to new server with less activity. Also, think about locking, blocking and deadlock situations where the same database is being accessed by multiple applications causing all sorts of issues for troubleshooting. It becomes easy to just concentrate on a single application performing bad when you have 1 database per application.

    5.Capacity Planning : You can easily establish baselines to see what applications will require more disk space in the future and possibly migrate them to a new server having enough disk space.

    6.Database Maintenance : The maintenance will be easy as you can concentrate on specific application's databases and give them priority as opposed to just one single database for all applications. The overall maintenance time can be reduced when performing checkdb, rebuild/reorg indexes, update stats, etc.

    Refer to this similar thread to get more information.

    If the response helped, please click "Mark as Answer" and upvote it.


    Best regards,
    Cathy 


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    Tuesday, August 11, 2020 2:21 AM
  • How would you handle a restore case if you separate into several databases? Is it OK to have your game database referring to a member that doesn't exist, since you restore the member database to an earlier point in time? And you can't have foreign key crossing the database border. Stuff like that!

    Potentially, if these are indeed separate systems, you make them independent of each other. Perhaps you have some master database for personal info, and from that database you export data to the other databases. I.e., a database isn't dependent on another database.

    As you can imagine, this can be a bit messy, so think carefully here!


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by bennyboy1973 Wednesday, August 12, 2020 2:17 AM
    Tuesday, August 11, 2020 7:27 AM

All replies

  • Hi bennyboy1973,

    Suggest you  separate databases. There are many good reasons to do that :

    1.Most importantly Log : SQL Server uses transaction log to be able to allow a point-in-time recovery in case of any disaster, provided that log backups are regularly taken when the database is online.

    2.Full Backups : Easy to manage per application and can be prioratize based on critical to less critical application databases.

    3.DR situation : Easy to manage in scenarios like application crash, server down or failover scenarios. You have the flexiblity of just performing failover of 1 application or all - depending on if you want to test DR for just 1 application or all.

    4.Performance consideration and Problem Isolation : When the application gets busy, it can easily be migrated to new server with less activity. Also, think about locking, blocking and deadlock situations where the same database is being accessed by multiple applications causing all sorts of issues for troubleshooting. It becomes easy to just concentrate on a single application performing bad when you have 1 database per application.

    5.Capacity Planning : You can easily establish baselines to see what applications will require more disk space in the future and possibly migrate them to a new server having enough disk space.

    6.Database Maintenance : The maintenance will be easy as you can concentrate on specific application's databases and give them priority as opposed to just one single database for all applications. The overall maintenance time can be reduced when performing checkdb, rebuild/reorg indexes, update stats, etc.

    Refer to this similar thread to get more information.

    If the response helped, please click "Mark as Answer" and upvote it.


    Best regards,
    Cathy 


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    Tuesday, August 11, 2020 2:21 AM
  • How would you handle a restore case if you separate into several databases? Is it OK to have your game database referring to a member that doesn't exist, since you restore the member database to an earlier point in time? And you can't have foreign key crossing the database border. Stuff like that!

    Potentially, if these are indeed separate systems, you make them independent of each other. Perhaps you have some master database for personal info, and from that database you export data to the other databases. I.e., a database isn't dependent on another database.

    As you can imagine, this can be a bit messy, so think carefully here!


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by bennyboy1973 Wednesday, August 12, 2020 2:17 AM
    Tuesday, August 11, 2020 7:27 AM
  • Hi

    If you have separate app which require a minimum resource ,Why use a huge one.

    So better for a separate databae

    Thanks and regards

    Tuesday, August 11, 2020 8:33 AM
  • Yeah, the idea of orphaned data after a restore is really terrifying.

    After thinking about your comment, I'm starting to get some idea how to organize.  I'm thinking:

    -a totally separate membership system per app

    -an additional "SuperId" membership system that can link accounts in the various apps if I want.

    -separate DB for media, with the understanding that the apps MIGHT end up with orphaned data that has to be handled.

    Wednesday, August 12, 2020 2:17 AM