Click here to monitor SSC
SQLServerCentral is supported by Redgate
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
Posted Wednesday, July 17, 2013 5:17 AM



Group: General Forum Members
Last Login: Yesterday @ 12:29 PM
Points: 17,173, Visits: 32,140
I'm somewhat inclined to agree with the single database argument here (the issues of backup & recoverability not withstanding).

But, the bigger question for me is the development and release cycle. These 8 different modules, are they being developed by a single team of developers on a single schedule? Just exactly how completely interactive are they? Sharing lots and lots of inter-dependent data, or just a few general lookups.

I had a system that, on the surface, sounds a lot like yours. So I made the decision to put it into a single DB. For terms of backup & restore and other general maintenance it's worked fine. It's also worked well for performance. But, with five different development teams making changes and releases on different schedules, we had HUGE bottlenecks on releases to production, testing, coding interdependencies that shouldn't have been there... Also, come to find out, the amount of information that was literally shared between these systems was minimal. The requirements gathering had, rather than look at the actual requirements of these systems, looked at a previous failed project that had put the data into overly protected individual silo's of information.

So, before you go down the single database route (which can work just fine), I would dig down a little further on the processes and business needs of the system, not the technology.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1474493
Posted Wednesday, July 17, 2013 6:19 AM


Group: General Forum Members
Last Login: Thursday, February 19, 2015 11:17 AM
Points: 23, Visits: 42
Thanks everybody.

This solution will look something like ERP.
First a product was created which was specific to one industry (has its own hardware and software).

Now after couple of years, some major requirement has come to add different modules like
a. the users and managers of the system with payroll/time allocation etc (so HRMS).
b. the assets/devices/spares etc bought for the product (so Inventory)
c. The age of asset/services etc (so Asset management)

Like wise many more modules have been identified and each of these submodules should be pluggable (customers can decide which to buy with the main product)

One big team will be working on the project.
And as of now we have decided to use different DB for each module

Thanks a lot again.


Post #1474538
Posted Wednesday, July 17, 2013 7:49 AM



Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 42,081, Visits: 39,473
unnikozhissery (7/17/2013)
And as of now we have decided to use different DB for each module

There is a possibly big "gotcha" when using multiple databases for such a project that us folks recommending multiple databases forgot to mention. Declared Referential Integrity (DRI). You might know it better as "Foreign Keys". Foreign keys don't work across databases. If you need DRI across databases, you need some form of well written trigger. You can get around this somewhat if the DRI is to a small lookup table simply by having a copy of such "standard" lookup tables in each module which would allow each module (DB) to operate in a stand-alone fashion but, if you have module specific tables and there needs to be DRI between such table across modules then, like I said, you'll need triggers for the cross database DRI. If that's going to become a huge headache, it may very well be that a single database with a different schema for each module might be the better solution.

Apologies for not thinking about that particular "little" nuance.

--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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1474623
Posted Wednesday, July 17, 2013 5:45 PM

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 9,833, Visits: 11,907
A little more detail about the "gotcha" that Jeff pointed out. You need pairs of triggers for each cross-database foreign key relationship: one trigger in the referring database, and one in the referred to database. The trigger in the referring database is pretty trivial to write. The trigger in the referred to database can introduce performance issues, and if you want cascade or similar actions on delete/update it can be quite a complex trigger.

In my experience, the gotcha has not actually been a problem, but that's because it's been possible to split the data into databases in such a way that cross-database foreign keys were not needed in the cases where I've chosen to split; that is definitely NOT the general case, so you need to make sure you are not buying yourself code complexity by splitting into separate database.

Of course the trade off here can be that splitting into separate databases with separate backup and DR plans can give you quite a big performance gain (for example if you can use simple error recovery for some of your data that saves the cost of doing log backups for updates to that part of the data, and if some of the data doesn't need to be recovered right up to date but can be a few days or weeks or months behind (perhaps because the loss is self correcting, as it often can be for archive data in a system that requires a few days/weeks/months of recent data in live as opposed to archived storage) the backup costs can be reduced still further. But you have no guarantee of such performance gains until you have carried out a detailed analysis of the effects of splitting the data on your system. And triggers are so easy to get wrong (for example most of the DBAs I've known have written single row triggers; so the system won't work if it uses any set-oriented operations; of course the DBAs can be educated to write triggers that cope with set-oriented operations, but you need to make sure that you the DBAs have that education since forbidding set-oriented operations is almost always a performance disaster.

Post #1474843
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse