Leverage Policy Based Management for regular Page Verify =Checksum compliance monitoring


Page Verify = Checksum is one option which I like all my servers to be compliant. By default all the new databases which gets created will have page verify = checksum as this option is inherited from the model database.

NOTE – APPLICABLE FROM SQL2005 AND ABOVE.

SELECT name,page_verify_option_desc FROM sys.databases
WHERE database_id=3

name                                page_verify_option_desc
=======                         ==================
model                               CHECKSUM

So far so good. But what will happen if we restore a database from SQL 2000 or even restore a database which is having a different page verify option other than checksum.

Lets check that out -

For the demo purpose on Server A we already have a database named Check_Page_Verify which is having a different page_verify option set as NONE(Bad idea !).

****Server A****

--Check the page_verify option of the db
SELECT name,page_verify_option_desc FROM sys.databases
WHERE database_id=6

name                         page_verify_option_desc
=====                       ==================
Check_PageVerify     NONE

We will back this database up and will restore it in Server B.

****Server A****

--Backup the database
BACKUP DATABASE Check_PageVerify
TO DISK='I:\Check_PageVerify.bak'
****Server B****

--RESTORE the database
RESTORE DATABASE Check_PageVerify
FROM DISK='I:\Check_PageVerify.bak'
WITH MOVE 'Check_PageVerify' TO 'D:\Check_PageVerify.mdf',
MOVE 'Check_PageVerify_log' TO 'D:\Check_PageVerify_log.LDF'

“Data file and log file in the same drive ! (Bad idea),however as this is a demo we can ignore this.”

In Server B lets check the page_verify option

****Server B****

--Check the page_verify option of the db
SELECT name,page_verify_option_desc FROM sys.databases
WHERE database_id=6

name                              page_verify_option_desc
=====                            ==================
Check_PageVerify          NONE

I started this post by saying Page Verify = Checksum is one option which I like all my servers to be compliant. Now after this restore my server is not complaint anymore for page verify. I will never come to know about this miss until and unless I regularly do compliance checks.

So how do we do that compliance checks ? This is where Policy Based Management (PBM) comes into picture.
We will need to create a condition first using the wizard.

PBM PageVerify1

I’m using facet ‘Database options’ for this demo and added the expression @pageverify= checksum.

Now, lets create a policy for this condition.

PBM PageVerify2

When creating the policy we will need to choose the condition which we created earlier. The evaluation mode for this policy are of 3 types

1. On demand 2.On schedule and 3.On change Log only.

To know more about PBM concepts please ref http://technet.microsoft.com/en-us/library/bb510667.aspx 

We are picking On schedule for this demo. This option needs a schedule to be created. We will create a schedule which will run at 12 AM every Sunday.

PBM PageVerify3

Once the schedule is created we need to ensure that policy is enabled.

PBM PageVerify4

The idea is to have the schedule run every Sunday to check if there are any databases which are violating the rule of page verify = checksum and on Monday morning when we come to work we will just need to check history of the policy to see if there are any violations.

For the demo purpose, lets trigger the schedule to run manually.

PBM PageVerify5

This is the job which was created automatically for the schedule which we created earlier.

After running the job successfully, all we need to do is check the history of the policy

PBM PageVerify6

There you go, we got the first violation details of the policy

PBM PageVerify7

It clearly says database Check_PageVerify is not compliant for the policy which we created.

If you have multiple databases which are not compliant, then you have the option to evaluate the policy manually ,choose the one which are not complaint and force them to be complaint.

PBM PageVerify8

PBM PageVerify9

PBM PageVerify10

PBM PageVerify11

Great, now we have everything under control and all the databases are compliant.

Conclusion

There are great things which we can do with PBM and if you have a better way to achieve this using PBM(Alternate facets,condition etc) or any other methods, then I’m eager to hear from you.

Keep watching this space for more.

Thanks for reading.

About these ads

6 thoughts on “Leverage Policy Based Management for regular Page Verify =Checksum compliance monitoring

  1. Pingback: Quick alert on servers which are not compliant for policies set using PBM | SQLSailor

  2. Ill right away grasp your rss as I can’t in finding your e-mail subscription hyperlink or e-newsletter service. Do you’ve any? Kindly let me recognise in order that I may subscribe. Thanks

  3. Hey! I just wanted to ask if you ever have any issues with hackers? My last blog (wordpress) was hacked and I ended up losing a few months of hard work due to no back up. Do you have any methods to protect against hackers?

    • Your question made me think about this and I found that you can backup stuff using plugins and also there is an export option which will put everything into an XML and you can import that back if needed.

      I really dont have any other methods to protect from hackers.

  4. Hi SQLSailor,

    I’ve come across your post about Database and setting CHECKSUM whilst looking for specific info on the intricacies of upgrading DBs from Torn Page Detection or no detection.

    You mention ‘…But what will happen if we restore a database from SQL 2000 or even restore a database which is having a different page verify option other than checksum…’ and then you go on to talk about looking for violations of a policy you set up to inform you of those that are not set to CHECKSUM.

    One thing that I think would be worth mentioning is that just switching a DB to CHECKSUM, say, as part of an upgrade, does not mean your DB and its pages are now covered by CHECKSUM. As Paul Randal has blogged (http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1730-page-checksums/), even if you switch a DB to CHECKSUM, the data pages will not be covered by CHECKSUM until they have been read into memory, altered, and written back to disc. Then, and only then, will your DBs data page(s) have a CHECKSUM set for it. That is…every page needs to be altered and written to disc again to be truely covered by CHECKSUM.

    And this is why I’m researching…as I have several hundred DBs to upgrade and I want to see what options I have available to achieve this marking of CHECKSUM for every data page in the DBs, in a controlled and guaranteed manner. One possible option is to encrypt and decrypt DBs, as this should touch every page in the DB during this operation – too much testing over and above the work already going on to consider leaving the DBs encrypted. I’m still looking for other possible options.

    Good write-up, though and hope this is helpful.

    • Thanks for the feedback. Yes,you are absolutely correct.Changing the option to checksum doesn’t mean that you are completely safe. Let me know if you find an option to mark every data pages for a database,I’m interested to hear about that.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s