SQL Server 2012 – Best Practices for Load and Traffic Balance

database-designperformancesql-server-2012

I am about to design an enterprise solution for a large scale application in which a windows service, MVC, and many other projects will use a database, so I am thinking about load balancing and best practices to organize my database.

I have two options :

  • use different schemas (I know schema is much like a folder)
  • use different databases

From the point view of database design which is better, and what's the best practice?

Best Answer

If the databases are going to live on the same server, then from a straightforward performance perspective, using a single database divided by schema vs. a bunch of separate databases isn't going to be any different. There is no "cost" to calling database A vs. database B (unless they are both set to auto-close, a "feature" you should avoid like the plague).

I typically advocate for separate databases, though, for a variety of reasons:

  • You seem to be separating on things that are functionally different - there is no real advantage to containing data for logically separate apps in the same database, whether you use schema separation or not - unless they truly do share the same copy of the data.
  • Easier to split out a database onto a separate server if it scales faster or outgrows the server it's on. Even with separate schemas it will be tedious to move just one app's data out of that database; if they start in their own database, it's as simple as backup, restore on the other server, and change the connection string for that app. If you have one database and you outgrow the server, you have to move the whole thing. And then again next time.
  • Easier to manage recovery separately - you may want use full recovery and frequent log backups for your source control, but don't need such rigor for the database that holds employees' favorite Power Ranger characters. You can also avoid having to restore all applications to the same point in time if you have to revert on e of the databases for some reason.
  • It sounds like more work to manage this, but really, you can make it so that managing 2 databases is not really any different from managing 2,000 databases.

In a previous life I managed a system with many databases; they were one per client with (largely) identical tables/views/procedures. I've answered multiple questions here based on my experience and knowledge through that architecture (including some drawbacks to be aware of in the first answer):

I have yet to hear anyone argue successfully for storing different data (different apps, different customers, etc.) in the same database. I think anyone who chooses to do this is either confident that their data will never outgrow that server, or is focusing on the near term goal of simplicity & minimal effort (which can be quite short-sighted, and bite you in the long run).

Related Question