Pros/Cons of using multiple databases vs using a single database

None of performance, stability, optimization are true. Does anyone have a solid argument or reference article why these would be true?

Resources are not allocated to a database: the SQL Server Instance balances resources so it makes no difference

You lose:

• data integrity
• restore integrity (data in DB7 will be later then DB1)

You gain complexity:

• security (users, roles etc) have to be in all databases
• you'll have some data that doesn't fit into 1 database nicely

Options:

• splitting a database onto separate disks can be done with filegroups
• use schemas to logically separate data (based on other answer)

If you're after splitting the data by logical domain you could always look at using schemas within SQL2008 (going away from the default of dbo.) but even that is painful and can cause issues with OR/Ms that aren't expecting a non-standard schema.

I've been in the position of collating data from more than one database and it is painful and far from high-performance. You end up cacheing data or at least using tricks to maintain speed.

As a test, build 7 dummy databases. Build a query that requires data simultaneously from all 7, or at least a good number of them.

Then compare the execution plans! I think you'll win your case right there.

Good reasons to create separate databases would be to support different availability requirements or simplify administration. For example if your databases require very different backup schedules or different recovery models. Another reason would be if you may want to run them on different instances.

There are no performance optimisations available with multiple databases that you can't also achieve with one database. Can you give more detail on what you mean by "performance, stability, optimization"?