Decision criteria on when to use a non-dbo schema vs a new Database

Question

I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out whether to divide my state using seperate Databases or using seperate Schema's in the same database. I've done a little reading on SQL Server Schema's and it seems like a natural way to seperate object domains (which I like), but I'm not sure if there may be hidden costs to this pattern.

What are the more practical things I should consider when selecting between these two approachs? If I avoid the dbo.mytable in favor of myschema.mytable will I be creating other challenges (or problems) for my architecture?

As a side note... At some point this will be handed over to a real DBA to maintain/support so I'm trying to make sure I don't make their lives harder.

Answer 1

I'll start by saying don't consider schemas as namespaces or object domains in the OO sense. Schemas are essentially permission containers with some added value (see below)

Also, "separate schemas" or "separate databases" are 2 different concepts. Data that needs to be transactionally and referentially consistent needs to be in the same database. See One Database or Ten? blog article for more.

In that database, you may or may not use schemas for organising your objects.

Personally, I'm a fan of schemas and always use them but for things like permissions and logical grouping. For that, I'll refer you to previous questions where you can see the general opinion is in favour of them:

• Schema design - best practices?
• What standard should I follow when naming tables and views?
• Over use/correct use of schemas?

For the case for separate databases, see Aaron's answer, but this all hinge on the "transactionally and referentially consistent" requirement.

Answer 2

Agree with @gbn. I've architected solutions using schemas for separation and databases for separation, and I find using databases is much more practical. A couple of reasons:

1. Having your app connect to a context-specific database and then running the same queries is much easier than having your queries inject <schema> in front of all the object references. This lends itself to either a lot of dynamic SQL, a lot of different application logins with default schemas (meaning your code would not use schema prefix, relying on default schema of application user - can lead to massive plan cache bloat and difficult debugging), or a lot of stored procedures to manage (picture just a simple report, if you need one per schema, then the code changes, now you have to go change the same code multiple times, once for each schema).
2. Separating by database allows you the flexibility to move busy databases to faster or different storage / instances without having to rip objects out of one big all-encompassing database. Most folks don't think about it this far in advance, but it is definitely a potential scale problem. Especially if you end up having one schema that "takes over" and requires most of the resources on the server, splitting them out can be an extremely cumbersome task, even if they are already separated by schema.
3. Separate databases can also allow you to have different maintenance and backup schedules for each division. I am not sure what you mean by "divide by state" but assuming you mean isolating customers, you may have customers with different requirements and different tolerance for data loss, index maintenance, etc.
4. You may end up with customers who, by law or by corporate policy, need you to keep their data separate from anyone else. This is usually acceptable at the database level, but schema level is usually going to be insufficient. You may not have these customers now, but it could become a real problem later if you do.