Quick and Dirty Powershell to Deploy SQLWatch using DBATools

General
1

Hi

I wanted to share a small Q&D PowerShell script to deploy SQLWatch, Maintenance Solution and WhoIsActive.

Requirements: DbaTools Powershell Module

Install-Module dbatools

Know issues:
If SQLWatch already exist, the SQLWatch import fail due to the Database Drift from its registered data-tier application - solution to overcome the problem is in the documentation.

=============

$sqlinstance="MyServer\MyInstance"

$SQLWatchDB="SQLWATCH"
$SQLWatchUser="Mydomain\MySQLWATCHAccount" # must exist in AD, account used by Grafana or Centrizaled Repository
$SQLWatchRoles=@('db_datareader','db_execute')
#Role for $SQLWatchUser on a Centralized Repository
#$SQLWatchRoles=@('db_owner')
$MaintenanceSolutionDB="DBATOOLS"
$MaintenanceSolutionCleanUpTime="96"
$MaintenanceSolutionTimeBackupLocation="\\BACKUPSERVER\SQL" # Where I put the Backup

# MaintenanceSolution
New-DbaDatabase                -SqlInstance $sqlinstance   -Name    $MaintenanceSolutionDB
Install-DbaMaintenanceSolution -SqlInstance $sqlinstance  -Database $MaintenanceSolutionDB -CleanupTime $MaintenanceSolutionCleanUpTime -BackupLocation $MaintenanceSolutionTimeBackupLocation -ReplaceExisting -LogToTable -InstallJobs

#SQLWatch
# The script creates the required database in the correct format`
Install-DbaSqlWatch            -SqlInstance $sqlinstance    -Database $SQLWatchDB 

# Create db_execute role
New-DbaDbRole                  -SqlInstance  $sqlinstance   -Database $SQLWatchDB -Role db_execute
Invoke-DbaQuery                -SqlInstance  $sqlinstance   -Database $SQLWatchDB -Query 'GRANT EXECUTE TO db_execute'
# Add login and user t the database
New-DbaLogin                   -SqlInstance $sqlinstance    -Login $SQLWatchUser -Force
New-DbaDbUser                  -SqlInstance $sqlinstance    -Database $SQLWatchDB -Login $SQLWatchUser -Force
Add-DbaDbRoleMember            -SqlInstance $sqlinstance    -Database $SQLWatchDB -Role $SQLWatchRoles -User $SQLWatchUser -confirm:$false

# WhoIsActive
Install-DbaWhoIsActive         -SqlInstance $sqlinstance  -Database $SQLWatchDB 
# WhoIsActive - SQLWatch Job activation
Set-DbaAgentJob                -SqlInstance $sqlinstance  -Job SQLWATCH-LOGGER-WHOISACTIVE -Enabled

# Optional
Invoke-DbaQuery -SqlInstance  $sqlinstance   -Database $SQLWatchDB -Query 'exec [dbo].[usp_sqlwatch_config_sqlserver_set_blocked_proc_threshold]'   
Invoke-DbaQuery -SqlInstance  $sqlinstance   -Database $SQLWatchDB -Query 'exec [dbo].[usp_sqlwatch_config_enable_compression_sqlwatch_indexes]'
Invoke-DbaQuery -SqlInstance  $sqlinstance   -Database $SQLWatchDB -Query 'exec [dbo].[usp_sqlwatch_config_enable_compression_sqlwatch_tables]'
1 Like
2
3

Thanks! I have pinned this to the top of the forum for everyone to see.