Like any good DBA, I’ve got a checklist I go through when I get a new SQL Server. I’ve got standard jobs I install, standard configurations for the model database to set, steps to take to add the new server into my automated monitoring jobs, etc. The more experience I get, it seems the longer the list gets. Anything I can do to reduce the number of steps that have to be manually performed is welcomed.
If your company uses Windows Group Policy to manage computers, you have a powerful tool to help configure your SQL servers. If you do use Group Policies, odds are your SQL Servers are all in one group and any new servers that come online should be added to this group by your Windows admins. The benefit here is that settings in the Group Policy will automatically get applied to all the servers in that group – including any new ones. This means if you set your policies up correctly, you have fewer things to manually set.
I have asked my Windows admin to configure four items in our SQL Server Group Policy:
- Power Settings. By default, Windows installs with a Power Setting of Balanced. Our policy changes this to High Performance.
- Add our SQL Server service account to the Performance Monitor Users local security group. This allows me to include the server in my Perfmon monitoring routine.
- Grant our SQL Server service account the Lock Pages In Memory right. This is something of a controversial topic, but, in my shop, it makes sense for us to set this as a default.
- Grant our SQL Server service account the Perform Volume Maintenance Tasks right. This allows SQL Server to use Instant File Initialization.
By including these settings in Windows Group Policy, I can ensure that they are always set on all my SQL Servers. Set it and forget it!