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