Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Is it Better to use Multiple DB than Single DB? Expand / Collapse
Author
Message
Posted Wednesday, July 10, 2013 1:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:12 AM
Points: 5, Visits: 34
Hi

In need to design an Enterprise level solution which will contain around 8 modules (such as Accounting, Human Resource, Inventory, Purchases etc). It will be a portal with single sign on to different modules.
Now the question is whether to put all the tables of all the modules in one DB or to have different DB for each module. All the modules will be interlinked (E.g. Users will be pulled from the Human Resources in the Purchase module. And any purchases made will be moved to inventory and transaction will be posted in Accounts too).

A rough estimate is that each module will have 100+ tables so in total 800+ tables in the whole system. In one module the transaction will be huge (2 million entries per month in a single table - will be archived at the end of the year)

So what will be best way to design the database. Both advantages and disadvantages

Unni
Post #1471978
Posted Wednesday, July 10, 2013 1:19 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 6:45 AM
Points: 14, Visits: 37
Single DB.
Far too many reasons to elaborate on
Post #1471980
Posted Wednesday, July 10, 2013 5:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:38 AM
Points: 128, Visits: 486
Wow, that's a lot of tables.

If they are all part of the same app, I'd go with 1 DB.

Mark



Post #1472059
Posted Wednesday, July 10, 2013 6:55 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 6:45 AM
Points: 14, Visits: 37
Just to add more confidence, I've worked with more than a thousand tables in a single database and multiple of these databases running on a single server with tables having a few hundred million records in a single table. The performance was just fine.

There are DBA's that monitor indexes etc, but this kind of architecture is very doable.

I've also seen in my experience issues experience with multiple databases using linked servers. It may be necessary sometimes but I would suggest keeping linked servers to the area "necessity" as there are limitations with linked servers.
Post #1472098
Posted Wednesday, July 10, 2013 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:12 AM
Points: 5, Visits: 34
NeverDie (7/10/2013)
Just to add more confidence, I've worked with more than a thousand tables in a single database and multiple of these databases running on a single server with tables having a few hundred million records in a single table. The performance was just fine.

There are DBA's that monitor indexes etc, but this kind of architecture is very doable.

I've also seen in my experience issues experience with multiple databases using linked servers. It may be necessary sometimes but I would suggest keeping linked servers to the area "necessity" as there are limitations with linked servers.


Thanks a lot. This really boosted my confidence.

Unni
Post #1472191
Posted Wednesday, July 10, 2013 9:17 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 8,287, Visits: 8,738
I'm inclined to disagree with earlier comments. Look at your data and see what changes or grows rapidly, and what is pretty stable and changes less often and in smaller quantity. You need different backup strategies for the different chunks of data if there is a significant distinction in those issues, unless you have store to burn in which you can hold pointless backup data, and the easy way to handle that is to put data into different databases. Of course this classification of tables may match up with the multiple modules or (more likely) not since several modules may cause updates to the same data. The discussion of linked servers is a red herring, since that's a question of how you split your data between servers not of how you split it between databases, and the question was about splitting into databases.

Tom
Post #1472197
Posted Thursday, July 11, 2013 8:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:33 AM
Points: 357, Visits: 1,928
Unless there's a compelling reason (need for security at the DB level comes to mind) to separate the data, I would go with a single DB. One of the accounting apps I used to support had the data segregated into 12 databases. It was a pain to do any sort of backup and restore to the development or acceptance servers, and writing queries was far slower because of the need to specify the database.

As for table counts, 800 tables for this sort of application is trivially small.

Having said that, I would also ask why you want to write your own ERP system when there many more than you can count available from a variety of competent vendors.
Post #1472614
Posted Tuesday, July 16, 2013 12:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
unnikozhissery (7/10/2013)
In need to design an Enterprise level solution which will contain around 8 modules (such as Accounting, Human Resource, Inventory, Purchases etc). It will be a portal with single sign on to different modules.
Now the question is whether to put all the tables of all the modules in one DB or to have different DB for each module. All the modules will be interlinked (E.g. Users will be pulled from the Human Resources in the Purchase module. And any purchases made will be moved to inventory and transaction will be posted in Accounts too).

