SQL Server 2016中的新PowerShell Cmdlet

最新推荐文章于 2023-08-01 16:46:11 发布

culuo4781

最新推荐文章于 2023-08-01 16:46:11 发布

阅读量557

点赞数

文章标签： linux java python 数据库运维

原文链接：https://www.sqlshack.com/new-powershell-cmdlets-sql-2016/

版权

介绍 (Introduction)

PowerShell is Windows Shell that can be used to automate tasks in Windows, Exchange, Azure, SQL Server and more.

PowerShell是Windows Shell，可用于自动执行Windows，Exchange， Azure ， SQL Server等中的任务。

Cmdlets are commands used in PowerShell.

Cmdlet是PowerShell中使用的命令。

In this new article, we will talk about the new cmdlets in PowerShell in SQL Server 2016.

在这篇新文章中，我们将讨论SQL Server 2016中PowerShell中的新cmdlet。

There are 4 new areas where cmdlets were included:

包含cmdlet的有4个新区域：

SQL Server Logs
SQL Server日志
SQL Server Agent
SQL Server代理
Azure SQL
Azure SQL
Always Encrypted
始终加密

In this article, we will show the SQL Server Log and SQL Server Agent cmdlets and list the other cmdlets.

在本文中，我们将显示SQL Server Log和SQL Server Agent cmdlet并列出其他cmdlet。

要求 (Requirements)

SQL Server 2016 Installed.
SQL Server 2016已安装。

Getting Started

入门

We will first see the SQL Server Error Logs. In SQL Server Management Studio (SSMS), you can see the SQL Log Files in Management>SQL Server Logs:

我们将首先看到SQL Server错误日志。在SQL Server Management Studio（SSMS）中，可以在管理> SQL Server日志中查看SQL日志文件：

In the Log Files, you can see information about the process started, services started, verify the status of backup operations, and detect error messages related to the execution of SQL Scripts or batches:

在日志文件中，您可以查看有关已启动的进程，已启动的服务，验证备份操作的状态以及检测与SQL脚本或批处理的执行有关的错误消息的信息：

We can now check the SQL Error Log File using PowerShell. On any node, right click and select Start PowerShell:

现在，我们可以使用PowerShell检查SQL错误日志文件。在任何节点上，右键单击并选择启动PowerShell：

The module used to be SQLPS and now the module name is SQLServer:

该模块以前是SQLPS，现在模块名称是SQLServer：

To verify the SQL Error Log entries, use the following example in PowerShell:

要验证SQL错误日志条目，请在PowerShell中使用以下示例：

Get-sqlerrorlog

Get-SQLErrorlog

Get-SqlErrorLog will display all the entries in the SQL Log file:

Get-SqlErrorLog将显示SQL日志文件中的所有条目：

There are thousands of entries in the log file. The following example will show how to show only the entries in the Error Log that contain the word collation in PowerShell:

日志文件中有数千个条目。以下示例将显示如何仅显示错误日志中在PowerShell中包含单词collation的条目：

Get-sqlerrorlog | Where-Object {$_.Text -like ‘*collation*’}

get-sqlerrorlog | Where-Object {$ _。Text -like'* collation *'}

The sentence is showing all the entries where the Text property contains the word collation. There are other comparison operators that you may find useful:

该句子显示了Text属性包含单词归类的所有条目。您可能还会发现其他比较运算符有用：

-eq	Equal
-ge	Greater than or equal
-gt	Greater than
-lt	Less than
-le	Less than or equal
-match	Regular expression comparison
-like	Wildcard comparison
-ne	Not equal
-notlike	Wildcard comparison
-notmatch	Regular expression comparison

-eq	等于
-ge	大于或等于
-gt	比...更棒
-lt	少于
-le	小于或等于
-比赛	正则表达式比较
-喜欢	通配符比较
-ne	不相等
-不喜欢	通配符比较
-不匹配	正则表达式比较

