SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is it Better to use Multiple DB than Single DB?


Is it Better to use Multiple DB than Single DB?

Author
Message
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40381 Visits: 32665
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
unnikozhissery
unnikozhissery
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
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.

Unni
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87048 Visits: 41112
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14394 Visits: 12213
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.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search