SQLServer2016中的新PowerShellCmdlet

介绍 (Introduction)

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

PowerShell是Windows Shell&＃xff0c;可用于自动执行Windows&＃xff0c;Exchange&＃xff0c; Azure &＃xff0c; 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.

在这篇新文章中&＃xff0c;我们将讨论SQL Server 2016中PowerShell中的新cmdlet。

There are 4 new areas where cmdlets were included:

包含cmdlet的有4个新区域&＃xff1a;

• 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.

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

要求 (Requirements)

1. 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&＃xff08;SSMS&＃xff09;中&＃xff0c;可以在管理> SQL Server日志中查看SQL日志文件&＃xff1a;

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:

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

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

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

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

要限制在PowerShell中&＃xff0c;请返回PowerShell命令行并编写以下内容&＃xff1a;

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。要进行验证&＃xff0c;请右键单击“ SQL Server日志”&＃xff0c;然后选择“配置”&＃xff1a;

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中&＃xff0c;您将在PowerShell中看到IdleCpuDuration等于600。 可以在“ SQL Server代理属性”的“高级”页面中验证该属性。 此信息用于定义CPU何时空闲。 默认情况下&＃xff0c;当CPU使用率每600秒低于10&＃xff05;时&＃xff0c;CPU处于空闲状态。 当您以调度类型调度作业时&＃xff0c;将使用此属性&＃xff0c;您可以在CPU空闲时运行作业。 您可以在“高级页面”中指定此值&＃xff1a;

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:

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

• 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对所有属性&＃xff08;包括多服务器作业&＃xff09;具有读取特权。

• 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.

当您需要临时停止作业时&＃xff0c;禁用作业很有用。

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的列表&＃xff1a;

• Add-SqlAzureAuthenticationContext

  Add-SqlAzureAuthenticationContext

• Add-SqlColumnEncryptionKeyValue

  Add-SqlColumnEncryptionKeyValue

• Complete-SqlColumnMasterKeyRotation

  Complete-SqlColumnMasterKeyRotation

• Get-SqlColumnEncryptionKey

  Get-SqlColumnEncryptionKey

• Get-SqlColumnMasterKey

  Get-SqlColumnMasterKey

• Invoke-SqlColumnMasterKeyRotation

  调用SqlColumnMasterKeyRotation

• New-SqlAzureKeyVaultColumnMasterKeySettings

  New-SqlAzureKeyVaultColumnMasterKeySettings

• New-SqlCertificateStoreColumnMasterKeySettings

  New-SqlCertificateStoreColumnMasterKeySettings

• New-SqlCngColumnMasterKeySettings

  New-SqlCngColumnMasterKeySettings

• New-SqlColumnEncryptionKey

  New-SqlColumnEncryptionKey

• New-SqlColumnEncryptionKeyEncryptedValue

  New-SqlColumnEncryptionKeyEncryptedValue

• New-SqlColumnEncryptionSettings

  New-SqlColumnEncryptionSettings

• New-SqlColumnMasterKey

  New-SqlColumnMasterKey

• New-SqlCspColumnMasterKeySettings

  New-SqlCspColumnMasterKeySettings

• Remove-SqlColumnEncryptionKey

  Remove-SqlColumnEncryptionKey

• Remove-SqlColumnEncryptionKeyValue

  Remove-SqlColumnEncryptionKeyValue

• Remove-SqlColumnMasterKey

  删除SqlColumnMasterKey

• Set-SqlColumnEncryption

  Set-SqlColumnEncryption

• Get-SqlAgentJobHistory

  Get-SqlAgentJobHistory

• Get-SqlAgentJobStep

  Get-SqlAgentJobStep

• Get-SqlAgentSchedule

  Get-SqlAgentSchedule

结论 (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.

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

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