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.
在本文中，我们将探讨DBAChecks PowerShell SQL Server模块 。 我们可以使用DBAChecks模块来使用各种模块来验证SQL Server实例。 我们还可以使用它执行100多次配置审查。
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.
DBA负责维护SQL Server数据库实例，它们的可用性以及对特定于环境的策略的遵从性。 PowerShell喜欢SQL Server，它使您可以有效地执行SQL任务。 我们可以使用PowerShell模块轻松进行管理检查。
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 监视SQL Server代理作业
- 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.
我们也可以使用t-SQL执行这些任务。 但是，DBAChecks通过最少的配置提供了更好的体验。 您需要将实例名称和参数一起传递以进行验证，然后就可以准备好结果了。
要求： (Requirements:)
- PowerShell V4 or higher PowerShell V4或更高版本
安装步骤 (Installation Steps)
Open Windows PowerShell with administrative rights and run the following command
以管理权限打开Windows PowerShell，然后运行以下命令
>Install-Module –Name DBAChecks
We are installing a custom PowerShell module DBAChecks. Enter Y to install this module from PSGallery.
我们正在安装自定义PowerShell模块DBAChecks。 输入Y从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.
在以下屏幕截图中，我们收到错误消息，指出新模块Pester与旧的installs模块冲突。 如果您已经安装了Pester模块，则需要在发布者检查验证期间收到此错误消息。
We need to skip publication check using the following command.
我们需要使用以下命令跳过发布检查。
>Install-Module –Name DBAChecks –skippublishercheck
We need to provide permission again by pressing Y, and it installs DBACheck PowerShell Module.
我们需要通过按Y再次提供权限，然后安装DBACheck PowerShell模块。
We can check all supported DBAChecks using the following command.
我们可以使用以下命令检查所有受支持的DBACheck。
>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
在以下屏幕截图中，我们可以看到针对SQL Server实例的大量检查。 我们可以将支票分为以下几组
| 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. |
组 | 描述 | 例子 |
代理商 | SQL Server代理相关的验证 | SQL Server代理服务帐户，检查失败的作业 |
数据库 | 与数据库相关的验证 | 验证上一次数据库备份，数据库一致性验证，数据库配置验证，例如Auto_Close，AutoClose，AutoShrink。 。 检查孤立的用户 |
哈德 | 集群相关验证 | 集群和节点运行状况 |
实例 | SQL Server实例相关的验证 | SQL服务的服务帐户，验证链接的服务器，备份路径，TempDB配置，实例内存配置等。 |
LogShipping | 日志传送 | 日志传送主要和次要 |
服务器 | 服务器相关验证 | 磁盘空间利用率，ping目标 |
维修计划 | 数据库维护任务 | 备份，完整性，索引，作业历史记录清除等数据库维护任务。 |
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.
在PowerShell中，我们可以在网格视图中查看结果，以提供用户友好的视图。 它使您能够从所需列中过滤事件。
Execute below command in PowerShell to get DBAChecks with GridView.
在PowerShell中执行以下命令以使用GridView获取DBACheck。
>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.
在本文中，我们要验证数据库的一致性，并获取SQL实例中最后一个成功的DBCC CHECKDB命令的时间戳。 如果一致性检查验证的年龄比定义的窗口还早，我们还将收到一条消息。
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.
注意： 我们正在使用Azure Data Studio终端运行以下部分中的命令。 您可以在此终端窗口中轻松进行格式化，复制\粘贴。 您也可以在Windows PowerShell窗口中运行命令。
We can get information about any particular command in DBAChecks using get-help. Following command gives detailed information about Get-DbaLastGoodCheckDb command.
我们可以使用get-help获得有关DBAChecks中任何特定命令的信息。 以下命令提供有关Get-DbaLastGoodCheckDb命令的详细信息。
>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.
在此演示中，我们使用SQL实例kashish \ SQL2019CTP 。 我将设置一个变量来保存此服务器名称。 我们不必在每个命令中都指定实例名称。 在这种情况下，我们可以调用变量。
PS > $server = "kashish\SQL2019CTP"
In the following command, we want to check last successful DBCC CHECKDB for all databases present on instance kashish\SQL2019CTP.
在以下命令中，我们要检查实例kashish \ SQL2019CTP上存在的所有数据库的最后成功DBCC CHECKDB 。
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.
让我们了解突出显示的Master数据库的输出。
- 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) KASHISH \ SQL2019 CTP上对主服务器的最后一次良好完整性检查应小于7天：默认情况下，DBACheck策略定义为一致性检查寿命应小于7天。 （我们将在后面的更改政策部分中介绍）
- 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 期望实际值大于2019-03-06T09：16.50：75577334 + 5，因为您应该在那段时间运行DBCC CheckDB但得到了$ Null：在我的测试实例中，我没有Master的任何DBCC CHECKDB历史记录数据库; 因此，它返回NULL值。 在此消息中，它表明实际最后一个DBCC CHECKDB时间戳应大于所提及的时间戳
- It also checks for data purity in DBCC CHECKDB. Data Purity checks that column values are valid 它还在DBCC CHECKDB中检查数据纯度。 数据纯度检查列值是否有效
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.
我们收到每个数据库的详细错误消息。 如果在SQL实例中有大量数据库，则很难解释结果。 我们可以使用–Database参数检查特定的数据库。
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
测试通过1，失败：1，跳过：0，待定：0.不确定：0
We can interpret this result as following
我们可以将这个结果解释如下
- Test passed 1: Data Purity check enabled 测试通过1：启用数据纯度检查
- Failed 1: DBCC CheckDB age older than 7 days 失败1：DBCC CheckDB年龄超过7天
- Skipped 0: We did not skip any database; it checked for database mentioned in command 跳过0：我们没有跳过任何数据库； 它检查了命令中提到的数据库
We can get the output in a tabular format using the following DBAChecks command.
我们可以使用以下DBAChecks命令以表格格式获取输出。
> 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 DatabaseCreated –数据库创建日期
- DaysSinceDbCreated– Age of a particular database DaysSinceDbCreated –特定数据库的年龄
- 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 DaysSinceLastGoodCheckdb –如果我们在数据库上执行了DBCC CheckDB，我们将获得最近一次成功一致性检查的天数。 在以下窗口中，该字段为空，因为我们未对任何数据库执行任何一致性检查
- Status– CheckDB should be performed 状态 –应该执行CheckDB
We can also get a result in GridView with the following command.
我们还可以使用以下命令在GridView中获得结果。
>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.
让我们在SQLShackDemo_ADR数据库上运行DBCC CHECKDB命令。 在此数据库中，我们没有任何分配和一致性错误。
Let us rerun the DBAChecks command. In the following query, we specified Out-GridView to get results in a grid format.
让我们重新运行DBAChecks命令。 在以下查询中，我们指定了Out-GridView以网格格式获取结果。
>Get-DbaLastGoodCheckDb -SqlInstance $server | Out-GridView
In the following screenshot, you can view the following for SQLShackDemo_ADR database.
在以下屏幕截图中，您可以查看SQLShackDemo_ADR数据库的以下内容。
- DaysSinceLastGoodCheckdb – 0 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.
我们已经在单个数据库上执行了DBCC CHECKDB。 因此，状态保持不变-应该对所有其他数据库执行CheckDB 。
Suppose we want to exclude any database from appearing in the output. We can do it using –excludedatabase parameter.
假设我们要排除任何数据库出现在输出中。 我们可以使用–excludedatabase参数来实现。
In the following query, we excluded tempdb for consistency check validations.
在以下查询中，我们排除了tempdb进行一致性检查验证。
>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.
在先前的查询中，我们没有指定任何凭据来连接SQL实例。 如果我们未指定任何凭据，则它将使用Windows身份验证进行连接。 我们可以使用带有参数SQLInstance的 SQL身份验证进行连接。
>Get-DbaLastGoodCheckDb -SqlInstance $server -SqlCredential rajendra | Out-GridView
When we execute the command with –SqlCredential, it opens a pop-up to enter the credentials.
当我们使用–SqlCredential执行命令时，它将打开一个弹出窗口以输入凭据。
Once authentication is completed, you get the required result.
身份验证完成后，您将获得所需的结果。
修改DBAChecks的一致性检查策略 (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.
在前面的示例中，您可以看到我们是否在7天之前执行了一致性检查，您会收到一条消息-应该执行CheckDB。
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.
假设在我的环境中； 我想检查年龄5天的一致性检查。 如果我们在5天之前执行了一致性检查，则应该收到一条消息。
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.
我们可以使用Get-DBcConfig命令进行配置更改。 您可以使用Get-DbcConfig命令获取配置更改的完整列表。 它提供了配置的名称，描述和特定配置的当前分配值。
Execute below command in PowerShell to get a list of configuration options.
在PowerShell中执行以下命令以获取配置选项列表。
> Get-DbcConfig | Format-Table –AutoSize
Let us filter out the results for keyword DBCC in a grid view.
让我们在网格视图中过滤掉关键字DBCC的结果。
>Get-DBcConfig | Out-GridView
In the following output, you can see the highlighted configuration for DBCC.
在以下输出中，您可以看到突出显示的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. |
名称 | 当前值 | 描述 |
Policy.dbcc.maxdays | 7 | 上一次成功的一致性检查与当前日期之间的差异应大于Policy.dbcc.maxdays才能成功进行验证。 |
We can change the configuration using Set-DBcConfig command. In the following query, we changed the configuration for policy.dbcc.maxdays to 5.
我们可以使用Set-DBcConfig命令更改配置。 在以下查询中，我们将policy.dbcc.maxdays的配置更改为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. |
名称 | 修改值 | 描述 |
Policy.dbcc.maxdays | 5 | 上一次成功的一致性检查与当前日期之间的差异应大于Policy.dbcc.maxdays才能成功进行验证。 |
>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.
重新运行DBAChecks进行特定数据库的一致性检查，以限制结果。 在输出中，您可以看到该消息。
Last good integrity check for SQLShackDemo on KASHISH\SQL2019CTP should be less than 5 days old.
KASHISH \ SQL2019CTP上对SQLShackDemo的最后一次良好完整性检查应少于5天 。
结论 (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
在本文中，我们探索了PowerShell模块DBAChecks来在SQL Server实例中执行验证，尤其侧重于数据库一致性检查验证。 我们将在以后的文章中继续探索该技术
目录 (Table of contents)
翻译自: https://www.sqlshack.com/powershell-sql-server-validation-utility-dbachecks/
扫一扫
2549
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
