Continuing from my previous post on [URL=http://blog.scalabilityexperts.com/?p=79]DMF[/URL], let’s look at several things you probably do today either manually or by writing some creative scripts or little apps to accomplish. We’ll start with the very basic stuff and then look at something a little more “interesting”.

Before we get started, there are a few DMF specific terms you need to be familiar with that are new in SQL Server 2008. Policies we all get (perhaps too well). Then there are targets. As the word implies, that’s what you want to apply your policies to. Targets can be a SQL Server instance, database, table, etc… Within the targets, you have different facets that you can apply one or more policies to. For instance a database target can have options (sp_db_options is deprecated btw), table, etc… You can set a naming policy for tables or set auto update stats on policy for the database. You might have noticed by now there’s a tree hierachy to the targets but we’re database guys, so we get this (or think we do). Anyway, other things to be aware of are conditions and execution modes. Conditions are just that; conditions you define for a facet. Execution modes determine how your policies are executed and enforced (e.g. passively as in log the transgression or actively where non-compliant activities are disallowed). This is just the elevator brief on the terms. Do look them up in BOL for detailed definitions and also for a few other related subjects not covered here.

Background:
Let’s say you’re new to the company/department (previous DBA just stopped showing up for work one day and won’t answer calls/emails, nobody knows why) and you’re the only “real” DBA in the company/department. You quickly discover that everyone and their pet iguanas know the SA password and you cannot change it or take it back because the developers have test harnesses with the password hardcoded and most if not all production apps use SA for one or more tasks. Well your boss wants you to clean up shop.

So your first few months are spent chasing after one performance issue after another along with the usuals like backups, reindexing, space allocations, etc… Somethings you script out and use SQL Agent to run periodically, others you write DDL triggers for. With servers running 2005 SP2, you even find time (between 2-5am) to write custom reports with colorful charts. Finally, after months of hard work and some help from a guy named Johnnie W. (the one with the blue ribbon, not the green one), you convinced your CIO to upgrade to SQL Server 2008. Now you’ve got some serious tools to work with….

Scenario 1: Database options policy
Starting small, you decide to set standard database options. You figured by setting auto stats management back on will solve 60% of performance tickets because someone had read somewhere that stats management can burn server resources and decided to turn it off in the interst of performance. Of course, that someone also doesn’t know what the stats are for and calls you regularly to complain about slow queries. And then you have 95% of the log files autogrowing at 10% because the default values have to be the best, right? Well, you’d rather create a big enough file and let it auto grow by 25MB increments. This should be enough for a start.

You fire up Management Studio and browse to Management in the object explorer. You then expand Facet and create the appropriate new conditions for the Database Option and Log File facets.  

Next, you create your first policy by right-clicking on Policies and select the conditions you want to apply. In this case, it’s the Database Options policy (use whatever name you assigned earlier). You’re even kind enough to provide an informational message in the description and a link to an intranet site for more information. You schedule this to run weekly because you know your team tends to get careless on Monday mornings before their second cup of coffee. When done, you repeat this for the Log Files condition.

All this is done easily via SSMS and the screenshots show a couple of the steps. Books Online documents this very clearly and even provides some examples. Do check it out when you get a chance.

Now, what is not documented are the actual commands executed to create the conditions and policies. The current cTP build doesn’t even have a “Script” button for you to script everything out like other tasks in SSMS. BOL doesn’t mention anything about what commands are used either. A simple trace reveals the use of system SPs in msdb such as msdb.dbo.sp_syspolicy_add_condition to create the condition. It takes in several parameters including name, description and an expression which is basically the condition you define but in XML-like format. Exporting the policies also yields a similar form. Not sure what the release plans are but according to Buck Woody (SQL Server PM who delivered a DMF session at SQLPASS), the product group does intend to provide scripting support so check out future CTP releases as they become available.

Regardless, this will take you a whole 2-3 minutes to create and maybe a few more to push out to your existing targets which can include new databases that will be created later. In less than 15 minutes after upgrading to Katmai, you’re done with chasing after databases every week to ensure they have the right database options. Not bad at all.

Though useful this is too easy a scenario. Next, we’ll look at a more complex example where you can have multiple conditions (including differentiating between 32-bit and 64-bit platforms) and policies that depend on other policies. 

 joe yong

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.

With every new release, you can generally classify new features and changes to existing features into 3 group:

• Things that require rearchitecture/major code change (including new code) to take advantage of
• Things that require some/minimal changes to take advantage of
• Things that you benefit from just by upgrading

While I was at the SQLPASS summit a couple of weeks back, most of the coversations on SQL Server 2008 were around the big and shiny features like Resource Governor (which really rocks btw), MOLAP enabled write-back, LINQ, DMF, backup compression, etc…. There are other features that didn’t get quite as much mention though they will likely help just about any deployment though the medium and large ones will likely benefit more.

I’ll be looking into some of these features, picking out the ones that require little or no change to an existing system to take advantage of - things you get just by upgrading.

One such feature is hot-add CPU. With SQL Server 2008, you now have the ability to add CPUs to the server without needing to bring the server down and restarting. This is takes advantage of some of the work done in 2005 where you can dynamically allocate/de-allocate existing CPUs (via affinity setting) without needing to restart the instance. With 2008, you can do this with nett new CPUs on the server. It doesn’t allow hot-remove but the need for removal is far lower than adding.

Customers who struggle with trying to balance between provisioniong a server that will serve them for as long as possible without taking it offline for upgrades vs. increasing resource utilization will love this feature. With hot-add memory already supported in SQL Server 2005, users can now provision a lightly populated server and increase RAM and/or CPU when they need to without incurring downtime. One clever default behaviour is that new CPUs added to the server will not automatically get used by SQL Server. You will need to run RECONFIGURE before SQL Server will start assigning work to the new CPU. This is a good thing because you might have added the new CPU

• for another instance in a multi-instance server - very common in consolidated environments (and this is a great feature for it)
• for a non-SQL Server workload on your machine - backup agents, management agents, etc…

Before you run out there and start plugging things into your server, do note that you need to have supported hardware and operating system else, you’ll be hitting that speed-dial for customer support in no time. Also, as of this time, this feature is only available on the Enterprise Edition of SQL Server 2008.

joe.