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
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:
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:
We can now check the SQL Error Log File using PowerShell. On any node, right click and select Start PowerShell:
The module used to be SQLPS and now the module name is SQLServer:
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:
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*’}
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:
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:
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:
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:
As you can see, PowerShell changed the number of Error Logs to 8:
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:
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:
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:
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:
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:
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:
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:
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.
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 writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023