If you need to filter all the sentences that contain the word collation and after a defined date, the following example will help you to filter dates using PowerShell:

如果需要过滤所有包含单词归类且在定义日期之后的句子，以下示例将帮助您使用PowerShell过滤日期：

Get-sqlerrorlog | Where-Object {$_.Text -like ‘*collation*’ -and $_.Date -gt ‘8/8/2016’}

get-sqlerrorlog | Where-Object {$ _。Text -like'* collation *'-和$ _。Date -gt'8/8/2016'}

The example shows all the Error Log entries that contain the word collation and after October 8:

该示例显示了10月8日之后所有包含单词collation的错误日志条目：

It is also possible to configure the number of files of SQL Error Logs. The oldest files are recycled whenever a new log file is created. In SSMS, you can change the default number. Right click on SQL Server Log and select Configure:

还可以配置SQL错误日志的文件数。每当创建新的日志文件时，都会回收最旧的文件。在SSMS中，您可以更改默认号码。右键单击“ SQL Server日志”，然后选择“配置”：

When you enable the option “Limit the number of error log files before they are recycled”, you can specify the number of files:

启用选项“错误日志文件在回收之前限制数量”时，可以指定文件数量：

To limit in PowerShell, return to the PowerShell command line and write the following:

要限制在PowerShell中，请返回PowerShell命令行并编写以下内容：

Set-SqlErrorLog -ServerInstance “.” -MaxLogCount 8 | Out-Null

Set-SqlErrorLog -ServerInstance“。” -MaxLogCount 8 | 空空

The cmdlets will change the limit of error log files to 8. To verify, right click on SQL Server Logs and select configure:

cmdlet将错误日志文件的限制更改为8。要进行验证，请右键单击“ SQL Server日志”，然后选择“配置”：

As you can see, PowerShell changed the number of Error Logs to 8:

如您所见，PowerShell将错误日志的数量更改为8：

SQL Agent

SQL代理

PowerShell now includes cmdlets to automate the Agent.

现在，PowerShell包含cmdlet来自动化代理。

The following cmdlet will show the SQL Agent Information:

以下cmdlet将显示SQL代理信息：

Get-SqlAgent

获取SqlAgent

The cmdlet shows SQL Agent Information:

该cmdlet显示SQL代理信息：

The ErrorLogFile in PowerShell shows the path of the Agent Log. In SSMS, when you right click on SQL Agent and select properties, you can see the Agent Error Log path. This is a different log file than the SQL Error Log. This Log stores the Agent information:

PowerShell中的ErrorLogFile显示代理日志的路径。在SSMS中，当您右键单击SQL Agent并选择属性时，您可以看到Agent Error Log路径。这是与SQL错误日志不同的日志文件。该日志存储代理信息：

In figure 11, you will see the IdleCpuDuration equal to 600 in PowerShell. That property can be verified in the SQL Server Agent Properties, Advanced page. This information is used to define when the CPU is idle. By default, the CPU is idle when the CPU usage is below 10% per 600 seconds. This property is used when you schedule a job in the schedule type, you can run jobs when the CPU is idle. You can specify this value in the Advance Page:

在图11中，您将在PowerShell中看到IdleCpuDuration等于600。可以在“ SQL Server代理属性”的“高级”页面中验证该属性。此信息用于定义CPU何时空闲。默认情况下，当CPU使用率每600秒低于10％时，CPU处于空闲状态。当您以调度类型调度作业时，将使用此属性，您可以在CPU空闲时运行作业。您可以在“高级页面”中指定此值：

Get-SqlAgent cmdlet shows several properties. If you want to see specific properties, you can combine with the select sentence. The following example shows how to see the ServiceAccount and the ServiceStartMode properties:

Get-SqlAgent cmdlet显示了几个属性。如果要查看特定的属性，可以将其与选择语句结合使用。下面的示例演示如何查看ServiceAccount和ServiceStartMode属性：

Get-SqlAgent | select ServiceAccount, ServiceStartMode

