SQLCoffee - Policy Based Management

[Company Logo Image]

Policy Based Management

Policy Based Management - Enforcing Recovery Model Full

Applies: SQL Server 2008 (Katmai) CTP November 2007

Microsoft SQL Server 2008 introduces Declarative Management, which delivers the following benefits:

	Ensures compliance with policies for system configuration
	Monitors and prevents changes to the system by authoring policies against the configuration
	Reduces total cost of ownership by simplifying administration tasks
	Detects compliance issues in SQL Server Management Studio

Let's use the Declarative Management Framework to enforce a recovery model full on each database attached to an specific instance of SQL Server 2008:

1. In Object Explorer, expand Management, expand Policy Management and then expand Conditions.

2. Locate the condition named "Recovery Model Full", and double click on it to see how it was created, and then close the window.

3. In Object Explorer, right-click Policies, and then click New Policy.

4. In the Create New Policy dialog box, in the Name box, type DBA - Enforce Recovery Model Full.

5. Select the condition named "Recovery Model Full".

6. Specify how the policy will be executed. Select the execution mode "On schedule"

7. Create a new job schedule and click OK

8. Select the Enabled box and click OK.

As you will see, the procedure we just completed created a new job for SQL Server Agent

Now let's test our policy.

To make all databases comply with the policy, make a click on the "Configure" button.