Daniel Calbimonte

New PowerShell Cmdlets in SQL Server 2016

August 18, 2016 by

Introduction

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

Cmdlets are commands used in PowerShell.

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

There are 4 new areas where cmdlets were included:

  • SQL Server Logs

  • SQL Server Agent

  • Azure SQL

  • Always Encrypted

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

Requirements

  1. SQL Server 2016 Installed.

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:


Figure 0 SQL Server Logs

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:


Figure 1. Log File Messages

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


Figure 2. Start PowerShell in SSMS

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


Figure 3. Starting SQL PowerShell

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

Get-sqlerrorlog

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


Figure 4. All the SQL Error Log Entries

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:

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


Figure 5. Using the where-object filter

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:

-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

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:

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

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


Figure 6. Filtering dates using PowerShell

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:


Figure 7. Configure the number of Error Logs

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


Figure 8. Limit of error log files

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

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

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


Figure 9. Verifying the limit of error logs

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


Figure 10. Limit of error log files changed

SQL Agent

PowerShell now includes cmdlets to automate the Agent.

The following cmdlet will show the SQL Agent Information:

Get-SqlAgent

The cmdlet shows SQL Agent Information:


Figure 11. Agent Information

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:


Figure 12. Agent Log File Path in General Page

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:


Figure 13. CPU conditions

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 | select 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:


Figure 14. Selecting specific SQL Agent Properties

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

Get-SqlAgent -ServerInstance . | Get-SqlAgentJob

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


Figure 15. SQL Job Properties in PowerShell

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:


Figure 16. SQL Job Properties

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:


Figure 17. Job Properties in 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:

  • SQLAgentUserRole is a role with fewer privileges. It can access to the jobs they own. Do not have access to multiserver jobs.

  • SQLAgentReaderRole has read privileges to all the properties including multiserver jobs.

  • SQLAgentOperatorRole is the combination of the roles explained before.

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.

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:

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.

References

For more information, refer to these links.


Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte
SQL Commands, SQL Server 2016

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

984 Views