Get-SqlAgent | 选择ServiceAccount，ServiceStartMode

The service Account is the Windows Account used to run the SQL Agent Service. The ServiceStartMode is used to set the way that the service will work. It can run manually, automatically or it can be disabled:

服务帐户是用于运行SQL代理服务的Windows帐户。 ServiceStartMode用于设置服务的工作方式。它可以手动，自动运行，也可以禁用：

Another popular cmdlet is the Get-SqlAgentJob it is used to show job properties. This cmdlet is used in combination with the Get-SqlAgent:

另一个流行的cmdlet是Get-SqlAgentJob，用于显示作业属性。此cmdlet与Get-SqlAgent结合使用：

Get-SqlAgent -ServerInstance . | Get-SqlAgentJob

Get-SqlAgent -ServerInstance。 | Get-SqlAgentJob

Get-SqlAgentJob shows properties like the Job’s name, owner, category and if the job is enabled:

Get-SqlAgentJob显示诸如作业名称，所有者，类别以及是否启用了作业的属性：

You can verify in the SSMS in the SQL Server Agent. If you right click and select properties, you will be able to see the syspolicy_purge_history job information:

您可以在SQL Server代理的SSMS中进行验证。如果右键单击并选择属性，您将能够看到syspolicy_purge_history作业信息：

The information in PowerShell in Figure 15, includes the Owner, Category and shows if the job is enabled or not. This information can be verified using the General Page in SSMS:

PowerShell在图15中的信息包括“所有者”，“类别”，并显示作业是否启用。可以使用SSMS中的“常规页面”来验证此信息：

An owner is used to assign permissions to specific roles to edit job properties. By default, the sa (Super Administrator) is the owner of the job. If you want to assign users without System Administrator privileges, you will need to assign the SQL Database user to any of the following roles in the MSDB database:

所有者用于将权限分配给特定角色以编辑作业属性。默认情况下，sa（超级管理员）是作业的所有者。如果要分配没有系统管理员特权的用户，则需要将SQL数据库用户分配给MSDB数据库中的以下任何角色：

SQLAgentUserRole is a role with fewer privileges. It can access to the jobs they own. Do not have access to multiserver jobs.
SQLAgentUserRole是具有较少特权的角色。它可以访问他们拥有的工作。没有访问多服务器作业的权限。
SQLAgentReaderRole has read privileges to all the properties including multiserver jobs.
SQLAgentReaderRole对所有属性（包括多服务器作业）具有读取特权。
SQLAgentOperatorRole is the combination of the roles explained before.
SQLAgentOperatorRole是前面解释的角色的组合。

Job categories are used to classify jobs. You can use existing categories or create your own categories.

作业类别用于对作业进行分类。您可以使用现有类别或创建自己的类别。

Disabling jobs is useful when you need to temporary stop a job.

当您需要临时停止作业时，禁用作业很有用。

PowerShell中的其他cmdlet (Other cmdlets in PowerShell)

It would take a long time to explain all the remaining cmdlets. We present the list of new remaining cmdlets in SQL Server 2016 PowerShell:

解释所有其余的cmdlet将花费很长时间。我们在SQL Server 2016 PowerShell中列出了新的剩余cmdlet的列表：

结论 (Conclusion)

In this article, we explained some of the main new cmdlets in PowerShell included in SQL Server 2016. If you have questions about this topic, do not hesitate to write your comments.

在本文中，我们解释了SQL Server 2016中包含的PowerShell中一些主要的新cmdlet。如果对本主题有疑问，请随时编写注释。

翻译自: https://www.sqlshack.com/new-powershell-cmdlets-sql-2016/

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL Server 2016中的新PowerShell Cmdlet

介绍 (Introduction) PowerShell is Windows Shell that can be used to automate tasks in Windows, Exchange, Azure, SQL Server and more. PowerShell是Windows Shell，可用于自动执行Windows，Exchange， Azure ， SQL ...
复制链接

扫一扫