As you sit and wonder about when the next Star Wars movie is going to come out, do you ever get the thought of “I wonder if all my SQL Servers are configured the same?”
Occasionally, I get a thought like that run through my mind. Or I might see something on Twitter or Blog post about something, and it sparks the question. Not about Star Wars, but my SQL Server environment.
Today something triggered me to confirm that all of my SQL Servers had the default backup compression setting set to enabled.
Finding Configuration Setting on Multiple Servers
I jumped into a trusty PowerShell session and ran the following command.
$servers = Get-DbaRegisteredServer -SqlInstance CMSServer -Group "SDLC" Get-DbaSpConfigure -SqlInstance $servers -Name DefaultBackupCompression | ogv
The first command gets a list of my servers from my Central Management Server in a -Group named SDLC. The next command runs the dbatools get function to find all configuration items with a -Name of DefaultBackupCompression, and that output is being piped to the
Out-GridView cmdlet. In the below screenshot, you see that my environment has five servers that do not have the default setting I was expecting.
Good thing all of these outliers are DBA servers. This is why settings are non-standard, and it is not crucial to keep them in line as no backups are taken.
Fixing configuration values
If I wanted to bring the SQL Servers up to a standard configuration to match the rest of my environment, I could run the command below for each SQL Server to set the configuration value to my desired state.
Set-DbaSpConfigure -SqlInstance Server01 -Name DefaultBackupCompression -Value 1
Conclusion
One good thing about this is that if you wanted to build daily reports to show this type of information, it would be straightforward. For example, I have a SQL Agent Job that runs several checks on important things each morning in my environment. So maybe one day, I can build a blog post about that topic. 
You could also look at the PowerShell module dbachecks. This module allows you to configure automated checks on different aspects of your SQL Server environment.
The post Quickly Identify Configuration Drift In Your Environment appeared first on GarryBargsley.com.