PowerShell SQL Server Validation Utility DBAChecks

PowerShell SQL Server Validation Utility DBAChecks

PowerShell SQL Server Validation Utility - DBAChecks

SQLShack

SQL Server training Español

PowerShell 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 higher

Installation 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 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 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

Related posts

DBATools PowerShell Module for SQL Server DBATools PowerShell SQL Server Database Backups commands Get SQL Database details using DBATools Script SQL Server objects using DBATools Creating a SQL Server Database using DBATools 6,798 Views

Follow us

Popular

SQL Convert Date functions and formats SQL Variables: Basics and usage SQL PARTITION BY Clause overview Different ways to SQL delete duplicate rows from a SQL Table How to UPDATE from a SELECT statement in SQL Server SQL Server functions for converting a String to a Date SELECT INTO TEMP TABLE statement in SQL Server SQL WHILE loop with simple examples How to backup and restore MySQL databases using the mysqldump command CASE statement in SQL Overview of SQL RANK functions Understanding the SQL MERGE statement INSERT INTO SELECT statement overview and examples SQL multiple joins for beginners with examples Understanding the SQL Decimal data type DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key SQL Not Equal Operator introduction and examples SQL CROSS JOIN with examples The Table Variable in SQL Server SQL Server table hints – WITH (NOLOCK) best practices

Trending

SQL Server Transaction Log Backup, Truncate and Shrink Operations Six different methods to copy tables between databases in SQL Server How to implement error handling in SQL Server Working with the SQL Server command line (sqlcmd) Methods to avoid the SQL divide by zero error Query optimization techniques in SQL Server: tips and tricks How to create and configure a linked server in SQL Server Management Studio SQL replace: How to replace ASCII special characters in SQL Server How to identify slow running queries in SQL Server SQL varchar data type deep dive How to implement array-like functionality in SQL Server All about locking in SQL Server SQL Server stored procedures for beginners Database table partitioning in SQL Server How to drop temp tables in SQL Server How to determine free space and file size for SQL Server databases Using PowerShell to split a string into an array KILL SPID command in SQL Server How to install SQL Server Express edition SQL Union overview, usage and examples

Solutions

Read a SQL Server transaction logSQL Server database auditing techniquesHow to recover SQL Server data from accidental UPDATE and DELETE operationsHow to quickly search for SQL database data and objectsSynchronize SQL Server databases in different remote sourcesRecover SQL data from a dropped table without backupsHow to restore specific table(s) from a SQL Server database backupRecover deleted SQL data from transaction logsHow to recover SQL Server data from accidental updates without backupsAutomatically compare and synchronize SQL Server dataOpen LDF file and view LDF file contentQuickly convert SQL code to language-specific client codeHow to recover a single table from a SQL Server database backupRecover data lost due to a TRUNCATE operation without backupsHow to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operationsReverting your SQL Server database back to a specific point in timeHow to create SSIS package documentationMigrate a SQL Server database to a newer version of SQL ServerHow to restore a SQL Server database backup to an older version of SQL Server

Categories and tips

►Auditing and compliance (50) Auditing (40) Data classification (1) Data masking (9) Azure (295) Azure Data Studio (46) Backup and restore (108) ►Business Intelligence (482) Analysis Services (SSAS) (47) Biml (10) Data Mining (14) Data Quality Services (4) Data Tools (SSDT) (13) Data Warehouse (16) Excel (20) General (39) Integration Services (SSIS) (125) Master Data Services (6) OLAP cube (15) PowerBI (95) Reporting Services (SSRS) (67) Data science (21) ►Database design (233) Clustering (16) Common Table Expressions (CTE) (11) Concurrency (1) Constraints (8) Data types (11) FILESTREAM (22) General database design (104) Partitioning (13) Relationships and dependencies (12) Temporal tables (12) Views (16) ►Database development (418) Comparison (4) Continuous delivery (CD) (5) Continuous integration (CI) (11) Development (146) Functions (106) Hyper-V (1) Search (10) Source Control (15) SQL unit testing (23) Stored procedures (34) String Concatenation (2) Synonyms (1) Team Explorer (2) Testing (35) Visual Studio (14) DBAtools (35) DevOps (23) DevSecOps (2) Documentation (22) ETL (76) ►Features (213) Adaptive query processing (11) Bulk insert (16) Database mail (10) DBCC (7) Experimentation Assistant (DEA) (3) High Availability (36) Query store (10) Replication (40) Transaction log (59) Transparent Data Encryption (TDE) (21) Importing, exporting (51) Installation, setup and configuration (121) Jobs (42) ▼Languages and coding (686) Cursors (9) DDL (9) DML (6) JSON (17) PowerShell (77) Python (37) R (16) SQL commands (196) SQLCMD (7) String functions (21) T-SQL (275) XML (15) Lists (12) Machine learning (37) Maintenance (99) Migration (50) Miscellaneous (1) ►Performance tuning (869) Alerting (8) Always On Availability Groups (82) Buffer Pool Extension (BPE) (9) Columnstore index (9) Deadlocks (16) Execution plans (125) In-Memory OLTP (22) Indexes (79) Latches (5) Locking (10) Monitoring (100) Performance (196) Performance counters (28) Performance Testing (9) Query analysis (121) Reports (20) SSAS monitoring (3) SSIS monitoring (10) SSRS monitoring (4) Wait types (11) ►Professional development (68) Professional development (27) Project management (9) SQL interview questions (32) Recovery (33) Security (84) Server management (24) SQL Azure (271) SQL Server Management Studio (SSMS) (90) SQL Server on Linux (21) ►SQL Server versions (177) SQL Server 2012 (6) SQL Server 2016 (63) SQL Server 2017 (49) SQL Server 2019 (57) SQL Server 2022 (2) ►Technologies (334) AWS (45) AWS RDS (56) Azure Cosmos DB (28) Containers (12) Docker (9) Graph database (13) Kerberos (2) Kubernetes (1) Linux (44) LocalDB (2) MySQL (49) Oracle (10) PolyBase (10) PostgreSQL (36) SharePoint (4) Ubuntu (13) Uncategorized (4) Utilities (21) Helpers and best practices BI performance counters SQL code smells rules SQL Server wait types © 2022 Quest Software Inc. ALL RIGHTS RESERVED. GDPR Terms of Use Privacy
Share:
0 comments

Comments (0)

Leave a Comment

Minimum 10 characters required

* All fields are required. Comments are moderated before appearing.

No comments yet. Be the first to comment!