A rough estimate is that each module will have 100+ tables so in total 800+ tables in the whole system. In one module the transaction will be huge (2 million entries per month in a single table - will be archived at the end of the year)

So what will be best way to design the database. Both advantages and disadvantages


Putting everything together in a single database sound as a good idea but, I have seen more than enough good ideas turning bad.

Think on the big picture...

How about scalability?
What is going to happen in the future if this system grows and grows? how are you planning to scale it? May be a solution would be to scale it out, on different servers in which case having different databases would make your life much easier.

How about recoverability?
What is going to happen if your HR Vice President comes one day and says, I'm so sorry, please recover the database as it was last Friday at 5:32PM? Having separate databases would make this easier? provided you have a sound backup/recovery strategy in place.

How about business continuity?
What is going to happen when you are planning your Business Continuity/Disaster Recovery plan? some applications like "billing" would be critical, on the "the company cannot survive without them" category while other applications like "HR" would be less than critical, perhaps on the "company can survive for two weeks without it" category. Having separate databases will help too, don't you think so?

In short, even if it sound counter intuitive probably having separate databases would give you a more powerful, more versatile platform to support your organization.

Just my two cents.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1474254
Posted Tuesday, July 16, 2013 9:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 35,967, Visits: 30,258
L' Eomot Inversé (7/10/2013)
I'm inclined to disagree with earlier comments. Look at your data and see what changes or grows rapidly, and what is pretty stable and changes less often and in smaller quantity. You need different backup strategies for the different chunks of data if there is a significant distinction in those issues, unless you have store to burn in which you can hold pointless backup data, and the easy way to handle that is to put data into different databases. Of course this classification of tables may match up with the multiple modules or (more likely) not since several modules may cause updates to the same data. The discussion of linked servers is a red herring, since that's a question of how you split your data between servers not of how you split it between databases, and the question was about splitting into databases.


+1000 to that. People just don't think about backups and the possible restores that go with them never mind thinking about static data that might only need to be backed up once per month and then only a part of that. It's not difficult to communicate between databases using SYNONYMs and there's virtually no performance hit.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1474361
Posted Tuesday, July 16, 2013 9:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 35,967, Visits: 30,258
PaulB-TheOneAndOnly (7/16/2013)
unnikozhissery (7/10/2013)
In need to design an Enterprise level solution which will contain around 8 modules (such as Accounting, Human Resource, Inventory, Purchases etc). It will be a portal with single sign on to different modules.
Now the question is whether to put all the tables of all the modules in one DB or to have different DB for each module. All the modules will be interlinked (E.g. Users will be pulled from the Human Resources in the Purchase module. And any purchases made will be moved to inventory and transaction will be posted in Accounts too).

A rough estimate is that each module will have 100+ tables so in total 800+ tables in the whole system. In one module the transaction will be huge (2 million entries per month in a single table - will be archived at the end of the year)

So what will be best way to design the database. Both advantages and disadvantages


Putting everything together in a single database sound as a good idea but, I have seen more than enough good ideas turning bad.

Think on the big picture...

How about scalability?
What is going to happen in the future if this system grows and grows? how are you planning to scale it? May be a solution would be to scale it out, on different servers in which case having different databases would make your life much easier.

How about recoverability?
What is going to happen if your HR Vice President comes one day and says, I'm so sorry, please recover the database as it was last Friday at 5:32PM? Having separate databases would make this easier? provided you have a sound backup/recovery strategy in place.

How about business continuity?
What is going to happen when you are planning your Business Continuity/Disaster Recovery plan? some applications like "billing" would be critical, on the "the company cannot survive without them" category while other applications like "HR" would be less than critical, perhaps on the "company can survive for two weeks without it" category. Having separate databases will help too, don't you think so?

In short, even if it sound counter intuitive probably having separate databases would give you a more powerful, more versatile platform to support your organization.

Just my two cents.


Your 2 cents are worth a million bucks. "Divide'n'Conquer" works well even at the database level.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1474362
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse