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 to perform 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 in 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 database table. This gives you an opportunity 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 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 Powershell command window as an administrator.
Install-Module -Name dbachecks -Force -SkipPublisherCheck
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 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 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