PowerShell SQL Server Validation Utility DBAChecks
PowerShell SQL Server Validation Utility - DBAChecks
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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolPowerShell SQL Server Validation Utility – DBAChecks
March 15, 2019 by Rajendra Gupta 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 higherInstallation Steps
Open Windows PowerShell with administrative rights and run the following command 1 >Install-Module –Name DBAChecks 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. 1 >Install-Module –Name DBAChecks –skippublishercheck 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. 1 >Get-DBCcheck 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. 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. 1 >Get-DBcCheck Out-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. 1 >Get-help Get-DbaLastGoodCheckDb 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. 1 PS > $server = "kashish\SQL2019CTP" In the following command, we want to check last successful DBCC CHECKDB for all databases present on instance kashish\SQL2019CTP. 1 PS > Invoke-DbcCheck -SqlInstance $server -Check Get-DbaLastGoodCheckDb 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. 1 PS > Invoke-DbcCheck -SqlInstance $server -Check Get-DbaLastGoodCheckDb –Database SQLShackDemo_ADR 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. 1 > Get-DbaLastGoodCheckDb -SqlInstance $server Format-Table –AutoSize 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. 1 >Get-DbaLastGoodCheckDb -SqlInstance $server Out-GridView 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. 1 >Get-DbaLastGoodCheckDb -SqlInstance $server Out-GridView 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. 1 >Get-DbaLastGoodCheckDb -SqlInstance $server -excludedatabase tempdb Out-GridView 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. 1 >Get-DbaLastGoodCheckDb -SqlInstance $server -SqlCredential rajendra Out-GridView 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. 1 > Get-DbcConfig Format-Table –AutoSize Let us filter out the results for keyword DBCC in a grid view. 1 >Get-DBcConfig Out-GridView 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. 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. 1 >Set-DbcConfig -Name policy.dbcc.maxdays -Value 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 articlesTable 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 Author Recent Posts Rajendra GuptaHi! 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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022