Category Archives: Database Settings

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

Posted on by

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.

SSMS Report Error – Index(zero based) must be greater than or equal to zero and less than the size of argument list !

Posted on by

SSMS reports are one of the best ways to pull some great deal of Server level or Database level information within SQLServer.

Today I was running the ‘Disk Usage’ report (This one is my favorite) for one of the database and immediately got this error!

Error – Index (zero based) must be greater than or equal to zero and less than the size of argument list!

Well that’s one really cool error message to get when you are seriously working on something important!

 

Immediately I checked to see if the same report is causing issues for other databases which were available on the same instance.

The report was working great for all other databases except one which gave the error.

All the 4 ‘Disk Usage’ reports where having issues for that particular database, and I decided to compare this database with one for which the report was running fine.

Eventually the culprit was found. The database was having compatibility level 80 (SQL 2000) and was residing on a SQL Server 2005 SP4 instance. SSMS which I used to run reports was of version 10.50.2500 (2008 R2 SP1).

There are multiple T-SQL statements which are ran to pull this report,and this can be tracked by running a SQL-Profiler(Again, with caution for a production environment)

Changing the compatibility to 90 fixed the issue and the reports were running as expected.

Note – Changing the compatibility needs to be reviewed properly with the application owners as it can bring unexpected results. Proper testing and approval needs to be taken.

Conclusion –

Yet another reason to find all the databases which are running with lower compatibility and push application owners to fix their code where ever its required and move forward with latest compatibility levels.

For SQL Server 2012 compatibility level 80 is not supported, hence you won’t face these type of compatibility related issues anymore.

Is there a way to find Historical Recovery Model changes on a database? Yes,you are lucky !

Posted on by

Today there was an interesting question posted on twitter, and that made me think about ways on how we can play around with SQL Server features/options to find solutions for tricky questions and requirements.

There are multiple ways to find this info, and the first and foremost thing which came on my mind was to read the error logs.

1. We can use sp_readerorrlog procedure to pull the details easily, however this details will going off when the error logs are recycled.

Sp_readerrorlog 0, 1, 'recovery'

The above method is great when you dont need huge amount of historical data and you are looking only for a recent specific time frame.

2. The second option is to use the default trace to find this info, however this is very tough as the recovery model changes are tracked under event class 164 which is the one for object_changes.There is no specific info which says the recovery model was changed.

A good post which leverage default trace + sp_readerorrlog + sp_enumerrorlogs is written by Simon Sabin(B/T) is available here.

In case you are using SQL 2008/above and would like to track this from now on then you also can leverage PBM(Policy Based Management) for this purpose.

The idea about PBM didn’t came to my mind early on, however it eventually came and I decided to test it to see if we can track historical info on recovery model changes.

A policy was created for all the databases with evaluation mode was ‘On Schedule’

The schedule was set for every 1 minute to ensure that multiple recovery model changes are evaluated and not missed.

The condition was also created as

The condition@RecoveryModel = Full is just one value for creating a condition, so that we can evaluate the policy.

Initially the database for this test case Adventureworks2012 was running Full recovery model and I changed it to Simple, Full and then Bulk Logged.

This means that I changed recovery model 3 times and currently its running on Bulk logged recovery model.

If we want to evaluate the policy manually we can do that too.if we opt to do a manual evaluation then we will get the evaluation details which will clearly says that database Adventureworks2012 is having a different recovery model than what is mentioned in the policy.



Our policy evaluation is running in the back ground to ensure that manual intervention is not required and we don’t miss any recovery model changes which might happen frequently (Multiple changes between 1 min can be missed out)


Note – Running schedules for every 1 min is not really recommended and should be used only if there is a specific requirement.


The tracking of recovery model changes comes in under the View History option available under Policy Management.




The first policy deviation was when the database was set as Simple recovery model.If we click the hyperlink under Detail section we will get the details of actual value and expected value.


The second policy evaluation success message is when the recovery model was changed to Full.


The third policy deviation was when the database was set as Bulk logged recovery model.


This way we can track the recovery model changes and we can disable/delete this policy once the requirement is completed.


The total number of days to retain this history for all policies is available too.It would have been great if we could retain history per policy,however that option is not available at this moment.




Conclusion 


As a hard and fast rule I definitely won’t allow anyone to change the database recovery model option. I can definitely enforce a policy to restrict it too, however as the requirement of the user was to track the changes I mentioned couple of ways to deploy it.


I am interested to know if you have dealt any similar situation like this. Feel free to pass on your experience as comments.


Thanks for reading.