In this article we will make use of Policy based management to check some of the basic but important properties of database. We will use policy based management to report if the specific database properties are changed. Some of the basic database properties that we monitor here are to database owner, recovery model of database, compatibility level, auto shrink and auto update statistics properties of database, etc.
Prior to SQL Server 2008, we use to write complex scripts and schedule jobs to get reports on these database properties and if some makes any changes to the scripts and it fails, again we have to spend time to fix the scripts and make sure we get the reports. Thanks to SQL Server 2008 policy based management, now their is no need to write scripts as most of the monitoring task can be done by using this very good feature.
Step as how to make use of policy based management to monitor these database properties and to report in case of violation:-
1) Connect to the database server and navigate to Management à Policy Management à Policies. Right click on Policies, thenclick on New Policy to start creating a Policy.
2) In ‘Create New Policy’ window give a relevant name to Policy like in example below its ‘Database Monitor Policy’. Then in the check condition click on New condition (if you have already created a condition you can either select the condition. But here we will create a new condition).
Click on ‘New Condition..’ New window will open.
3) Here type the name of new condition like ‘Database Monitoring Condition’ and then against Facet click on drop down and select Database. After selecting the database (next to Facet), start adding expression. In this case we will select the database properties fields that we need to monitor and get notified if the default values are changed.
@AutoShrink: – Auto Shrink of database is usually and preferably set to false. If the auto shrink property is set to true and total free space in database fall below the threshold value, SQL Server will start shrinking the database. In times this can happen during peak working hours and can cause blockings and performance issues. So as a best practice this option is always set to false.
@AutoUpdateStatisticsEnabled: – Auto update statistics option of database is set to true to make sure that the database statistics are always up to date. Although some time you may have to manually update the statistics also (even if this option is true) depending on the insert delete updates in the database.
@CompatibilityLevel: – To make full use of the new features available in SQL Server 2008, your database should be in SQL Server 2008 compatibility level i.e. 100. Although there are some applications that prefer to remain in previous version compatibility level to avoid costs linked to making necessary changes in code. What I believe is, there is no point moving to newer version if you are not willing to make use of the new features available.
@IsAccessible: – Is set to true to allow connections to database
@Owner: – As a best practice, owner of the database should always be ‘sa’ and not any individual user.
@ReadOnly: – If true then user cannot make any modifications to the database. (In the example I have considered that all database in production servers are set to read write mode. Although some exceptions might be there)
Now, after selecting all the fields and values click OK. You can select more or less fields as per your need.
4) Back to Create New Policy window. After selecting the newly created condition against ‘Check Condition’ option just makes sure that every option is checked if you want this policy for all databases.
Now, its time to select the evaluation mode for this policy. In this case you will have only two options either to run on demand or to schedule it via SQL Server agent job. Ideally there are four evaluation modes available for a policy but depending on the conditions that you have selected, these evaluation options will vary.
After selecting the Evaluation mode click OK. In this example we have selected the evaluation mode as ‘on demand’.
5) Now to evaluate the policy just navigate to Management -> Policy Management -> Policies. Just right click on the newly created policy and select Evaluate.
6) Once evaluation is complete, you will get the below screen with the evaluation results. For the databases where all properties are as per policy you will see green tick and for the once which deviate for the policy setting are marked by red cross.
To check the details of the policy results just click on View under Details column. A new screen will open with the expected values (policy settings) and the actual values. Thus it will help you to understand as which database properties need to be corrected to the expected values.
7) If you want to save the evaluation results, just click on the Export Results option. You can save this report in xml format and can view it later by opening it in any xml editor.
To Conclude, Policy based management is a very good feature introduced in SQL Server 2008. You can make use of these features to monitor/force policies as per your requirements. There are many more options available in Policy Based management that will help you to manage your database servers more easily and to enforce almost all the standards as per your company requirements.
