What are you going to automate today with PowerShell?
I’m cheating a little bit in that this is something I did a couple weeks ago, but it was immensely helpful. I’d been working on building out a new instance to migrate our test databases onto, but the developers had an urgent need to do some testing in isolation so they “borrowed” that new instance. But we had an additional requirement – the configuration needed to match production as closely as possible, more than our current test instance. Of course, I reached for Powershell and dbatools.
I started with
Get-DbaSpConfigure to retrieve the settings available from
sp_configure as these were the most important to my comparison. I ran this against production as well as each of my test instances and saved the results of each to a variable. Because accessing my production instance requires either jumping through hoops or using SQL Authentication, I passed
-SqlCredential (get-credential -Message "Prod" -UserName MySQLLogin) so I’d be prompted for that password instead of using Windows Authentication.
My configurations saved for reference, I can now look at one of the objects returned to see which properties need to be compared:
ServerName : TEST1 ConfigName : AdHocDistributedQueriesEnabled DisplayName : Ad Hoc Distributed Queries Description : Enable or disable Ad Hoc Distributed Queries IsAdvanced : True IsDynamic : True MinValue : 0 MaxValue : 1 ConfiguredValue : 0 RunningValue : 0 DefaultValue : 0 IsRunningDefaultValue : True
Looks like I want to be checking out
ConfigName is the same name that you’d pass to
sp_configure. PowerShell comes with a handy function
Compare-Object which (you guessed it!) lets you compare two objects and reports the differences.
Hmm…that’s no good. I know there are differences between test and production – for one, production has about 24 times the amount of RAM test has. I took to the SQL Community Slack for help, and was reminded that
Compare-Object by default doesn’t do a “deep” comparison on
PSCustomObjects, so you have to specify which property(ies) you want compared. In this case,
RunningValue. So, passing both
Compare-Object (the former so that I’d know what was being compared), then sorting the output, I was able to readily see the differences.
The value corresponding to the left-pointing arrow is what came from the reference object, and the right-pointing arrow is the value from the difference object (which instance is the “reference” in this case isn’t terribly important, as long as you remember which is which). So
MaxServerMemory are both higher in production – which is expected.
If we really want to get crazy, we can even make this a one-liner. But I don’t recommend it.
Running this against my second test instance as well let me quickly deliver the news to the developers that the instances were configured as closely as possible, with any differences being limited to the hardware/environments they were in which is not something we were in a position to address.