Sang,
Just a couple of thoughts:
In my opinion, there are advantages and disadvantages to both.
In an oracle world, what you describe is more attractive than in SQL server because in Oracle - different databases require different instances of the server and this can get expensive from a resource point of view. In SQL server, you can have multiple databases on the same server and the potential downside is wasted space because each database has its own transaction file and needs room preallocated for growth rather than 1 tran file and room for one database.
Setting up development environments is smaller for 1 DB per application and easier for 1 size fits all (all app data in 1 DB)
Backups are smaller and faster - as are restores with individual databases - vs - there are more things to back up and check that the backup was OK.
With individual DBs, you can set one to trunc log on checkpoint and have others with this option turned off.
With individual DBs, you can put one DB into single user mode for maintenance and the others are unaffected
With individual DBs, it is easier to migrate onto multiple machines if this should be seen as advantageous in the future.
If an application should become 'messy' and you need to figure out what is used and what is not used (tables, etc,), it is easier if you are just looking at the tables for 1 application rather than for several - although this advantage of multiple DBs can be mitigated if using 1 DB if you have naming standards which include a hint of the application in the name of the table, view, etc.
All in all, I would say multiple DBs in SQL server but possibly 1 DB in Oracle.
. . . Tom
On 03/27/2002 07:35:39 AM "Nahm, Sang Y" wrote:
> Hi
>
> As web interface to applications blurs the application boundaries it becomes
> less apparent to users which databases they are accessing. We have a few
> people in development who think database design/modeling need to follow that
> trend meaning that we should have one database with one schema/owner to
> support multiple applications.
>
> I find it troubling since it does not provide flexibility of separating the
> applications out into another database if needed in the future. Unless they
> share the same objects I do not see why they should be in the same database.
> It defies all I know of database modeling.
>
> Can anyone give me any benefits of putting all objects into one database
> owned by one even though they're not shared?
>
> I posted this question on Oracle DBA forum and got some answers, but wanted
> to hear from SQL DBAs.
>
> Thank you.
>
> Sang Nahm
>
-----------------------------------------------------------------
Visit our Internet site at http://www.reuters.com
Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
MS Sql Server LazyDBA home page