The problem.
I’m sure you already know the dbatools published at https://dbatools.io/ ; after a great talk by Björn Peters I started playing around with that tools and they are really great to get your maintenance tasks done.
While saying that, when having another talk with Björn about running your SQL Server VMs in Azure we came to the point to reset some of the settings like max degree of parallelism dynamically. While dbatools allow you to run a script once to configure that settings they do not allow to adjust that dynamically.
This is where PowerShell Desired State configuration comes into play to dynamically resize your SQL Server configuration settings.
Solve it by using DSC.
Please find a set of DSC resources in the MSSQLConfigure module on GitHub ; the module is currently in ist initial state, so do not expect it to be pretty perfect. But it’s working. For example, you can easily configure a dynamic maximum degree of parallelism like shown in the screenshot.
So you can easily resize your server running in Azure, AWS or on your on-premise data center.
Which DSC resources are currently available?
In the initial version, only maxDOP is available. While saying that during the next weeks we plan to implement resources to configure all the options as listed in the article Server Configuration Options (SQL Server). We plan to achieve this until the end of September.
In case you think we should focus on some special options first, let Martin know by adding a comment to this post below.