Single or multiple databases

Question

SQL Server 2008 database design problem.

I'm defining the architecture for a service where site users would manage a large volume of data on multiple websites that they own (100MB average, 1GB maximum per site). I am considering whether to split the databases up such that the core site management tables (users, payments, contact details, login details, products etc) are held in one database, and the database relating to the customer's own websites is held in a separate database.

I am seeing a possible gain in that I can distribute the hardware architecture to provide more meat to the heavy lifting done in the websites database leaving the site management database in a more appropriate area. But I'm also conscious of losing the ability to directly relate the sites to the customers through a Foreign key (as far as I know this can't be done cross database?).

So, the question is two fold - in general terms should data in this sort of scenario be split out into multiple databases, or should it all be held in a single database?

If it is split into multiple, is there a recommended way to protect the integrity and security of the system at the database layer to ensure that there is a strong relationship between the two?

Thanks for your help.

Answer 1

You can use link to connect the databases. Your architecture is smart.

If you can't use a link, you can always replicate critical data to the website database from the users database in a read only mode.

concerning security - The best way is to have a service layer between ASP (or other web lang) and the database - so your databases will be pretty much isolated.

Answer 2

If you expect to have to split the databases across different hardware in the future because of heavy load, I'd say split it now. You can use replication to push copies of some of the tables from the main database to the site management databases. For now, you can run both databases on the same instance of SQL Server and later on, when you need to, you can move some of the databases to a separate machine as your volume grows.

Answer 3

This question and thus my answer may be close to the gray line of subjective, but at the least I think it would be common practice to separate out the 'admin' tables into their own db for what it sounds like you're doing. If you can tie a client to a specific server and db instance then by having separate db instances, it opens up some easy paths for adding servers to add clients. A single db would require you to monkey with various clustering approaches if you got too big.

[edit]Building in the idea early that each client gets it's own DB also just sets the tone for how you develop when it is easy to make structural and organizational changes. Discovering 2 yrs from now you need to do it will become a lot more painful. I've worked with split dbs plenty of times in the past and it really isn't hard to deal with as long as you can establish some idea of what the context is. Here it sounds like you already have the idea that the client is the context.

Just my two cents, like I said, you could be close to subjective on this one.

Answer 4

Single Database Pros

• One database to maintain. One database to rule them all, and in the darkness - bind them...
• One connection string
• Can use Clustering

Separate Database per Customer Pros

• Support for customization on per customer basis
• Security: No chance of customers seeing each others data

Conclusion

The separate database approach would be valid if you plan to support per customer customization. I don't see the value if otherwise.

Answer 5

Imagine we have infinitely fast computers, would you split your databases? Of course not. The only reason why we split them is to make it easy for us to scale out at some point. You don't really have any choice here, 100MB-1000MB per client is huge.