imajaydwivedi/sqldbatools

Powershell Module containing cmdlets for carrying out SQL DBA activities.

SQLDBATools

Powershell Module containing cmdlets for carrying out SQL DBA activities.

Functionality covered includes finding our server properties, disk utilization, discover sql instance in network, find license keys, setup mail profile, setup dba operator, get backup history, audit user/database permissions, maintain inventory, cleanup orphan database files from disk, find resouce consuming queries on server, space consumers, estimate space to add into disk/database files, setup maintenance jobs, perform basic basic server optimization settings etc.

This module also has built-in capability to setup Monitoring & Alerting system using PowerShell & SqlServer. The alerting system is designed to automatically clear alerts when no longer active, send email notifications, suppress alert is required, display alert history on Grafana. This available alerts include Disk Space, Blocking, Job Failure, High CPU, Memory Issue, and many more.

How to Install

One way to work with this module is to simply download this github repository at zip file, extract downloaded zip file, and extract it to folder named 'SQLDBATools'. Finally copy/paste it on one of the module folders returned by variable $PSGetPath.

It can also be installed easily from PSGallery using below command -

Install-Module -Name SQLDBATools
# Update-Module -Name SQLDBATools

Donation

If this project help you reduce time to develop, you can give me a cup of coffee :)

PayPal		UPI

Help or Documentation

Want to know in details about functionalitires supported by this module, kindly check below documentation -

Documentation on [SQLDBATools]

To learn on how to use this script, please watch below YouTube video:-

sqldbatools's Issues

Error - Wrapper-SdtDiskSpace - Parameter Value Null Provided

Wrapper-SdtDiskspace throws an error while processing the inventory server itself with FQDN.

Alert-SdtServerAvailability

Alert when a server is down or not responding

Alert-SdtDataAge

Alert for specific server, database, tables when data is older than x threshold days

Alert-SdtFailedSqlJob

Alert when a job has failed and crossed continuous failure threshold

Enhancements - Alert Sdt-DiskSpace by Client, Severity

Raise Alert for all servers group by Client, Severity. Take minimum value of $DelayMinutes from AlertRules table.

Add alert clearing for Wrapper-SdtDiskSpace Failed.

Create a separate job to clear all the alerts on timely basic. Maybe this can check dbo.sdt_alert table, fetch alert_key, state, severity, email_to, id.

Add Alert State & Id in Mail Notification Subject

Modify alert mail notification Subject to include

• Alert ID
• Alert State
• Subject

Sample

