Usually, right after building a SQL Server instance, it's in pretty good shape. You've configured settings like maxdop, cost threshold for parallelism, max memory, etc. You've likely set up your standard maintenance jobs and set up users and roles appropriately.
But let's say it's a year or two later, and you've been disengaged from that instance. Are the settings you applied still intact? Is the instance still in a good state? There's a good chance that databases have been moved from older instances to this one. How can you be sure they're using CHECKSUM for the page_verify option?
Multiply this scenario across 10s or 100s of instances, and it becomes overwhelming. You're often left scrambling when the auditors show up.
Today's challenge has two objectives.
- Verify that Policy-Based Management is enabled.
- Create two policies: SQL Server Max Degree of Parallelism and Database Page Verification.
Ready?
Estimated time to complete: Less than 1 hour.
Step 1: Verify that Policy-Based Management is enabled
Objective: Verify that PBM is enabled and review properties.
- Open SSMS and connect to your SQL Server instance.
- Expand Management, right-click on Policy Management, then click Properties.
Enabled - Specifies whether PBM is disabled or enabled.
HistoryRetentionDays - The number of days that policy evaluation history should be retained. If 0, the history will not be automatically removed. History is stored in the msdb database.
LogOnSuccess - Specifies whether PBM logs successful policy evaluations. When true, both successful and failed policy evaluations are logged.
Step 2: Create two policies
Last week, we covered the 4 key components of PBM: Facets, Conditions, Policies, and Targets
We'll use those components to create our first policy. Let's make the first one together. The second will be left up to you to create on your own.
- Right-click Policies and select New Policy.
- Enter "SQL Server Max Degree of Parallelism" as the name.
- Click on the Check Condition drop-down menu and select "New Condition."
- Give the condition a name. I named it "Maximum Degree of Parallelism."
- Select "Server Performance" from the Facet menu.
- Configure the expression as shown below and then click OK.
You can give the policy a description and assign it to a category. Leave the evaluation mode set to On Demand for now.
Evaluating the Policy
Next, right-click on the new policy and then click evaluate. If maxdop is set above 4, then the evaluation fails. To see the details, click View in the Target details box.
I have maxdop set to 8, so the evaluation fails.
Action Item:
Continue with creating the second policy - Database Page Verification. You'll need to identify which facet to use for your condition. Facets can be found, along with their description, within the Facet folder under Policy Management.
Helping Others and Sharing Your Results
If you're managing many SQL Server instances, creating policies one by one isn't scalable.
Next week, I'll introduce you to a framework that does scale.
If you have tips or experiences to share, please post them in the comments or tag me on LinkedIn with the #dbachallenges hashtag.
Next week, we'll create a policy using PBM.
Feedback
If there's a DBA Challenge you'd like to see, let me know by replying to this email.
Feel free to reply to this email with any thoughts or questions.
Good luck, and I look forward to seeing your results!
Luke
Responses