One large database or many small....

Hi Experts

We are debating what is best:

1. To combine all the company's data in one large database, and use schemas and file groups to create logical and physical distribution on drives and namespaces

or

2. Distribute the data into smaller databases with related data - eg. products and product description in one db, Customers in another and orders and orderlines in a third db.

Just what are the pros and cons?

regards

Jens Chr

In almost any case I would suggest one database. The ability to use foreign key constraints is enough right there. The only reason I would suggest many databases is if you might need to seperate them into different servers, but that is very seldom.

It is just so much easier to work with data in the same database versus multiple databases. You get one log, which is easier to manage (unless you have tons of disk drive channels to have multiple log channels, it will be faster to have the one log). If you seperate stuff into filegroups instead of databases, you can still backup and restore them independently if you need to, as well as position the tables in a filegroup on different drives as needed.

|||I echo Louis' sentiments - listen to him. He's a wise and experienced SQL person :)

Check out my SQL Server 2005 Video Tutorials: http://www.learnsqlserver.com/