[ACTIVE] - [Id#32345] - Alert-SdtDiskSpace

Add-SdtBlockingAlert

Create blocking alert based on https://github.com/imajaydwivedi/SQLDBA-SSMS-Solution/issues/5

Add GMail Sensitive CSS

At present mail sent to Gmails is stripped of any CSS style causing mails not look good.

Kindly create separate CSS when SMTP server is gmail server.

Alert-SdtMemoryGrants

Alert when there are sessions pending for Memory Grant

Alert-SdtLogUsage

Alert when log file utilization cross threshold percent

Add - Server field in dbo.sdt_alert table

Kindly add [Server] field in dbo.sdt_alert table.
Plus, make this a XML column with server names.

For example, disk alert could be for multiple servers. So having those server names in this field would make sense for future reporting

Alert-SdtSqlAgentDown

Alert when SQL Server agent is down

Copy valid Private\Set-SdtEnvironmentVariables from old version when Module is updated

When we update module using Update-Module SQLDBATools, copy existing valid Private\Set-SdtEnvironmentVariables.ps1 file to new version path automatically when module is loaded.

Alert-SdtBlocking

Create alerting and monitoring for blocking

Alert-SdtFileGroupSpace

Create alert when database filegroup is low on space

Alert-SdtDatabaseBackup

Alert when there has been no backup since threshold time

Alert-SdtAgLatency

Alert when there is latency in availability group

Alert-SdtCpuUtilization

Alert and monitoring for high cpu utilization

Wrapper-SdtDiskSpace - Error - "out-file : Could not find a part of the path

After running Update-Module, and with first job execution, got below error messages -

Looking for location of SQLDBATools module to import..
Module file found based on wrapper file location
Import-Module "C:\Program Files\WindowsPowerShell\Modules\SQLDBATools\0.0.12\SQLDBATools.psm1" -DisableNameChecking
Loading module from $env:PSModulePath
out-file : Could not find a part of the path 'C:\Program
Files\WindowsPowerShell\Modules\SQLDBATools\0.0.12\Logs\Wrapper-SdtDiskSpace__Log__2021-11-27 07.32.08.txt'.
At C:\Program Files\WindowsPowerShell\Modules\SQLDBATools\0.0.12\Wrapper\Wrapper-SdtDiskSpace.ps1:78 char:28

• ... 1,-10} {2}" -f "($((Get-Date).ToString('yyyy-MM-dd HH:mm:ss')))","(ST ...

•                    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : OpenError: (:) [out-file], DirectoryNotFoundException
  • FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

out-file : Could not find a part of the path 'C:\Program
Files\WindowsPowerShell\Modules\SQLDBATools\0.0.12\Logs\Wrapper-SdtDiskSpace__Log__2021-11-27 07.32.08.txt'.
At C:\Program Files\WindowsPowerShell\Modules\SQLDBATools\0.0.12\Wrapper\Wrapper-SdtDiskSpace.ps1:87 char:26

• ... 1,-10} {2}" -f "($((Get-Date).ToString('yyyy-MM-dd HH:mm:ss')))","(IN ...

•                    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : OpenError: (:) [out-file], DirectoryNotFoundException
  • FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

Get-ChildItem : Cannot find path 'C:\Program Files\WindowsPowerShell\Modules\SQLDBATools\0.0.12\Logs' because it does
not exist.
At C:\Program Files\WindowsPowerShell\Modules\SQLDBATools\0.0.12\Wrapper\Wrapper-SdtDiskSpace.ps1:88 char:1

• Get-ChildItem -Path "$SdtLogsPath$($Script.Replace('.ps1',"Log*. ...

•   + CategoryInfo          : ObjectNotFound: (C:\Program File...ols\0.0.12\Logs:String) [Get-ChildItem], ItemNotFound 
   Exception
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand
  
  

out-file : Could not find a part of the path 'C:\Program
Files\WindowsPowerShell\Modules\SQLDBATools\0.0.12\Logs\Wrapper-SdtDiskSpace__Log__2021-11-27 07.32.08.txt'.
At C:\Program Files\WindowsPowerShell\Modules\SQLDBATools\0.0.12\Wrapper\Wrapper-SdtDiskSpace.ps1:94 char:26

• ... 1,-10} {2}" -f "($((Get-Date).ToString('yyyy-MM-dd HH:mm:ss')))","(IN ...

•                    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : OpenError: (:) [out-file], DirectoryNotFoundException
  • FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand

Alert-SdtMonitoringDisabled

Alert for server with monitoring disabled

Add functions to interact with [Credential Manager]

Add functions to interact with Credential Manager infra.

Error - Command not found in Wrapper-SdtDiskspace

When calling Wrapper-SdtDiskspace.ps1 without explicitly importing the SQLDBATools module, it fails to find underlying variables & functions.

Kindly add Import-Module as first line in Wrapper-Sdt*** functions.

Script Non Trusted Issue

Add instructions in documentation to unblock file using below code -

Get-ChildItem SQLDBAToolsModuleFolder -Recurse | Unblock-File

Create Jobs Automatically when SdtEnableInventory is True

Kindly automatically create jobs for Monitoring servers if

• $SdtEnableInventory is True
• $SdtInventoryInstance.$SdtInventoryDatabase has $SdtInventoryTable existing
• Warning if the script path is not accessible by SQL Service account
• Warning message to configure job with Credential/Proxy if above script path check fails

Also, we below jobs to monitor Inventory Server & Database itself

• Add windows scheduled task for Monitoring SQL Services of Inventory Server
• Add windows scheduled task for Monitoring Inventory database health on Inventory Server

Error: The property 'Name' cannot be found on this object.

I am getting the following error message;
The property 'Name' cannot be found on this object. Verify that the property exists.
At C:\Program Files\WindowsPowerShell\Modules\SQLDBATools\0.0.13\Functions\Retry-SdtCommand.ps1:29 char:17

•             $fields = $_.InvocationInfo.MyCommand.Name,
  

•             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : NotSpecified: (:) [], PropertyNotFoundException
  • FullyQualifiedErrorId : PropertyNotFoundStrict

Alert-SdtLinkedServerFailure

Alert when a linked server connectivity fails

Add [owner] in dbo.sdt_server_inventory

Add [owner] column in dbo.sdt_server_inventory to hold server owners email ids in string format.

This should allow email ids separated by commas or semi colons. which can be parsed in automation when generating alerts.

ConvertTo-SdtMarkdownTable - Does not produce correct display

ConvertTo-SdtMarkdownTable produces scrambled data when the column has multiline data, or no data in cell.

Attaching excel with dataset for reproducing issue - VM-Settings.xlsx

$excelData = Import-Excel C:\Users\adwivedi\Documents\VM-Settings.xlsx
$excelData | ConvertTo-SdtMarkdownTable | Out-File C:\Users\adwivedi\Documents\excelData.txt

Error: Get-SdtLinkedServer : Cannot bind argument to parameter 'Left' because it is an empty array.

Getting below error message when trying to figure out linked server on local instance -

PS C:\Windows\system32> Get-SdtLinkedServer -SqlInstance MyLocalServer| ft -AutoSize
Get-SdtLinkedServer : Cannot bind argument to parameter 'Left' because it is an empty array.
At line:1 char:1
+ Get-SdtLinkedServer -SqlInstance MyLocalServer| ft -AutoSize
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Get-SdtLinkedServer], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorEmptyArrayNotAllowed,Get-SdtLinkedServer
 

PS C:\Windows\system32> 

Add-SdtTempdbSaver

Function to automatically compile tempdb saver, and create sql agent job

Alert-SdtDatabaseAvailability

Alert when a database is not healthy

Feature - Use Gmail SMTP

Add feature to utilize GMAIL smtp server, credential if required

Generate Warning If SdtEnableInventory is True, but Env variables are not configured

Generate Warning If $SdtEnableInventory is True, but below Env variables are not configured -

• SdtInventoryInstance
• SdtInventoryDatabase
• SdtSmtpServer
• SdtDBAMailId
• SdtDBAGroupMailId

Alert-SdtEventLogs

Alert when high or critical event logs are detected

Alert-SdtServerReboot

Alert when a server has crashed or rebooted with any maintenance

Import Error - The variable '$previousEnvFile' cannot be retrieved because it has not been set.

Issue

When SQLDBATools module is placed directly using Copy/Paste or GitClone method in below folder structure, then Import-Module SQLDBATools generate error -

Error

PS C:\Windows\system32> Import-Module SQLDBATools
The variable '$previousEnvFile' cannot be retrieved because it has not been set.
At C:\Users\Ajay\Documents\WindowsPowerShell\Modules\SQLDBATools\SQLDBATools.psm1:95 char:32
+     if([String]::IsNullOrEmpty($previousEnvFile) -or (-not (Test-Path ...
+                                ~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (previousEnvFile:String) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : VariableIsUndefined
 
Import-Module : The module to process 'SQLDBATools.psm1', listed in field 'ModuleToProcess/RootModule' of module manifest 
'C:\Users\Ajay\Documents\WindowsPowerShell\Modules\SQLDBATools\SQLDBATools.psd1' was not processed because no valid module was found in any module directory.
At line:1 char:1
+ Import-Module SQLDBATools
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ResourceUnavailable: (SQLDBATools:String) [Import-Module], PSInvalidOperationException
    + FullyQualifiedErrorId : Modules_ModuleFileNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

Reproducer

Copy paste SQLDBATools in path structure like C:\Users\Ajay\Documents\WindowsPowerShell\Modules\SQLDBATools\SQLDBATools.psm1 and then call Import-Module SQLDBATools

Error: Get-SdtLocalTime : Exception calling "FindSystemTimeZoneById" with "1" argument(s): "The time zone ID 'Coordinated Universal Time' was not found on the local computer."

(2021-11-11 20:22:37) (INFO)     Calling 'Raise-SdtAlert' to generate alert notification..
(2021-11-11 20:22:38) (ERROR)    Something went wrong. Inside catch block.
(2021-11-11 20:22:38) (INFO)     Sending mail notification using Raise-SdtAlert..
Get-SdtLocalTime : Exception calling "FindSystemTimeZoneById" with "1" argument(s): "The time zone ID 'Coordinated Universal Time' was not found on the local computer."
At C:\Program Files\WindowsPowerShell\Modules\SQLDBATools\0.0.8\Functions\Raise-SdtAlert.ps1:113 char:137
+ ... ast alert was sent @ $((Get-SdtLocalTime $lastNotifiedDateUTC).ToStri ...
+                             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-SdtLocalTime], MethodInvocationException
    + FullyQualifiedErrorId : TimeZoneNotFoundException,Get-SdtLocalTime

Alert-SdtLongRunningJobs

Alert when there are jobs running over threshold time

Alert-SdtAgHealth

Alert when availability groups are not healthy

Wrapper-SdtDiskspace - Change Default Parameter Values

Set parameter values as below-

WarningThresholdPercent = 75.00
CriticalThresholdPercent = 85.00
FailureNotifyThreshold = 3
DelayMinutes = 60

Redirect Certain Alert to Individual App Teams

Kindly add alert redirection logic. Basically created a table, say, dbo.sdt_alert_rules with columns like alert_key, server_name, email_to, team_name, rule_type (time, date), start_date_time, end_date_time, enable_dba, created_by, updated_by, created_date, updated_date etc.

This should be a temporal table to track accidental changes.

Alert-SdtMemoryDump

Alert when a memory dump is generated

Alert-SdtDiskMissing

Alert when any disk drive is missing from server. Very common in case of virtual machines

Get-SdtServerHealthReport

Get beautiful mail with health report of server using https://github.com/imajaydwivedi/SQLDBA-SSMS-Solution/blob/master/BlitzQueries/RingBuffer-PerfMon-CPU-Memory.sql

Alert-SdtPerfmonCounter-XXX

Alert for specific performance counters

Alert-SdtReplicationLatency

Alert when there is latency in transactional replication

Add-SdtLogSaver

Function to automatically create log saver, and setup sql agent job to execute same.

Alert-SdtHighInstanceWait

Alert when a specific wait type duration crossed threshold

Alert-SdtSqlServerFatalError

Get an alert when fatal errors are detected in SQL Server logs