Most new product releases come with a slew of new features along with some new ways of doing the same thing. Well, Katmai introduces a “somewhat” new way to manage your SQL Server systems. I say somewhat because the concept isn’t really new but it is now being supported by enhancements in the management tools. Also, in addition to changes in the tools, Microsoft has also come up with guidance on how you should manage your SQL Servers. Welcome to the Declarative Management Framework (DMF). Btw, there are at least a couple of places in the July CTP BOL that incorrectly calls it the “Dynamic” Management Framework; it really should be Declarative. At least that’s what it was called at the last major public event - PASS Summit 2007 (Denver).

So what’s DMF? In a nutshell, it’s an approach to database administration based on policies and not tasks. Many DBAs plan their work around the various tasks they need to perform which may include backing up databases, reviewing event logs, scanning for improper/unauthorized object creations, killing long running query SPIDs, etc… The list goes on for quite a bit (all the way to Christchurch my Kiwi friends say but my OZ buddies assure me there really isn’t much East of Wollongong). Bottom line is, there are a lot of tactical and sometimes reactionary items on the list which can keep the conscientious DBA busy pretty much all year around (not even counting the support calls).

With DMF, you’re suppose to determine what policies, restrictions, behaviours, etc…. the SQL Servers in your organization will enforce, define the policy using Management Studio and select one or more servers to enforce the policy. You then monitor from a central console; SSMS. The classic example (if you paid attention at TechEd or PASS Summit this year), is users are not allowed to create tables in the DBO schema. That’s handy but not particularly interesting. How about making sure all user created stored procedures begin with a USP_ prefix? Disallow the use of SQLMail? No OPENROWSET queries while we’re at it. Notice that you can define policies that have a database or a server scope? Pretty cool huh? Sure, you can change the relevant sp_configure settings for each server and db_options. You can even put all that in a script and run them against all the databases/servers in your org. Well, DMF and SSMS in Katmai makes all that a lot easier. It’s not reinventing the wheel, it’s just putting on some good tires.

Though not stated by Microsoft, it really looks to me that they are trying to drive the whole “manage by exception” philosophy, which I subscribe to. DBAs should not be repeating mundane tasks day after day. These can and should be automated. However, they should not operate in a blackbox either. The DBA should have full view and control over what’s going on and if an “exception” occurs, the DBA should be notified. Whether automatic corrective actions are taken or not, that depends on the nature of the event.

So what does that have to do with ugprading to Katmai? Well, it’s a classic example of a product upgrade that requires a DBA upgrade (Side-by-side is best). Sure, you can continue to manage things the old way but if you are working in a dynamic environment that continues to grow, you’ll soon find yourself overwhelmed (unless your management is kind enough to add new headcount your team every year, no?).

This is a pretty broad topic and an important one too given the number of companies that have more than a dozen SQL Server instances. So instead of writing another 5 pages on DMF and how it works with Management Studio, I’ll stop here with the intro.

Next up will be several scenarios with DMF and details on how they are implement.

Btw, if you are currently facing challenges managing multiple SQL Servers in your company, drop me a note and we’ll see if/how DMF and enhancements in Katmai SSMS helps.

joe yong.