Derrick Leggett Blog

I know this is a little late, but I wanted to finish it up for my own purposes.  The final day of PASS was incredible.  Anytime you get a chance to listen in on Kimberly Tripp you should take that chance and remember it.  This is how a PASS presentation should be.

Very Large Databases with SQL Server 2005 (by Lubor Kollar) -- **GOOD**

The presentation by Lubor was a high-level presentation that led really well into a lot of the presentations at PASS.  He did a good job of explaining how the engine works to determine locks.  He also explained the threading/fiber technology used by SQL Server and how that ties into the OS.  He covered many of the concepts in 2005 that allow it to scale better.  One of the main items is the advanced partitioning and online operations in 2005.  I'm already setting up databases in the betas using this technology.  It's going to be a good learning curve for DBAs not participating or looking at the betas to learn all these new features.  You NEED to get started now if you haven't began looking at SQL Server 2005.

Security in SQL Server 2005 (by Girish Chander) -- **Excellent**

This guy should give presentations for a living.  :)  Girish heads up the security team responsible for rewriting SQL Server Security.  They have done just that.  With the exception of the CLR, security could be the single biggest change in terms of impact, administration, and skill transition.  Here are a few of the highlights: 

• SQL Server logins have been drastically changed.  They now tie directly into the Windows API if you are using W2k3 to enforce the complexity of the passwords.  There is only a simple complexity check in W2k since the API isn't available.  Blank passwords are off by default, which shouldn't be allowed at all.  :)  You can now disable logins and worry about deleting them later.  We're in the middle of a security audit and redesign right now for SOX.  I wish this feature was availabe now.  You can turn the password policy and expiration (yes, I said expiration) off.  That won't happen where I work though. 
• Everyone has, or should have heard, of how schemas are now handled.  The naming convention for objects is server.database.schema.object.  Objects are now owned by schemas.  Users own, or are allowed access to, these schemas.  This allows deleting of users not previously possible without renaming all their objects, etc.  It's a LOT like the Oracle model (shhhhh).
• You have new permission levels that are useful.  You can now give someone rights to JUST execute procs, making them part of their own schema.  You also have a view definition that allows you to see only the metadata.   This will be nice for auditing purposes.

Here are a few concerns I have from the presentation:

• Deny always takes precedence, regardless of the level it's implemented at.  I understand the reasoning for this.  This will continue to cause major issues though when you need to grant an exception access to denied objects.  There should be an override feature.  Oh well....such is life.
• You can grant someone without permissions to an object the right to that object by using “EXECUTE AS 'user'“.  This is a nice feature, but you need the ability to override it and deny people the ability to execute this.  A reporting person in accounting shouldn'be be able to grant a janitor the rights to see sensitive accounting data.  A few people recommended this in the session, so hopefully this will be addressed.
• There is still no really good solution to database ownership chaining.  The need to do this is just a fact of life.  We end up granting access directly to tables at times because this has not been addressed correctly.  Microsoft needs to recognize the issue and deal with it by making a “super group“ permissions level that can deal with this type of issue without granting cross-ownership of the whole database.

SQL Server 2005 Partitioning - The Rolling Range (by Kimberly Tripp) -- **ROCKS!!!!**

I don't know what to say about Kimberly.  She is GOD might work.  :)  Anyone who doesn't know how to use partitioning or thinks it's “one of those things you might use in a specialized shop” needs to download this presentation, study it, learn it, and realize the impact it can have on their business.  We will be implementing it where I work to fix some of the large table hot spots we currently have.  SQL Server 2005 does a great job of making this more manageable and useable.  Kimberly Tripp not only explains how things work, she gives you a step-by-step demo of how to use.  She also addresses many of the challenges faced when implementing partitioning. 

Summary

The PASS Conference this year was awesome.  I was disappointed they didn't have more presentations giving comparisons between 2000 and 2005, including areas to “watch out for”.  That would be a great idea for a presentation though.  The sessions I did attend, with the exception of DBA 101, were great though.  The PSS group was very helpful in answering questions, including an issue we were experiencing at work.  Orlando and the resort were unbelievable. 

Can't wait until next year.