June 22, 2010 at 2:47 pm
Your answer is good for you as long as you leave documentation as to your method. Organizational methods provides a means of troubleshooting difficulties.
June 22, 2010 at 2:48 pm
Something that hasn't been mentioned is that many programming tools, including those from Microsoft, don't seem to handle schemas very well. For example, the .NET data source configuration wizard in Visual Studio 2008 isn't really schema aware (you have to manually enter schema-qualified SQL). I also seem to recall fiddling with SSIS components that didn't recognize non-dbo schemas.
If you're thinking about using schemas in a project, you might want to confirm that your development and configuration tools will work as you expect before going too far down the schema path.
June 22, 2010 at 6:35 pm
Thanks for the insight.
June 23, 2010 at 8:20 am
We use schemas to group tables by their functions (rather than their contents). So:
[refr] = reference tables.
[meta] = tables that serve the application or database (for example, the table that maps column names to their labels in the application).
[secr] = tables that implement application-level security (for example the table that maps users to the columns that are visible to them).
[audt] = tables that track who did what when.
[data] = tables that contain data per se (for example, the Addresses table).
Previously, we had been making these distinctions on an ad hoc basis (for example, reference table names prefixed with 'z'). There is consensus in our shop that this use of schemas is highly perspicuous, and that it creates no particular overhead.
June 23, 2010 at 10:52 am
My two pro-schema arguments are the standard ones, but hopefully my explanations are helpful to somebody...
1. Organization
Before schemas, the database was the only container for objects (unless you used owners like schemas, but nobody seemed to do this -- I don't blame them). As a result, I have seen many SQL 2000 servers (and 2005+ that were upgraded from 2000) where there was a ridiculously large number of databases because that was the best way to segregate the content into logical containers. As humans (and especially as developers) we like to put things into containers. But too many databases makes for a cluttered server. And it also means that everytime somebody creates a new database, they need to make sure it is added to any relevant backup and maintenance plans, make sure they set the growth settings and recovery model correctly, etc. But the biggest issue with using the database as your container is that you can't use foreign keys across databases. In an enterprise environment, the objects in one container often need to refer to objects in other containers. Schemas let you do this with the proper foreign keys in place. Separate databases do not. Of course, the other choice is to not use any containers at all. If your database is small, this is fine. But as it gets larger, containers become very helpful. Are you prefixing your object names (dbo.patient_Address, dbo.patient_Visit, dbo.billing_GetOverdueAccounts)? If so, you clearly want to use containers... why not use real ones instead of using prefixed object names?
2. Security
As previous posts mention, why not grant the rights at the container-level rather than at the object level? Even if you use roles, you still need to remember to grant EXEC rights on a stored proc to that role every time you create a new proc. But if you grant EXEC rights on the schema to your role, then with each new proc you add to that schema, it inherits the security of its container. Too often, before schemas, I have seen DB users that only need to EXEC some stored procs put into the db_owner role simply because it was annoying to have to remember to grant rights on each new procedure created (or to go through and find all the procs to grant rights on when creating the new user). Schemas help us to be both lazy and secure.
December 7, 2010 at 8:50 am
Does it seem reasonable to align the filegroups by the organized database with schemas?
December 7, 2010 at 9:05 am
Denise.Smallwood (12/7/2010)
Does it seem reasonable to align the filegroups by the organized database with schemas?
The purpose of schemas are to logically organize objects in your data model. For example, Sales, Billing, and HR. Filegroups are for physical organization, perhaps each filegroup on a seperate disk drive. Generally it's two different things, but wether it makes sense depends on why you want to do it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 7, 2010 at 9:16 am
The reason I would like my schemas and filegroups to line up are for performance. The more filegroups setup the less likely for issues down the road.
December 7, 2010 at 9:43 am
Denise.Smallwood (12/7/2010)
The reason I would like my schemas and filegroups to line up are for performance. The more filegroups setup the less likely for issues down the road.
I agree that strategically splitting objects across multiple filegroups can improve performance, but I don't understand why you would want to arbitrarily associate a schema to a specific filegroup. For example, if you have a Sales schema containing large tables, you may want to have the table or partition containing sales transactions for the current period on one filegroup and the table containing historical periods on a different filegroup. A schema name like 'Sales' is a way to logically oraganize the sales related tables to make things easy for the developers who reference them in their applications and reports, and you'll want the flexibility for tables in the same schema to be split across different file groups.
File groups are a way for the DBA to organize the physical location of tables for performance reasons. If you change the schema name as a result of shuffling tables between file groups, then you break the SQL queries. For example, if you move a table from FG1 to FG2, then the SQL query "select * from FG1.MyTable" no longer works and the SQL has to be changed in the application. If I'm misunderstanding what you're wanting to do, then provide an example with more detail.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply