May 11, 2021 at 10:34 am
Hi,
My name is Radek and I just started my first dba job.
Currently, in my new company, there is not Policy-Based Management implemented. As one of the first tasks, I have been asked to draft and implement policies that we will be using in the future. Unfortunately, I have never worked with that tool and I am not sure what is the best way to approach that task. It is quite easy to google how to set up new policies, but I cannot find the list of good practices which actually should be considered/implemented within Policy-Based Management.
I have found/figured out a few of them:
Check if we have a current backup
Check if backups are on the same drive as data files
Check if the recovery model is set to simple at dev environments
Check if auto shrink is set to off
But I wonder (I am sure that there is, but I cannot find such thing) is there an recognized by community list of policies/good practices that should be enforced. Could you please help me to find such a list, or advise how to approach Policy-Based Management from a scratch? Could you please advise also if there are any known good practices related to that tool? (maybe it is not efficient to have more than n polices implemented, or exactly opposite we should add there everything which can be standardized)
Thanks,
Radek
May 11, 2021 at 11:31 am
So, I really love Policy Based Management. However, you need to know, Microsoft doesn't share my feelings. They have given that technology almost zero attention since it was originally released. While I like it, I would advise you not to pursue it as a mechanism for managing your systems. It's somewhat brittle and subject to outage & error. It's a shame too. It had so much potential. I'm not sure why they pulled back from it after the initial release, but they did.
As such, few people have experimented with it. There is no real community around it. There isn't much in the way of suggestions on how best to implement it, because, frankly, very few people have implemented it.
Sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2021 at 11:36 am
I would check out things like DBAChecks powershell module which has something like 136 different inbuilt checks to see things like max memory set, tempdb setup correctly, backups taken, duplicate/disabled indexes, latest patches etc to ensure best practise is configured correctly. All completely customisable to meet your needs.
My best practise to your best practise will be slightly different as some of it depends on workload, your workload may only need a couple of tempdb data files to elevate the contention, I may need more, same with things like MAXDOP and Cost for parallelism all that sort of stuff is subjective to workload
But DBAChecks will put you in a good starting point to go fixing things which are deemed as outside the usual community guidelines but may be perfectly valid for your environment, so it will take some tweaking.
May 11, 2021 at 11:47 am
Yeah, and there are also a ton of checks built into DBATools. So that might be worth looking at. There are also 3rd party solutions. For example, my company, Redgate Software, has a monitoring tool that also looks at your queries to identify common code smells.
I still wish PBM had received appropriate love from Microsoft though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2021 at 10:37 pm
Thanks guys... It is not exactly what I was hoping for, but really helpful.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply