October 1, 2008 at 2:22 pm
Let me start off by saying this has nothing to do with data migration (at least at this point).
We're a shop that has used Oracle fairly exclusively for a long time. We're looking to transition to SQL Server 2005 (or 2008), but at this point, only in that all new applications will be developed against the SQL databases. We're not migrating old stuff at this point.
My questions are more design related.
Right now, in Oracle, this is how we do things.
On our development box, when a new project comes along, the DBA creates an oracle Schema for that project, and releases it to the developers. The developers use that schema owner account to do all development work. Creating whatever tables, procedures, etc that they need. When we move to beta, we lock the schema owner account on the beta machine to make sure changes are more controlled. And of course, on production, the schema owner is also locked.
In addition, we use stored procedures to view and manipulate the data. Roles are created and given execute to the stored procedures as necessary. Then, on production, these roles are granted to individual user accounts.
That's the short version.
I'm still new to SQL Server, so I'm having some trouble wrapping my brain around some of the differences. In Oracle, for example, their schemas are similar to SQL Server's databases. SQL Server's schemas are another layer that Oracle doesn't really have. But in coming up with our general SQL Server strategies, I'm trying to go a route that is as similar to our Oracle ways as possible, but in also a way that makes sense for future scalability.
So I guess my question is this... how do folks do things in the real world, as far as general SQL Server setups go? General change control progression from a dev environment, to beta, to production? How do are rights typically handled?
I'm thinking something like this.
In dev, we would create a new database when a new project comes up. We could use our Active Directory group that identifies developers, grant them dbowner rights for that database. We'd create a schema in that DB named the same, since we usually don't need the seperation that SQL Server schemas can provide.
In beta and prod, developers wouldn't have the dbowner rights.
We'd still use stored procedures, create database roles, and grant execute of procedures to the roles as needed, then give those roles to the logins in production as needed.
Does this seem reasonable, or am I falling into any pitfalls? I still feel like I'm ignoring Schemas. Its just kind of there. The thing is, we typically don't need to seperate data within a database. We lump all the data in together, and then use different roles to determine who can execute which procedures.
October 1, 2008 at 2:38 pm
Pretty much what I'd do.
If you're not going to use schemas, put the objects in the dbo schema and set all the users' default schemas to dbo.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2008 at 2:44 pm
I like that, as far as making it easier on me, but one question. I warned you I'm having trouble wrapping my brain around SQL schemas 🙂
If we did that, would it cause objects with the same name to clash? In other words, if databaseA had an Employee table, could databaseB also have an Employee table?
October 1, 2008 at 3:45 pm
Yes, different databases can easily have tables, views, stored procedures, whatever with the same name. Being a former Oracle person myself, it sounds like you do have a good grasp of the logical constructs that make up the SQL Server database. Schemas were just introduced in 2005, so even SQL Server 2000 people went through a learning curve to understand schemas. Some other differences to note:
- It's the exception to design a table in SQL Server without a clustered index, as opposed to Oracle's indexed organized tables which were typically only used for small / seldom updated tables.
- It's much easer to return data to an application from a query in a stored procedure than in Oracle.
- SQL Server isn't as flexible when converting datetime data to varchar, there are only a handfull of formats that the CONVERT function will handle.
- There isn't a simmilar concept to Oracle's ROWID, at least not anything that is visible.
- Security can be a little more convoluted in SQL Server because you have a separate concept of login (at the instance level) from the concept of a user that you grant roles and privileges to (at the database level)
- NULLs do not necessarily act the same way as they do in Oracle, a VARCHAR empty string '' is not equivalent to NULL, and database options determine how NULL behaves in concatenation. If I remember correctly AND logical operator in Oracle would always result in NULL if either operand was NULL, but in SQL Server if one operand is FALSE and the other NULL, the AND returns FALSE. Simmilar for OR (inclusive) logical operator, Oracle would always result in NULL if either operand was NULL, SQL Server will return TRUE if one of the operators is TRUE and the other is NULL.
October 2, 2008 at 5:54 am
llevity (10/1/2008)
If we did that, would it cause objects with the same name to clash? In other words, if databaseA had an Employee table, could databaseB also have an Employee table?
Absolutely. SQL databases are independent of each other in terms of objects and storage. They're backed up separate, have separate security, etc
Don't worry about being lost. You'll pick it up quickly. I started working with Oracle a week ago and the first thing I tried to do was create a database to test in. :hehe:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2008 at 5:58 am
One other thing to add to Chris's list.
Cursors (from what I understand) are often used in Oracle and are a good way to do things. Not so in SQL Server. Stay away from cursors and look for set-based ways to do things
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2008 at 3:54 pm
Thanks for that list of gotchas, Chris. And Gail as well. We use cursors all the time in Oracle, yes.
One additional question related to schemas. Gail, you recommended to put all user's schema in DBO. I initially liked that, but in my experimentations, I did find one nice use for them. When assigning permissions, I found it was handy if you had a lot of procedures, you could add all objects belonging to a certain schema to the securables. That made it easy to go down the list and tick execute where needed.
If I'm not sticking the procs in a seperate schema for each DB, I'm finding I have to go to the securables, bring back objects of type stored procedure, check all the ones I want to modify, click okay, then go through the list again ticking off execute. I'm essientially going through the list a couple of times.
Call me lazy, but I like to think its effiency 🙂
And granted, this is something you typically do once and then are done, but it seems kind of tedious. So, is there a better way to do this without the help of schemas? Are there downsides to using schemas for just this easier method of grouping?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply