Houston Texas Questions and Answers SQL 2005 (9-15-2005)

  • Article

Good afternoon Everyone!  Thank you for coming to the event, and I apologize for the mix up in directions, I was on Dunvale Rd. at 6:30 am.  :-)  Here are the questions I took down at the event.  Please comment if you have any further questions or if I missed any.

Q: How do you migrate an Oracle database to SQL 2005?
A: This is a great question and there are tons of great resources here, to help with the migration from Oracle but also other database environments.  With that said, and focusing on the question, it is a complex answer and fortunately there is a Solution Guide for Migrating Oracle on UNIX to SQL Server on Windows.  There are a ton of tools to help you with this migration, from a lot of our partners.  What makes this a challenge, is not so much the data but the applications that ride on top of the database.  There is a fantastic white-paper comparing both SQL 2005 (beta 2) and Oracle titled: Features, strengths, and weaknesses comparison between Microsoft SQL Server 2005 (Yukon) and Oracle 10g databases.  Even though the author has some preconceived notions about both products not a badly written document.  

Q: What are the different ways to make Schema Owner Calls?
A: One of the huge benefits of SQL 2005 is schema separation, this provides several benefits. This separation of database users from schemas provides several benefits to administrators and developers.

  • Multiple users can own a single schema via membership in roles or Windows groups. This extends the familiar functionality allowing roles and groups to own objects.
  • Dropping database users is greatly simplified.
  • Dropping a database user does not require the renaming of objects contained by that user's schema. Thus it is no longer necessary to revise and test applications that refer explicitly to schema-contained objects after dropping the user that created them.
  • Multiple users can share a single default schema for uniform name resolution.
  • Shared default schemas allow developers to store shared objects in a schema created specifically for a specific application, rather than in the DBO schema.
  • Permissions on schemas and schema-contained objects can be managed with a higher degree of granularity than in earlier releases.
  • Fully-qualified object names now have four parts: server.database.schema.object.

So the question becomes is what happens when you delete the owner, well hopeful nothing but it all depends on how you called the procedure.  SQL 2005 now allows several ways to execute code:

  • EXECUTE AS CALLER - the default value, matching the SQL Server 2000 behavior, where the module executes in the security context of its caller.
  • EXECUTE AS USER= 'user_name' (or 'login_name' ) - assigns the security context of a specific database user for the duration of the module execution. This means that permissions on all referenced objects are evaluated against this user_name, rather than against the original caller (regardless of whether the module and objects have the same ownership or not).
  • EXECUTE AS SELF - designates the user who creates (or modifies) the module definition (not necessarily the same as its owner) as the one whose security context will be used to execute it (useful in scenarios where applications create modules for its users on demand).
  • EXECUTE AS OWNER - designates the owner of the module (or rather the module's schema) as the one whose security context will be used to execute it (note that in this case, the owner must be an individual user, not a Windows group or database role). If the owner is deleted this call will need to be re-written.

Q: What happens when you run Microsoft Baseline Security Analyzer (MBSA) after you rename the SA account?
A: So I downloaded the Microsoft Baseline Security Analyzer (MBSA) and got out my handy dandy Virtual PC and loaded the SQL 2005 beta 2.  So I renamed the SA account (something new in SQL 2005), and ran the MBSA against my server and who-la, I got the ever informative Unhandled Exception Error.  :-)  This is what I expected the MBSA is configured to look for the information that it is programmed to look for.

Q: How does SQL 2005 handle dirty reads?
A: There is an excellent white-paper about how concurrency and data consistency works, it is located here.  It provides a great table that outlines the isolation levels, in addition the only way to get a dirty read, just like past products, is with read uncomitted.  Check out here as well for more information on this topic as well.

Q: With the optimistic concurrency control turned on, does it copy the indexes into the tempdb?
A:  Unfortunately no, from the books online there are several limitations with concurrency row level versioning:

  • READ_COMMITTED_SNAPSHOT cannot be enabled in tempdb, msdb, or master.

  • Global temp tables are stored in tempdb. When accessing global temp tables inside a snapshot transaction, one of the following must happen:

    • Set the ALLOW_SNAPSHOT_ISOLATION database option ON in tempdb.
    • Use an isolation hint to change the isolation level for the statement.

  • Snapshot transactions fail when:

    • A database is made read-only after the snapshot transaction starts, but before the snapshot transaction accesses the database.
    • If accessing objects from multiple databases, a database state was changed in such a way that database recovery occurred after a snapshot transaction starts, but before the snapshot transaction accesses the database. For example: the database was set to OFFLINE and then to ONLINE, database autoclose and open, or database detach and attach.

  • Distributed transactions, including queries in distributed partitioned databases, are not supported under snapshot isolation.

  • SQL Server does not keep multiple versions of system metadata. Data definition language (DDL) statements on tables and other database objects (indexes, views, data types, stored procedures, and common language runtime functions) change metadata. If a DDL statement modifies an object, any concurrent reference to the object under snapshot isolation causes the snapshot transaction to fail. Read-committed transactions do not have this limitation when the READ_COMMITTED_SNAPSHOT database option is ON.

  • Any snapshot transaction that is active when the ALTER INDEX statement is executed receives an error if it attempts to reference the HumanResources.Employee table after the ALTER INDEX statement is executed. Read-committed transactions using row versioning are not affected.