Quickly Identify Configuration Drift In Your Environment2 min read

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.

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.


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.


One Reply to “Quickly Identify Configuration Drift In Your Environment2 min read

  1. Good afternoon Garry,

    I don’t want to derail this topic but came across a post you made on mssqltips.com talking about installing SSRS Report Server on a Azure SQL managed instance. I didn’t see a place where I could comment on that page so I found you here.

    My question is around an error I receive when following these steps around a “RECOVERY” option that Azure managed SQL instance do not seem to support.

    Searching the internet, some crowds say the answer is to create a local instance of SQL Server, build the DB there and migrate that to the managed instance. As you did not include these complexities in your guide I was curious if I was missing something.

    Any help would be greatly appreciated!

    System.Data.SqlClient.SqlException (0x80131904): Keyword or statement option ‘RECOVERY’ is not supported in this version of SQL Server.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource
    1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(SqlConnection conn, String script, ICommandWrapperFactory commandWrapper)
    at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
    at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
    Error Number:40517,State:1,Class:16
    ClientConnectionId before routing:4478084f-2599-423f-8b64-82339abd9257
    Routing Destination:f8d1bbff4660.tr630.eastus1-a.worker.database.windows.net,11020


Leave a Reply

Your email address will not be published. Required fields are marked *