Things are starting to feel “normal” again here (whatever that means, and it doesn’t mean much). I’m going to post something M would probably have enjoyed, or at least argued with me about — which amounted to enjoyment for both of us.
I’m trying to use a new-ish feature in SQL Server — Policy Based Management — and figuring out the best way to create my own policy condition. Conditions are a building block of policies, and SQL Server comes with a whole bunch in-the-box.
If you don’t see the default set of conditions, you haven’t installed the policies that reference conditions. When you Import Policies from the shipping set (check BOL for the location of these XML files in your SQL Server installation), they drag along the conditions they need.
But what if the conditions you need aren’t included? That’s when the fun begins. As usual in SQL Server, there are probably at least 5 ways to express the conditions you’ll want to check; what’s the best way?
I’ll walk through creating a condition here, and show you how I made a design decision when face with such a choice.
Setup/requirements
The example policy and its underlying conditions that I’ll use in this walkthrough aren’t available in-the-box, as far as I could tell. That’s probably because this example checks for a problem that’s incredibly stupid. Nobody should ever have it.
On the other hand, that doesn’t mean this is not a real (and realistic) example. This problem does happen in my environment and maybe it happens in yours, too.
So here we go.
The problem to be solved, aka the policy to be checked:
System Databases containing User Code
Yeah, it’s sad but true: there are several people who:
- have the rights to deploy code in my various SQL environments;
- deploy objects manually, and ;
- sometimes make mistakes.
Specifically, they sometimes deploy the object without setting the right database context first.
They usually figure it out right away when they try to reference the newly-deployed (or newly-updated) object, and it doesn’t do what they expect. They quickly re-deploy.
Meanwhile, some of these people have, or used to have, a default database of “Master”. After they’ve re-deployed, they’re often blissfully ignorant of why the first version didn’t show up in the correct database as expected, or just don’t care. The mistakenly-deployed version is still sitting around in the system database, which just bugs me.
I decided that I wanted to do a periodic check of all my environments of any code in any of the system databases that shouldn’t be there.
Using a Central Management Server, this is easy to do with a SQL statement that hits every server in a managed group. The statement to check the master database might look like this:
select @@SERVERNAME as ServerName, count(*) as NonMSCount
from master.sys.objects
where is_ms_shipped = 0
If you want to check all three system databases in one statement, you might write the following:
select @@SERVERNAME as ServerName, sum(NonMSCount) from
(select count(*) as NonMSCount from master.sys.objects
where is_ms_shipped = 0
union all
select count(*) from msdb.sys.objects
where is_ms_shipped = 0 and name not like ‘sys%’
union all
select count(*) from model.sys.objects
where is_ms_shipped = 0) xx
… don’t get too hung up on the actual SQL statements; I’m sure you can do it better. The highlighted item, for example, is just my lazy way of accounting for the fact that msdb.dbo.sysdtslog90 is installed on demand with SSIS therefore is_ms_shipped is not 0 — at least on my local box. Your mileage may vary.
The point here is that I am summing three numbers (a count of non-MS-provided database objects in each of the system databases). If the total is not zero, I’ll borrow from the immortal Ricky Ricardo and say “Lisa’s got some ‘splaining to do.” I’m not going to take any automated action, I’m not going to yell at anybody in a grumpy email alert, there may be a good reason for that user code… I just want to review it. OK?
The politics of Policies
Why bother turning this easy code into a Policy? Why not just issue the statement(s) you see above, manually?
It comes down to the advantage of declaratively, explicitly installing the policy into SQL Server (in this case, via a Central Management Server). I could run the script every once in a while, and it would have the same result.
I could even declare the intent in a Sharepoint page, where everybody would know the plan, assuming they read the Sharepoint page… which they will not.
But if I install the policy, with a suitable name, it will carry more weight with the relevant personnel.
Plus, it’s actually easier to retrieve and run.
I could do a similar thing by creating a DTSX package and running that at intervals, of course. It would also be very visible to the same personnel. I love SSIS. But, in this case, a DTSX would be overkill, harder to maintain, and not nearly as cool, besides.
Policies are a declarative way of exposing, instituting, evaluating, and managing best practices. Unlike SSIS, they don’t do anything else, they’re tuned for this.
So how do we get started?
Here’s the walkthrough
First, you create the conditions you want to check, and then you create the policy that references these conditions.
We have already designed the “conditions” we want to check in our policy, by writing the SQL statements above.
In typical SQL Server Management Studio style, drill down through the Object Explorer in the Management node, where you’ll find a Policy Management area. Opening this, you find Policies, Conditions, and Facets. Right-click on the Conditions node to start creating a new Condition, and give it a suitable name:
I’ve used the facet “Server” here, because it seems most appropriate to me. Think of this basically as the scope of the command I’m going to run. I need to run it once per server, not once per database on the server, right? Hold that thought for a moment.
Note: there are lots of other facets, such as Data File, Application Role, Server Settings, and they all seem disparate, but the same thought process is going to hold true. If you think of the facet as a collection of something over which you’re going to run something like a For each…, you’ll probably pick the right facet.
You’ll know if you’ve picked a really wrong facet for your purpose, in the next step, if the nested dialog doesn’t give you any attributes that make sense for the scope you had in mind. For example, if I pick the facet Remote Service Binding, I’m going to get an attribute called @CertificateUser and another one called @IsAnonymous. Keep holding that thought.
On the second panel of this dialog, you’ll give the condition a good description, I hope. Take a look at the MS-created ones; they’re complete with recommendations, URLs for further reference, etc.
In my screenshot, you can see this isn’t a complete condition yet, because I haven’t created an expression that represents what this condition is supposed to, well, express. That’s where our “several ways to do something” comes in. We know what we want to say; how are we going to say it?
Do what the dialog suggests; “Click here to add a clause”. What the heck are you supposed to do here ? The “Field” dropdown supplies attributes that belong to a Server (our chosen facet), but how do they relate to the SQL we’re using for this condition?
They don’t! And that’s okay. Press the ellipsis to the right of the Field dropdown to get to the following nested dialog:
… as you can see, we have the use of a function to execute the SQL statement(s) of our choice.
What should the condition be?
You have at least the following choices:
- use exactly the second SQL statement I created above, resulting in sum(NonMSCount), a single value (in other words, remove the ServerName column I added, which you won’t need and which will make the result of the statement ineligible for a policy condition; only a single value result is allowed).
In the condition, you’ll be comparing this value, using the = operator, with the expected value of 0 on the right side of the clause. - revise the SQL statement I created above to nest ExecuteSql statements in Add() functions, so that you can avoid the unions and the outer Sum(). The Advanced dialog is not exactly fun to type in; it’s a little masochistic.
However, this would work, and using the nested functions like this teaches you something about what you can actually do in this syntax, which is a lot, in other scenarios. Your revision would be:Add(
Add ( ExecuteSql( ‘Numeric’ , ‘select COUNT(*) from model.sys.objects
where is_ms_shipped = 0′),
ExecuteSql (‘Numeric’ , ‘select COUNT(*) from master.sys.objects
where is_ms_shipped = 0′)
),
ExecuteSql(‘Numeric’ , ‘select COUNT(*) from msdb.sys.objects
where is_ms_shipped = 0
and name not like ”’sys%”)
) - Break up the original SQL statement into three clauses, one for each system database, like this — I won’t repeat the three ExecuteSql expressions under each clause, because you just put each one of the three you see above into each clause, and omit the Add() functions that put them all together above. The three clauses look like this:
No matter which of the three you choose, once you have a fully-posable condition, you can reference it in a policy.
With any of these methods, once you’ve created (and saved) your condition, creating the policy based on it is simple:
Right-click on the Policies node in Object Explorer, to create a New Policy similarly to how you created a New Condition. For our purposes, you just have to choose the condition you created earlier as the correct one to reference for the new policy; nothing else is required and at this level, which method you used to create your conditionn is completely irrelevant:
So, what difference will it make which method you used to create the condition?
What’s the best practice here?
The first attempt I made, simply transferring my compound SQL statement into a single clause, was the easiest one for me to do. I just told the policy to do exactly what I’d been doing manually in SQL Management Studio. If I stopped there, however, I wouldn’t really be taking advantage of what policies have to offer in terms of process and management.
The second attempt I made, adding the nested Add() functions, doesn’t really provide any particular advantage. Working through that version (with lots of sidebars and messing about) simply satisfied me with regards to how flexible and useful the condition syntax would be, to move things out of ExecuteSQL when appropriate.
Why did I care about the available condition expression syntax? SQL is easiest for us SQL jocks, but ExecuteSQL conditions can’t be put in policies that need to execute on a schedule, rather than On Demand. Not a big deal in this case, but worth learning for later.
Both of these methods have the same weakness: if the policy check fails, you’re not going to know which system database has the errant object. You’re not even going to see anything much in the Evaluation Results dialog, because your Condition field expression is pretty convoluted.
By contrast, when you run a policy based on the third method, all you have to do is look at your results and you can see right away exactly which clause failed:
Here, we learn that making the condition building blocks more granular is a better practice. We’ll keep that in mind for the future.
One reason you might not want multiple clauses, for some conditions, is that, if one of the clauses can’t have an “automatic fix/apply” result, that will take away this type of resolution for all the clauses. In our example scenario, there’s no “automatic fix/apply” for any of our clauses, so there isn’t any problem.
Even more ways to muddy the waters
Is “more granular” always better?
You have additional choices, if you want, and if you change the the facet against which you apply the condition:
- One way to make the policy, and its referenced condition, more granular is to base the condition on the Database facet. When you create the policy using this condition, you have a chance to say against which target databases you want to use it :
As you can see, you can create a new condition to indicate the correct target databases on the server you’re evaluating. In this case, your new condition to filter the targets could use either the @Name of the database or the @IsSystemObject attribute of the database.
In this case, I didn’t feel that was a correct resolution. Remember: I had a slight exception in the check that I wanted to make for the msdb database; I couldn’t execute exactly the same code against each system database.
-
If you want to go all-out-granular, you could even choose to create different conditions, and execute a different policy, for facets aimed at the individual object types: one for Stored Procedure, one for User-Defined Types, etc. But, in my example scenario, declaring a separate policy for each object type would dilute the message my policy is trying to send: it doesn’t matter what the object type is, I don’t want the individuals in question to even think there is such a distinction. Object-level is really the wrong scope for my purposes. Not to mention, it would be a PITA for me to evaluate all these policies separately!
I hope this gives you some ideas about how you might use conditions you design yourself, and how to make some of the design decisions you’ll face.
If you’re already using policies, you’ve probably already figured all this out and don’t even realize it can be troublesome. But I can tell you, when starting out, I looked at available guidance and didn’t see what I was looking for.
Going further up and farther in
Using a SQL-based condition is not ideal, and may not take full advantage of what policies have to offer; you can’t use them to enforce any policy, not just this one, automatically. As you can see from the “Apply to targets” mechanism I’ve just discussed, and because the facets have lots of attributes and the policy condition expression syntax is quite rich, there may definitely be a way to define my condition/rule without using ExecuteSQL at all.
Maybe I’ll check that out some other time — although I suspect it would run slower, and, in this case, I don’t care about the restriction anyway.
Meanwhile, I would like Central Management Server queries and, possibly, policies attached to them, to be more flexible in their results. What do I mean by that? I think I’ll post a separate example showing you, after thinking it through a little more.
Double-meanwhile, you’ll doubtless let me know what you think.