Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Policy Management to Enforce Database Settings in Development


By:   |   Last Updated: 2010-07-15   |   Comments (1)   |   Related Tips: More > 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:

using SQL Server 2008 Policy management can evaluate your databases to ensure they meet a certain configuration

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:

By default, the policy is going to apply the condition against every database

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:

 using the Database facet, we'll specify the names of the databases we want to exclude

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:

evaluate the policy quickly to get to where you see which databases are not in compliance

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:

click the Apply button

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:

there are some cases where the policy will not be able to remediate in this manner

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:

 testing for whether or not the owner belongs to a particular login

Therefore, be careful of the settings you do choose if you want the policy to do the work for you.

Next Steps


Last Updated: 2010-07-15


next webcast button


next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, June 06, 2014 - 11:12:32 AM - David Shabat Back To Top

This is a great article.  The next step:  does anyone have code to obtain the "Exception Databases" via code?  I have a table that contains a list of objects, along with an integer to denote a particular exclusion.  For example, I can have data in my table that effectively contains lists of databases that carry a common policyexclusionID.  So, ID 1 might be "exclude from full backup check policy" and ID 2 might be "exclude from log backup check policy".  So, I would want to create a condition that says:  Check every database for current full backups except "select name from mytable where exclusionID = 1".  Does anyone have know-how to make that happen, fast?


Learn more about SQL Server tools