DBAChecks - a tool for SQL Server DBAs

I recently went to a meeting of the very active SQL Ireland User Group. The meeting was organized by Bob Duffy and the main guest and the expert who presented was the one and only Cloud and Data Centre MVP Rob Sewell.

Rob is one of the creators of the very impressive set of Powershell scripts called dbachecks. This collection of scripts allows for performing a variety of database and system health checks. The version I installed on my machine contains 95 checks! The great thing about this toolset is that it is open source and available on github - so you can look inside and contribute if you find something missing or off.

Rob showed us various how easy it is to set up an automated solution that will perform validations of the environments and give you results in a very short time. Time is critical for DBAs, especially those who manage large numbers of environments. The solution can be plugged into a CI/CD system, or executed directly by a scheduler like SQL Agent. Since these are Powershell scripts, their results can be redirected to any data destination, including a database table. This allows you to build a BI reporting solution around your checks, and trace the health of your systems over time.

Powershell has been a Windows-only option for a long time, but since the introduction of the .Net Core framework, it is also available on Linux - so you can run the database checks on a Linux machine too. 

After the meeting, I decided that I will try to play with the dbachecks as well.

Installation of the dbachecks is relatively straightforward, however, on Windows 10 you may need to get over a few little obstacles.

Open the Powershell command window as an administrator.

Type

This should install the toolset. In my case, when I executed the Get-DBcCheck command, I encountered an error:

As the error message suggests

PS C:\WINDOWS\system32> Get-DBcCheck
Get-DBcCheck : The 'Get-DBcCheck' command was found in the module 'dbachecks', but the module could not be loaded. For
more information, run 'Import-Module dbachecks'.
At line:1 char:1
+ Get-DBcCheck
+ ~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Get-DBcCheck:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CouldNotAutoloadMatchingModule

I ran the command, and it showed more detailed information about the problem:

PS C:\WINDOWS\system32> Import-Module dbachecks
Import-Module : File C:\Program Files\WindowsPowerShell\Modules\Pester\4.4.2\Pester.psm1 cannot be loaded because
running scripts is disabled on this system. For more information, see about_Execution_Policies at
https:/go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:1
+ Import-Module dbachecks
+ ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : SecurityError: (:) [Import-Module], PSSecurityException
    + FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand 

The issue is caused by the insufficient script execution rights that you can check using the command 

In my case all execution policies were undefined.

 The solution is straightforward, as documented on the Microsoft documentation page. Just run Set-ExecutionPolicy to set the desired level. In my case, I executed the following:

Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

And a quick check verified that the policy has been set:

PS C:\WINDOWS\system32> Get-ExecutionPolicy -List

        Scope ExecutionPolicy
        ----- ---------------
MachinePolicy       Undefined
   UserPolicy       Undefined
      Process       Undefined
  CurrentUser    RemoteSigned
 LocalMachine       Undefined

Now I can run the Get-DBcCheck command to see what goodies are in the store.

For examples 

Enjoy!

For more information about how to use dbachecks and what validations are available, please visit Rob's blog.

You can also dive straight into the code if you feel like it on github.

PS: If you look for an icon, look no further than to Nline Web Fonts