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.

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.

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.

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

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.

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.

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.

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

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

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

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

Let us run the DBCC CHECKDB command on SQLShackDemo_ADR database. We do not have any allocation and consistency error in this database.

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.

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.

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.

Once authentication is completed, you get the required result.

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.

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.

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

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.

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

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023