The more I use the dbatools PowerShell module, the more I like it. It really is a solid set of scripts that allow you to easily manage your SQL Servers via PowerShell. If you are not familiar with dbatools, I recommend you head over to my previous post, Getting Started With The dbatools PowerShell Module, for a quick intro on getting it set up.
At work, one of the things my team has been using dbatools on is a standard SQL Server configuration script that we will use on all new SQL Server installations. As part of this script, we wanted to use PowerShell to set the SQL Server settings that we would typically use sp_configure for, which is very easily done by using the Set-DbaSpConfigure command (you would use Get-DbaSpConfigure to view the configurations).
|
1 2 3 4 5 6 7 |
#Get the value for XPCmdShellEnabled Get-DbaSpConfigure -SqlInstance localhost -Name XPCmdShellEnabled #Set the value for XPCmdShellEnabled Set-DbaSpConfigure -SqlInstance localhost -Name XPCmdShellEnabled -Value 1 |
Pretty straight forward, right? The parameter “-SqlInstance” is the name of the SQL Server you want to modify, “-Name” is the name of the configuration you want to view or modify, and “-Value” is the value you want to set the configuration to. In the second example above, we would connect to “localhost” and set XPCmdShellEnabled = 1.
Now, I will say that one area where I find the dbatools documentation lacking is around the list of “-Name” configurations that you can use here. I finally found a list of properties in the Configuration Class documentation, and these seem to be the server properties that you can configure with Set-DbaSpConfigure. (I’m assuming. Every one I have tried so far has worked, and I haven’t seen any documentation to lead me to believe otherwise)
Configuring Individual SQL Servers
Here is a simple example of how we use Set-DbaSpConfigure instead of sp_configure to configure a single SQL Server.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
$SQLInstance = "localhost" #Use Your SQL Server Name ################################## ## Set backup compression as default. ######################################## write-host "Set backup compression" Set-DbaSpConfigure -SqlInstance $SQLInstance -Name 'DefaultBackupCompression' -Value 1 ################################## ## Enable remote dedicated admin connections. ######################################## write-host "Enable remote dedicated admin connections" Set-DbaSpConfigure -SqlInstance $SQLInstance -Name 'RemoteDacConnectionsEnabled' -Value 1 ################################## ## Set Cost Threshold For Parallelism. ######################################## write-host "Set Cost Threshold For Parallelism" Set-DbaSpConfigure -SqlInstance $SQLInstance -Name 'CostThresholdForParallelism' -Value 25 ################################## ## Set Optimize For Ad-hoc Workloads. ######################################## write-host "Set Optimize For Ad-hoc Workloads" Set-DbaSpConfigure -SqlInstance $SQLInstance -Name 'OptimizeAdhocWorkloads' -Value 1 |
This really isn’t much different than running a T-SQL script in Management Studio. I would typically run the above script in PowerShell ISE. One of the advantages of this is that you can run it on your local machine, you don’t have to remote into the server or connect to SSMS, and all you have to do is change the SQLInstance variable to run it against a different server. So, you could run it against Server1, then change the value of SQLInstance to Server2 and run it against it. But, where the real power comes in is when you realize you can run this on multiple servers simultaneously.
Configuring Multiple SQL Servers
To run our configuration script against multiple servers, we just need to make a few slight modifications. We start out by creating a server list instead of specifying a single server (duh!). The next part may seem a little odd if you’re not familiar with how PowerShell works. First, we connect to each of the servers in our list using Get-DbaSpConfigure and retrieve the values we want to change. Then we pass (also known as “piping” in PowerShell) that server name and configuration info into Set-DbaSpConfigure, which will connect to the server and set the configuration to the value specified.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$ServerList = ("server1", "server2") ################################## ## Set backup compression as default. ######################################## write-host "Set backup compression" Get-DbaSpConfigure -SqlInstance $ServerList -Name 'DefaultBackupCompression' | Set-DbaSpConfigure -Value 1 ################################## ## Enable remote dedicated admin connections. ######################################## write-host "Enable remote dedicated admin connections" Get-DbaSpConfigure -SqlInstance $ServerList -Name 'RemoteDacConnectionsEnabled' | Set-DbaSpConfigure -Value 1 |
So, in the above example dbatools will get the DefaultBackupCompression from both of the servers specified, then call Set-DbaSpConfigure to change those values to “1”. It will then do the same for the RemoteDacConnectionsEnabled setting.
I initially tried to use the server list with Set-DbaSpConfigure directly, but that didn’t work out like I expected, it was running the configuration update multiple times on each server instead of just once on each server. Looking through the documentation, they give an example of using Get-DbaSpConfigure and then piping the values into Set-DbaSpConfigure, so that’s what I did here and it seems to work pretty well.
Admittedly this is a very basic example, but I will be building off of it in more posts with real world examples of how I am using dbatools to enhance my day to day SQL Server administration.