Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 9 down vote accepted

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 the 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):

Edit, I didn't really make the case for separate databases. See Aaron's answer, but this all hinge on the "transactionally and referentially consistent" requirement

share|improve this answer
1  
@gdn This answer addressed one of my biggest concerns, and that was that I was viewing this from the perspective of a developer (e.g. object-focused) and not with the eyes of a DB professional. Yes… I was thinking object namespaces and domains. Thanks for steering me back on course. –  JoeGeeky Jan 22 '12 at 10:58
    
Data that needs to be in transactionally and referentially consistent ... state? I want to edit but I'm not sure what the next word needs to be? –  jcolebrand Jun 14 '12 at 18:08
    
@jcolebrand: just remove "in" –  gbn Jun 14 '12 at 18:09
add comment

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.
share|improve this answer
3  
The golden question now is "is all data transactionally and referentially consistent"? I assume now and your answer is correct for this –  gbn Jan 21 '12 at 19:25
    
@gbn That’s a really good question and something I need to give some thought too before committing on a path. –  JoeGeeky Jan 22 '12 at 10:53
    
@AaronBertrand This is interesting... Sounds like I need to do a little capacity planning, and see where scaling issues may arise. If scaling horizontally is suitable, then separate databases may be a good choice? Otherwise, I'll have to consider other patterns. –  JoeGeeky Jan 22 '12 at 11:05
2  
@JoeGeeky: subject to "transactionally and referentially consistent", a single database can be scaled with filegroups too. Anyway, you have 2 valid ansers based on your use case. Neither is incorrect. –  gbn Jan 22 '12 at 11:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.