SQL Server Policies for SQLOpsDB

The SQL Server policies are part of collection from Microsoft default best practice policies plus additional policies I have written over the year.

The policies typical follow the naming convention of rule check, if the policy passes, means your configuration is correct. If rule fails, an update is required.

I will not belong about how to write policies, but if you need assistance please reach out. I would advice you to make sure policies are limited to SQL checks, I have found checking event logs, WMI calls, etc. can be very expensive.

How to deploy policies?

  1. Connect to Central Management Server (CMS).
  2. Go to Management.
  3. Go to Policy Management.
  4. Go to Policies > Right Click on Policies.
  5. Select Import Policies.
  6. In the Dialogbox Open, click on the “…” to navigate to working directory where policies extracted (e.g. C:\Temp\SQLOpDB_Solution_V3.00.00.0000\SQL Policies\). Select all the XML files in one go.
  7. Select “Replace duplicate with item imported”.
  8. Click OK.

Once imported you should see all the policies and their respective conditions loaded. Note all policies default to disable and should remain disabled.

The policies are broken into five categories by default. Therefore any new policy must be assigned to these categories for it to be evaluated.

  • Maintenance
  • Security
  • Database Configuration
  • Server Configuration
  • Performance [currently not evaluated]

If you wish to modify the categories, introduce new ones, or enable Performance category. You will need to update the “C:\SQLOpsDB\PowerShell\EPM\EPMExecution.ps1” PowerShell script.

Copy line #25 and paste it below, updating the “PolicyCategoryFilter” value to “Performance”.

No environment is perfect, nor they can follow 100% of the best practice configurations.

There is no exception to the rule that every rule has an exception.

James Thurber

You can modify the policies for every exception. However, on large environments you will find this a very tedious and difficult task. If you do modify the policies, it will lower the data collection. However, I find the effort is not worth it.

Therefore, my solution reports against policies by checking custom table (Policy.PolicyExclusion). There is no PowerShell script for this. For each policy, server instance, or object you do not want reported in dashboard. You can create a custom exclusion.

Examples

“Auto Create Statistics is Disabled” or “Auto Update Statistics is Disabled”. These policies are valid in 90% of the cases. If the SQL Server instance or database is for SharePoint. These policies are invalid. There is no easy way to identify all SharePoint databases.

Therefore, we can create an exclusion for the SQL Instance where all SharePoint databases might be deployed.

  INSERT
    INTO Policy.PolicyExclusions (Policy_ID, EvaluatedServer, ObjectName, ReasonForExclusion)
  VALUES (20, 'SharePoint\Inst01', '%', 'SharePoint defaults to Auto Create Stats OFF')

  INSERT
    INTO Policy.PolicyExclusions (Policy_ID, EvaluatedServer, ObjectName, ReasonForExclusion)
  VALUES (21, 'SharePoint\Inst01', '%', 'SharePoint defaults to Auto Updates Stats OFF')

Currently only available via T-SQL. Your policy ID might be different, review msdb.dbo.syspolicy_policies for actual ID values.

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.