3

I ask this specifically for SQL Server, the advantages that I can see for multiple databases are:

  1. Logs are smaller (so one runaway transaction cannot cause others (replication etc) to stall)
  2. A page corruption does not affect multiple applications (can this be isolated using filegroups?)

I can't seem to find any benefits from a single database architecture (maybe the code management is simpler).

Which architecture did you choose and why?

Improve this question
2
  • 2
    Hard to comment - how are your applications related or not related? Do you have multiple customers or just one (e.g. you use the apps in-house)? If multiple, do they use single apps independently or do they use them together in some kind of way? Does one database store data for multiple customers?
    – Aaron Bertrand
    Commented May 23, 2013 at 13:21
  • or dba.stackexchange.com/questions/11670/…
    – gbn
    Commented May 23, 2013 at 14:34

3 Answers 3

Reset to default
6

I agree with mrdenny to have 1 database per Application. 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.

Above are just some advantages of using 1 database per application. There are more, like having different requirement of database recovery models, backup schedules, collation, etc as well.

Also, you can think of logically separating within the database using different schemas.

Improve this answer
3

I would always put one application per database. This way as the applications grow I can move busy applications to new servers without having to spilt the databases. Also having multiple databases creates a security wall between the databases.

Improve this answer
3

Is the data transactionally consistent across all applications?

  • yes = you need one database
  • no = use separate databases
Improve this answer
2

Not the answer you're looking for? Browse other questions tagged or ask your own question.