Archive for the ‘Policy-Based Management’ Category

Using Policy-Based Management to Update PAGE_VERIFY Settings – Part Two

August 2, 2013 2 comments

In my last post, I showed how to define a policy using Policy-Based Management (PBM). In this post, I will show how to evaluate this policy. While a policy can be evaluated against a single instance of SQL Server, PBM is especially powerful when used with Central Management Server. For more information about setting up Central Management Server, see my previous post on the topic.

5 - CMS

To begin evaluating your policy, open the Registered Servers tab in Management Studio and expand the Central Management Servers node. Right-click on the server group you want to evaluate and select New Policy. In the example, I will be using the 2005-2008 group.

6 - Evaluate Policies

In the Evaluate Policies dialog, click the lookup button next to the Source box.

7 - Select Source

Click the radio button next to Server and enter the name of the instance where you defined your policy. Click OK.

8 - Evaluate Policies 2

You should see your policy in the Policies list. To evaluate it, check the checkbox next to the policy and click Evaluate. Central Management Server will connect to each of the instances defined in the selected server group and will evaluate the policy, returning the results in the Target Details pane.

9 - Evaluate Policies Results

In my example, a number of databasesfailed the test. As a result, a red X is displayed next to the policy name in the Results pane. The Target Details pane will show the status for each database. I've obscured the results, to protect the innocent (and my job), but the Server column contains the name of the instance connected to and the Target column contains the following: SQLSERVER:\SQL\<Machine Name>\<Instance Name>\Database\<Database Name>.

A - Evaluate Plices Result Detail

Clicking the View link next to a failed database will show you the reason for the failure. In this case, the PAGE_VERIFY setting for the database is set to NONE. That, in technical terms, is real bad! Fortunately, Policy-Based Management gives us an easy method of fixing all databases in violation of the policy.

B - Apply Policy Changes

To apply the policy against all databases in violation, check the checkbox next to the policy name in the Results pane. This will select all of the databases in violation. If you want to apply the policy to a subset of the violators, you can select those manually in the Target Details pane. Once you've selected the databases you want to apply the policy to, click the Apply button.

C - Policy Apply Warning

Management Studio warns you that you are going to make changes to the databases in violation of the policy. If you are sure that this is what you want to do, click Yes.

D - Policy Applied

Central Management Server will connect to the instances containing databases in violation of the policy and change the PAGE_VERIFY setting to CHECKSUM. When it completes, you should see a green check mark next to the policy name in the Results pane, as well as green check marks next to all databases in the detail pane. If so, congratulations, you've successfully evaluated and applied your policy and PAGE_VERIFY is set to CHECKSUM for all databases.

These posts have demonstrated how to define and evaluate a Policy-Based Management policy with a single condition. There are many other facets and conditions that can be evaluated. You can get more information on Policy-Based Management at Books Online. And if you are really interested in learning more, I recommend Pro SQL Server 2008 Policy-Based Management.

Policy-Based Management provides a powerful tool for evaluating and standardizing your environment. I hope this introduction is helpful to you.

Using Policy-Based Management to Update PAGE_VERIFY Settings – Part One

July 31, 2013 Leave a comment

After my last post on using Dynamic SQL, I tweaked the script to check for databases with a PAGE_VERIFY setting other than CHECKSUM. If you are running SQL Server 2005 or higher, CHECKSUM should be set for all of your databases. With PAGE_VERIFY set to CHECKSUM, when SQL Server writes a dirty page back to disk, it first calculates a checksum value for the contents of the page and stores the value in the page header. When the page is read back into memory, the checksum is recalculated and compared with the values in the page header. If they are different, you have corruption. This is the best method for detecting corruption caused by the IO subsystem.

If you want a second opinion, here is Microsoft's recommendation. If you need a third opinion, here's Paul Randal's take on the subject.

To determine which databases have PAGE_VERIFY values other than CHECKSUM, you could query sys,databases and change the offenders manually. Or you could write a dynamic SQL script like the one from my previous post to identify the right databases and update them. I was planning on posting the script that I wrote to do just that, and then I remembered Policy-Based Management. Policy-Based Management (PBM) first shipped with SQL Server 2008 and is available in 2008 R2 and 2012. PBM allows you to define policies for your environment, audit your environment for adherence to those policies and make changes to ensure the environment is compliant. Policies can be run against SQL Server 2005 instances, but some functionality is not available. You can get more detailed information about PBM from Books Online.

Create a New Policy

For this example, I will set up a policy to check the PAGE_VERIFY setting for each user database in my environment. To start, expand the Management node in Object Explorer, followed by the Policy Management node. Right-click on Policies and select New Policy.

1 - Define Policy

Enter a name for your policy in the Name field of the Create New Policy dialog. I've called mine PageVerify. Next click the Check Condition dropdown and select New Condition.

1 - Define Condition

Enter a name for your condition and click the Facet dropdown, selecting Database Options. Click the lookup button next to the Field text box and select @PageVerify. Click in the Operator text box and select the equals sign. Finally, click the Value dropdown and select CHECKSUM. Your dialog should look like the one below. Click the OK button and you will return to the Create New Policy dialog.

3 - Set Policy

4 - Policy Defines

From here, you can set the policy to run against a subset of databases or servers. You also have the opportunity to set the Execution Policy. For this example, I will leave it set to On Demand. The other options for this example are On Schedule and On Change: Log Only. A fourth option, On Change: Prevent is available for some policies. It will roll back a change that violates the policy and output an error message to the user. Click okay, and you've created your first policy.

I will cover evaluating this policy in my next post.