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…
