skreebydba

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.



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.





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.





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.








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.