SQL 2K5 - Multiple databases vs. Multiple files
- by Bob Palmer
Hey all, quick question.
Our current legacy system was built using multiple distinct databases (about ten of them). These are all part of the same discreet system, and a large number of SPs and functionalty span multiple databases. There are also key relationships that span (for example, a header table may be in database A with history, etc. in database B).
When deploying multiple copies of our app to the same server therefore, we have to use multiple instances (because the database names are coded into so many sprocs).
We're evaluating the idea of taking these ten databases (about 30gb total with individual sizes ranging from 100mb to 10gb) and merging them into a single database.
Currently, we have our databases spread accross multiple spindles for better IO. The question I have is whether or not there is any performance loss or benefit of having 10 different databases vs. 10 different database files?
i.e. rather than having three databases (A, B, and C)
Disk D: A.mdf (1gb)
Disk E: B.mdf (4gb)
Disk F: C.mdf (10gb)
Disk G: A_Log.ldf, B_Log.ldf, C_Log.ldf
have one database (X)
Disk D: X1.mdf (5gb)
Disk E: X2.mdf (5gb)
Disk F: X3.mdf (5gb)
Disk G: X1_log.ldf,X2_log.ldf,X3_log.ldf
Thanks!
-Bob
