By: K. Brian Kelley | Updated: 2010-07-15 | Comments (1) | Related: > Policy Based Management
Problem
We have a fairly active development SQL Server with databases getting created or restored quite frequently. There are certain settings we wish to enforce, but none of us want to walk through each database by hand to determine if the databases are meeting the appropriate settings. We're using SQL Server 2008 and I've heard policy management can help. What do I need to do to use it to enforce my settings? I'm looking at settings like who is the owner, is the recovery model set to simple, is autoclose and autoshrink disabled and the like.
Solution
Policy management can indeed evaluate your databases to ensure they meet a certain configuration. However, depending on what you're trying to do with it will determine whether or not it can:
- Evaluate the settings and tell you what's wrong (which you'll have to fix manually)
- Evaluate the settings and give you the option of letting it fix what's wrong
- Enforce the settings to prevent them from being changed in the first place.
For what you've specified, you can do the second of these options. A previous tip describes the basics of using Policy Management, so if you're not familiar with how to set up a policy, be sure to read it first. It begins to show how to set the conditions you're looking for, so let's expand upon it. You'll need to create a condition which specifies the Database facet. Applicable settings are shown in Figure 1.
Figure 1:
In figure 1 I've specified the following conditions:
- Autoclose should be set to false (and thus disabled).
- Autoshrink should be set to false.
- Statistics will be created and updated automatically.
- The database will be in the simple recovery model.
And once we click OK we'll have our condition created. When you're selecting the conditions, you'll need to see the effect. Some settings don't let you apply corrections. We'll see an example a bit later.
Now it may be that you need to make an exception on a database or two. By default, the policy is going to apply the condition against every database (except system databases). Because we know that there will potentially be exceptions, let's go ahead and set up a filter. You can do this by clicking the Every for the Against targets section. This leads to a pop-up menu. We'll want to specify a New condition, such as shown in Figure 2.
Figure 2:
Again using the Database facet, we'll specify the names (Using the @Name for the field) of the databases we want to exclude. A good example of a reason to exclude a database is if you need to turn on Full or Bulk-Logged recovery for it. In Figure 3, we've excluded 2 databases, named Test1 and Test3. All other user databases will be evaluated.
Figure 3:
Once the condition is specified for the targets, you'll have to set the evaluation mode for the policy. For this policy, because we're dealing with database settings, we can only schedule the policy or run it on demand. If you're intending to run it every so often, then set it to On Demand. However, if you set it to On Schedule, you could set it to run in the morning hours before you get to work, and then you just need to check the history of the policy. If you see an exception, you know there's either a database to correct or a database to add to the filter condition for the targets.
In either case, if you do see there are issues, it's easy to remediate, at least with these settings. In the case of an On Schedule one, you'll need to evaluate the policy quickly to get to where you see which databases are not in compliance, such as in Figure 4.
Figure 4:
If you only want to correct individual databases, in the Target details, simply click the checkbox beside the appropriate databases. If you want to take care of them all, you can simply click the checkbox beside the policy itself, like in Figure 5. Then click the Apply button. The databases will be brought into compliance.
Figure 5:
Now there are some cases where the policy will not be able to remediate in this manner and you'll have to go and apply settings manually. An example is if you modify the condition to include an owner. Such as in Figure 6.
Figure 6:
Here we're testing for whether or not the owner belongs to a particular login. In this case I'm testing to see whether or not the database is owned by sa. In Figure 7, we'll see that there are databases which fail the policy, but note that there is no checkbox to allow the policy to fix the settings which aren't in compliance. You'll have to correct the settings manually.
Figure 7:
Therefore, be careful of the settings you do choose if you want the policy to do the work for you.
Next Steps
- Read these other tips related to Policy Based Management
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-07-15
