PowerShell Pipeline
Checking SQL Best Practices Using the DBATools Module
With the help of this extremely useful PowerShell module, you'll be able to ensure that all your SQL-based servers are optimized.
The PowerShell community is a treasure trove of amazing modules and scripts that you can find online and use in your day-to-day operations. It doesn't matter the platform or the purpose, there is probably a script that you can use out there somewhere. In the case of SQL, this is definitely no different. One such module that has been developed by the community is called DBATools and is described as a best practices module with a lot of other useful commands also available. The module was started by Chrissy LeMaire (blog | twitter) and has had others in the community involved in making this an amazing module to have in your toolkit. They are actively accepting feedback as well as any bugs found and features that you would like to see in the module.
The first thing that we need to do is bring the module down to our system. If you are running PowerShell V5, you can use Install-Module to download and install the module from the PowerShell Gallery. Otherwise you can download the module from the GitHub page.
In my case, I am going to download and install the module from the gallery as it is the easiest thing to do.
Install-Module -Name DBATools
[Click on image for larger view.] Figure 1. .Installing the DBATools module.
Taking a look at the commands in the module, I can see that there are 95 commands. Quite a few to work with, but in this article, I will be solely focused on looking at the best practices commands that are available and see how my SQL server stacks up.
[Click on image for larger view.] Figure 2. Looking at the total number of commands in the module.
The best practices commands fall under the Test verb, so with that we can narrow down the scope of commands using Get-Command and specifying Test as our verb.
Get-Command -Module DBATools -Verb Test
CommandType Name Version Source
----------- ---- ------- ------
Alias Test-SqlDiskAllocation 0.8.61 dbatools
Function Test-DbaConnectionAuthScheme 0.8.61 dbatools
Function Test-DbaDatabaseCollation 0.8.61 dbatools
Function Test-DbaDatabaseCompatibility 0.8.61 dbatools
Function Test-DbaDatabaseOwner 0.8.61 dbatools
Function Test-DbaDiskAlignment 0.8.61 dbatools
Function Test-DbaDiskAllocation 0.8.61 dbatools
Function Test-DbaFullRecoveryModel 0.8.61 dbatools
Function Test-DbaJobOwner 0.8.61 dbatools
Function Test-DbaMaxDop 0.8.61 dbatools
Function Test-DbaPowerPlan 0.8.61 dbatools
Function Test-DbaServerName 0.8.61 dbatools
Function Test-DbaVirtualLogFile 0.8.61 dbatools
Function Test-SqlConnection 0.8.61 dbatools
Function Test-SqlMigrationConstraint 0.8.61 dbatools
Function Test-SqlNetworkLatency 0.8.61 dbatools
Function Test-SqlPath 0.8.61 dbatools
Function Test-SqlTempDbConfiguration 0.8.61 dbatools
Nice! There are a plenty of items here that we can look at and see how the results of the commands stack up with our SQL server. I think I will take a look at my databases and see if the owner matches up to the recommended best practices by using Test-DBADatabaseOwner and see what happens.
Test-DbaDatabaseOwner -SqlServer VSQL | Format-Table
[Click on image for larger view.] Figure 3. Displaying the databases that are not using best practices for ownership.
Something that I should mention before moving on with these findings, you will only be shown a few things with each command that is used, meaning that in this case, only the non-best practice items were shown. Whereas in other cases, only the information that is needed will be displayed. We can show more information with the commands by using the –Detailed parameter switch. Let's try that real fast to see what happens.
[Click on image for larger view.] Figure 4.A look at all of the databases.
As you can see, we have four extra items which are already best practices are now showing up. Something to keep in mind if you want to get more information.
Moving on with our original command, we see that there were three databases which do not conform with a best practice of specifying the account as owner of each database. Lucky for us, there is a command available within the module that will allow us to fix this finding.
Set-DbaDatabaseOwner -SqlServer VSQL
[Click on image for larger view.] Figure 5. Fixing the databases which are not conforming to best practices.
Now if we run Test-DBADatabaseOwner again, we will see that nothing is displayed showing us that we are no longer outside of the best practices for this particular area unless we specify the –Detailed parameter which will verify that everything is good by looking at the OwnerMatch property.
[Click on image for larger view.] Figure 6. Verifiying that the best practices are being met.
That was very cool to use and helped fix an area of this SQL server that wasn't quite up to the best practices approach that I am hoping to have with my systems. Let's take a look at another command and see what else we can find.
I think looking at the TempDB would be fun. I'll use the Test-SqlTempDBConfiguration command and specify the –Detailed parameter so I can not only what doesn't meet the best practices, but also what doesn't.
Test-SqlTempDbConfiguration -SqlServer VSQL -Detailed | Format-List
[Click on image for larger view.] Figure 7. Checking out the TempDB configuration.
The highlighted items represent areas where we are not meeting a best practice while the other areas show where we are meeting this. What I like here are the notes which provide more information about each best practice finding such as by providing the KB number in support of setting Trace Flag 1118 on your system so you can research if needed. Setting a Trace Flag means that I will have to set that up in the StartupParameters and restart the service. I think I will wait on that for now but go ahead and change the file growth so that it no longer uses a percent for the TempDB file. One last thing to note is that I do meet the file count limit as I only have a single core with my CPU on my virtual machine which is not that realistic in an enterprise environment, otherwise this would have definitely flagged as a non-best practice.
Wow! We have only scratched the surface of this module and have already found some key areas where we can update this SQL server to better meet the best practices design. With all of these commands, we have a great tool that we can use to create a report of all of our SQL servers to monitor to ensure that all of them are meeting the best practices as well as some sort of pre-production scan if new SQL servers are being deployed into a production environment.
Remember that you can find this module out on GitHub or on the PowerShell Gallery. Once you put this amazing module built by the community into use, you will be ensuring that all of your SQL servers are configured in a way that is a best practice.