The Journey to Katmai - Part 1
I didn't get to all the sessions at TechEd. In fact, I only got to 3, mainly because I was leaving halfway through the conference, so I missed a few that were there. As a result, I don't have a complete look at the product, but I have the CTP and I heard about a few features. But Katmai is coming and I thought it made some sense to comment on some of the features.
Intellisense
It's coming!!!!
From the words of Dan Jones, manager of the manageability team. He said he wasn't sure on Powershell integration, but Intellisense is coming to Management Studio. No promises on performance, but it seems like it will work similar to the Red Gate SQLPrompt product.
From my talk with the people that built this at Red Gate, it's a hard problem to solve and there are some performance implications. A section of code has a limited number of methods you can call and the keywords are pretty well defined. In SQL Server you can have hundreds or thousands of objects, so you run into performance issues with memory.
I'd be sure that you have lots of memory in workstations that use Intellisense.
Policies
I'm a production guy and typically manage lots of servers when I'm working. So I like to see features that increase my scalability as we DBA. While I wasn't sure about policies at first, and how useful they could be, but after seeing Dan Jones talk about them, he brought out some interesting points.
The goal with policies was to make the DBA more scalable. By allowing you to define policies that cover virtually anything, you could enforce some standardization in your server. At first I was thinking this would be like the limited set of items similar to what exists in the SQL Server 2005 Surface Area Configuration tool, but in the demo from the June 2007 Katmai CTP, it is really an open ended, expression based dialog.
So if you want a policy that prevents changes to any of the password policies, you can do that. If you want to set a policy that sets maximum CPU resources for a particular type of user, you can do that. If you want to set a policy for ensuring all views end in "_vw", you can do that (I wouldn't do this one). With the ability to define and push out these policies, you could definitely ease the management burden.
You don't remove the need for a DBA or make it easy to set up the policy and define it, but you definitely allow one DBA to do the work of ten (or at least 2).
I think this is a much more mature, evolved version of the master/target system we have for jobs and I think it works well. We can set up different policies for different groups of servers and push them from one main server.
Resource Governor
Ever since I saw this appear in IIS, where you could limit the amount of CPU for a particular web site, I wanted it in SQL Server. Actually I wanted it before then, but at that point I knew Windows could support it. And since SQL Server has always tried to be ahead of Windows...why has it taken so long??!?!?
It doesn't really matter, but I think along with the DAC, this is a great new feature. It allows you to specify minimum and maximum amounts of memory and/or CPU that are useable by some group. The group can be an application, a user, role, etc. I'm looking forward to digging into my CTP more on this particular area.
By making these assignments, you can control, or limit, runaway queries and hopefully have a smoother running server. This should also go a long way towards consolidating databases onto the same instance, or running multiple instances on the same box.
BLOBs
One of the classic debates that I often see in the forums is the debate about where to store BLOB data. Do you put it in the database or put it in the file system with a "path" in the database? Both can work and both have problems.
Katmai recognizes this problem and part of the team has been looking to help you with it. One of the areas I thought was very interesting was the work on storing BLOB data with a mix of these technologies. The FILESTREAM type is built into SQL Server to allow you to use T-SQL to read and write data into your tables, but it gets stored in the file system as a separate filegroup. You designate a filegroup as a FILESTREAM type and then a combination of T-SQL and Win32 streaming APIs are used to work with the data.
I'm not explaining it well, but it's a pretty cool idea. I'd like to learn more about exactly how the data gets stuck in the file system (formatting, access, etc.), but it seems like a better solution than either of those above.
Katmai also implements a space for providers from third party BLOB servers, like EMC's Centera, to plug into SQL Server and have applications work with SQL Server that then sends the actual BLOB data to the other server. With the tremendous amount of new digital data that's unstructured and of varying types, like images and video, this is a cool addition to the platform.
Conclusion
This isn't intended to be a complete look at what's new, but it's a list of some of the features that I think are interesting or will have some impact in the world. There are definitely many more things (XML upgrades, large UDTs, filtered indexes, data compression, etc.), though a much smaller list of new features than what went into SQL Server 2005. I think it's a bit more than a point release, but they could easily have made this an R2 release instead of a new version.
If there are other things you think I've missed or want more information, make a note in the discussion and I'll see what I can do to get more information out there. Look for a few other features to be discussed in other articles.
If you're interested, some real-time blogs from TechEd:
