Implementing Database Settings Using Policy Based Management

Introduction

Database Administrators have always
had a tough time to ensuring that all the SQL Servers administered by them are
configured according to the policies and standards of organization. Using SQL
Server’s  Policy Based Management feature DBAs can now manage one or more
instances of SQL Server 2008 and check for policy compliance issues. In this article we will utilize Policy Based Management (aka
Declarative Management Framework or DMF) feature of SQL Server to implement
and verify database settings on all production databases.

It is best practice to enforce the
below settings on each Production database. However,
it can be tedious to go through each database and then check whether the
below database settings are implemented across databases. In
this article I will explain it to you how to utilize the Policy Based Management Feature
of SQL Server 2008 to create a policy to verify these settings on
all databases and in cases of non-complaince how to bring them back into complaince.

Database setting to enforce on each user database :

  • Auto
    Close and Auto Shrink Properties of database set to False
  • Auto
    Create Statistics and Auto Update Statistics set to True
  • Compatibility
    Level of all the user database set as 100
  • Page
    Verify set as CHECKSUM
  • Recovery
    Model of all user database set to Full
  • Restrict
    Access set as MULTI_USER

Configure a Policy
to Verify Database Settings

1. Connect to SQL
Server 2008 Instance using SQL Server Management Studio

2. In the Object
Explorer, Click on Management > Policy Management and you
will be able to see Policies, Conditions & Facets as child nodes

3. Right click Policies
and then select New Policy…. from the drop down list as shown in the
snippet below to open the  Create New Policy Popup window.

4.
In the Create
New Policy
popup window you need to provide the name of the policy as “Implementing
and Verify Database Settings for Production Databases
” and then click the
drop down list under Check Condition. As highlighted in the snippet
below click on the New Condition… option to open up the Create New
Condition window.

5.
In the Create
New Condition
popup window you need to provide the name of the condition as
Verify and Change Database Settings”. In the Facet drop down
list you need to choose the Facet as Database Options as shown in
the snippet below. Under Expression you need to select Field value as @AutoClose
and then choose Operator value as ‘ = ‘ and finally choose Value
as False. Now that you have successfully added the first field you can
now go ahead and add rest of the fields as shown in the snippet below.

Once you have successfully added
all the above shown fields of Database Options Facet, click OK to
save the changes and to return to the parent Create New Policy –
Implementing and Verify Database Settings for Production Database windows

where you will see that the newly created condition “Verify and Change
Database Settings
” is selected by default.

Continues…

Pages: 1 2 3




Array

One Response to “Implementing Database Settings Using Policy Based Management”

  1. In step 7 you say – Since, we want to perform this check only against all the online user databases choose Online User Database option under Against target as shown in the snippet below.

    I do not get this option, all that is available to me is Every database. I do get other options if I try to set a different kind of policy. Do I need to add these options? If so, how do I do that? I can’t find where I can add the options you suggest.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |