Rajendra Gupta
PowerShell SQL Server module DBAChecks

PowerShell SQL Server Validation Utility – DBAChecks

March 15, 2019 by

In this article, we will explore the DBAChecks PowerShell SQL Server Module. We can use the DBAChecks module to validate SQL Server instances using the various modules. We can also perform more than 100 configuration reviews using it.

DBA’s are responsible for maintaining SQL Server database instance, their availability and compliance with the policy specific to the environment. PowerShell loves SQL Server, and it gives you wings to perform SQL tasks effectively. We can easily do administrative checks using the PowerShell Module.

Suppose you want to perform the following tasks in your environment

  • You want to validate last successful database validation as per defined policy
  • You want to know the time since the last backup
  • Monitor SQL Server Agent jobs
  • Verify database files and their configuration options
  • We can also check Network latency compared with a defined threshold

We can perform these tasks using t-SQL as well. However, DBAChecks provides a better experience with minimum configuration. You need to pass the instance name along with parameters to validate, and you are ready with results.

Requirements:

  • PowerShell V4 or higher

Installation Steps

Open Windows PowerShell with administrative rights and run the following command

We are installing a custom PowerShell module DBAChecks. Enter Y to install this module from PSGallery.

PowerShell SQL Server module DBAChecks

In the following screenshot, we get the error message that new module Pester conflicts with an old installs module. If you have already installed the Pester module, you need to receive this error message during the publisher check validation.

PowerShell SQL Server module DBAChecks installation

We need to skip publication check using the following command.

We need to provide permission again by pressing Y, and it installs DBACheck PowerShell Module.

PowerShell SQL Server module DBAChecks - Skip publication check

We can check all supported DBAChecks using the following command.

In the following screenshot, we can see a vast range of checks against the SQL Server instance. We can divide the checks in the following group

Group

Description

Examples

Agent

SQL Server Agent related validation

SQL Server Agent service account, Check failed jobs

Database

Database related validations

Validate last database backup, database consistency validation, Database configuration validations such as Auto_Close, AutoClose, AutoShrink. .

Check for orphaned users

HADR

Cluster related validations

Cluster and Node health

Instance

SQL Server Instance related validation

Service account for SQL Services, Validate linked server, backup path, TempDB configurations, Instance memory configuration etc.

LogShipping

Log Shipping

Log Shipping primary and secondary

Server

Server related validation

Disk space utilization, ping target

MaintenancePlan

Database Maintenance tasks

Database Maintenance tasks for backup, integrity, index, job history purge etc.

PoweShell SQL Server module DBAChecks: Get-DBCCheck outout

In PowerShell, we can view results in a grid view for a user-friendly view. It enables to filter the events from the column we want.

Execute below command in PowerShell to get DBAChecks with GridView.

It opens another result window with search filter in it. We can see all validations along with type, group, Config and description.

powershell sql server module DBAChecks: Get-DBCCheck output in Grid view

Suppose, we want to view the results for keyword Backup present in the description column. Click on Add Criteria, select the filter column from the drop-down list and enter keyword as per the following image.

powershell sql server module DBAChecks: search in Grid view PowerShell

In this article, we want to validate database consistency and get a timestamp for the last successful DBCC CHECKDB command in our SQL instance. We should also get a message if consistency check validations age is older than the defined window.

Note: We are using Azure Data Studio terminal to run the commands in the following sections. You can easily do formatting, copy\paste in this terminal window. You can run the command in the Windows PowerShell window as well.

We can get information about any particular command in DBAChecks using get-help. Following command gives detailed information about Get-DbaLastGoodCheckDb command.

powershell sql server module DBAChecks: Get information about a particular command DBAChecks.

In this demo, we are using SQL instance kashish\SQL2019CTP. I will set up a variable to hold this server name. We do not have to specify the instance name with each command. We can call the variable in this case.

In the following command, we want to check last successful DBCC CHECKDB for all databases present on instance kashish\SQL2019CTP.

We get the detailed logs as an output of this command.

Let us understand output for Master database as highlighted.

  • Last good integrity check for master on KASHISH\SQL2019 CTP should be less than 7 days old: By default, DBACheck policy is defined that consistency check age should be less than 7 days. ( We will cover in a later section of changing this policy)
  • Expected the actual value to be greater than 2019-03-06T09:16.50:75577334+5, because you should run a DBCC CheckDB inside that time but got $Null: In my test instance, I do not have any DBCC CHECKDB history for Master database; therefore, it returns a NULL value. In this message, it shows that actual last DBCC CHECKDB timestamp should be greater than mentioned timestamp
  • It also checks for data purity in DBCC CHECKDB. Data Purity checks that column values are valid

vpowershell sql server module DBAChecks: iew the consistency validation check using DBACheck.

powershell sql server module DBAChecks: view the consistency validation check output using DBACheck.

We get a detailed error message for each database. If we are having a large number of databases in a SQL instance, it becomes difficult to interpret the results. We can check for a particular database using –Database parameter.

In the output, it checks for the only specified database. Take a look at the following the message.

Test passed 1, Failed: 1, Skipped: 0, Pending: 0. Inconclusive: 0

We can interpret this result as following

  • Test passed 1: Data Purity check enabled
  • Failed 1: DBCC CheckDB age older than 7 days
  • Skipped 0: We did not skip any database; it checked for database mentioned in command

powershell sql server module DBAChecks: Filter result for a particular database

We can get the output in a tabular format using the following DBAChecks command.

In the following screenshot, you can see all the details in a user-friendly way.

We get the following useful information in this format.

  • DatabaseCreated– Database creation date
  • DaysSinceDbCreated– Age of a particular database
  • DaysSinceLastGoodCheckdb– If we have performed DBCC CheckDB on a database, we get r of days of last successful consistency check. In the following window, it is blank because we have not performed any consistency check on any database
  • Status– CheckDB should be performed

powershell sql server module DBAChecks: Output in a tabular format

We can also get a result in GridView with the following command.

powershell sql server module DBAChecks: DBAChecks Output in Grid View

Let us run the DBCC CHECKDB command on SQLShackDemo_ADR database. We do not have any allocation and consistency error in this database. powershell sql server module DBAChecks: DBCC CHECKDB output

Let us rerun the DBAChecks command. In the following query, we specified Out-GridView to get results in a grid format.

In the following screenshot, you can view the following for SQLShackDemo_ADR database.

  • DaysSinceLastGoodCheckdb – 0
  • Status – Ok

We have performed DBCC CHECKDB on a single database. Therefore, status remains the same- CheckDB should be performed for all other databases.

powershell sql server module DBAChecks: Validation check after performing DBCC CHECKDB on a database

Suppose we want to exclude any database from appearing in the output. We can do it using –excludedatabase parameter.

In the following query, we excluded tempdb for consistency check validations.

In the output, we did not get any row for the excluded database.

powershell sql server module DBAChecks: Exclude database for consistency validation check

We did not specify any credentials to connect with SQL instance in previous queries. If we do not specify any credentials, it connects using Windows authentication. We can connect using SQL authentication with parameter SQLInstance.

When we execute the command with –SqlCredential, it opens a pop-up to enter the credentials.

powershell sql server module DBAChecks: Connect using SQL authentication

Once authentication is completed, you get the required result.

powershell sql server module DBAChecks: View result after connecting with SQL user

Modifying a consistency check policy for DBAChecks

In the earlier example, you can see if we have performed consistency check before 7 days, you get a message- CheckDB should be performed.

Suppose, in my environment; I want to check for consistency check for age 5 days. If we have performed a consistency check before 5 days, we should get a message for that.

We can do configuration changes using Get-DBcConfig command. You can get a complete list of configuration changes using Get-DbcConfig command. It gives the name of the configuration, description and current assigned value for a particular configuration.

Execute below command in PowerShell to get a list of configuration options.

powershell sql server module DBAChecks: DbcConfig commands

Let us filter out the results for keyword DBCC in a grid view.

In the following output, you can see the highlighted configuration for DBCC.

Name

Current Value

Description

Policy.dbcc.maxdays

7

Difference between the last successful consistency check and current date should be greater than Policy.dbcc.maxdays for successful validation.

powershell sql server module DBAChecks: Modifying consistency check policy for DBAChecks

We can change the configuration using Set-DBcConfig command. In the following query, we changed the configuration for policy.dbcc.maxdays to 5.

Name

Modified Value

Description

Policy.dbcc.maxdays

5

Difference between the last successful consistency check and current date should be greater than Policy.dbcc.maxdays for successful validation.

powershell sql server module DBAChecks: Modifying consistency check policy for DBAChecks

Rerun the DBAChecks for consistency check for a particular database to limit result. In the output, you can see the message.

Last good integrity check for SQLShackDemo on KASHISH\SQL2019CTP should be less than 5 days old.

powershell sql server module DBAChecks: View conssitency check output

Conclusion

In this article, we explored the PowerShell module DBAChecks to perform validation in SQL Server instances with a particular focus on database consistency check validation. We will continue to explore this technology more in future articles

Table of contents

DBATools PowerShell Module for SQL Server
PowerShell SQL Server Validation Utility – DBAChecks
SQL Database Backups using PowerShell Module – DBATools
IDENTITY columns threshold using PowerShell SQL Server DBATools
DBATools PowerShell SQL Server Database Backups commands
SQL Restore Database using DBATools
Validate backups with SQL restore database operations using DBATools
Fix Orphan users in SQL Server using DBATools PowerShell
Creating a SQL Server Database using DBATools
Get SQL Database details using DBATools
Get-DbaHelpIndex command in DBATools
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views