Problem
PowerShell is an important Windows shell which can communicate with SQL Server to help automate and perform several DBA tasks. Windows PowerShell supports more complex logic than T-SQL scripts, giving SQL Server administrators the ability to build robust administration scripts. In SQL Server 2016 there are some significant changes along with new cmdlets and in this tip we will explore a few of these new PowerShell cmdlets.
Solution
As you may know, with SQL Server 2016, SQL Server Management Studio is no longer a part of the standard SQL Server installation and it needs to be separately downloaded. In the first monthly update of SQL Server Management Studio (SSMS) (Version number 13.0.15500.91) since the SQL Server 2016 release, Microsoft has made several changes to PowerShell for SQL Server.
List PowerShell Modules for SQL Server
Before we move further, let’s see the modules available with previous versions of SQL Server 2016 using the below query.
Get-Module -ListAvailable -Name SQL*
After I downloaded SQL Server 2016 Management Studio version 13.0.155900.91, I ran the command again.
As we can see above, there is a new PowerShell module “SQLSERVER” in SQL Server 2016 as compared to previous versions of SQL Server.
SQL Server PowerShell components are included with the SSMS installer as well as SQL Server engine installation. SQL Server Management Studio will be regularly updated by Microsoft. so in order to provide regular updates Microsoft has changed the SQL PowerShell module from SQLPLS to SQLSERVER. The SQL PowerShell module that ships with SSMS has changed from SQLPS to SqlServer (there is no change to the module used by SQL Server Agent). SSMS has been updated to integrate with SQLTOOLSPS.exe rather than SQLPS.exe. When we launch PowerShell from SSMS it will automatically launch a session with the new PowerShell module.
This is an important step towards separating the components from the existing server component set up and can be updated separately without the need for updating the whole server instance.
Now along with this change, there are several new cmdlets as well. To see the cmdlets in the SQLSERVER module run the following command.
get-command -module SQLSERVER
It will list all cmdlets for the module SQLSERVER.
In this tip we will focus on these PowerShell cmdlets for SQL Server:
- Get-SqlErrorLog
- Set-SqlErrorLog
PowerShell cmdlet Get-SqlErrorLog
This is used to get the details from the SQL Server logs. We can filter the error logs with text, dates, etc.
We can get the property and definition of the Get-sqlErrorLog as follows:
Get-Command | Where-Object {$_.Name -eq"Get-SqlErrorLog"} | Format-List *
Now let’s explore this cmdlet with different examples.
1. Get total number of events in the error logs
Get-SqlErrorLog -ServerInstance localhost\sql2016 | measure
We will get the total number of events in the error logs from all error logs files.
2. Filter error logs
Here is an example where we look for the word “login”.
Get-SqlErrorLog | Where-Object { $_.text -like "*Login*"} | Out-Grid View
Out-Grid View parameter opens a new grid view window to easily view the logs. If we want to see the events in the cmd prompt only remove this keyword.
Similarly if we want to filter out with keyword “SQL Server”, run the below command.
Get-SqlErrorLog | Where-Object { $_.text -like "*SQL Server*"} | Out-GridView
To filter out the logs between two dates we can use the before and after parameter.
Get-SqlErrorLog -After "2016/09/25" -Before "2016/10/01" | Where-Object { $_.text -like "*SQL Server*"} | Out-GridView
To see error logs after a particular date we can run the below command.
Get-sqlerrorlog | Where-Object {$_.Text -like ‘*SQL Server*’ -and $_.Date -gt ‘10/01/2016’}| Out-GridView
PowerShell cmdlet Set-SqlErrorLog
We can configure the number of error logs using this command. The oldest files are recycled whenever a new log file is created. In SSMS, we can change the default number by right clicking on SQL Server Logs and select Configure.
We can enable and select the number of SQL Server error log files between 6 to 99.
We can get the property and definition of the Get-sqlErrorLog as follows:
Get-Command | Where-Object {$_.Name -eq"Set-SqlErrorLog"} | Format-List *
To limit this using PowerShell, return to the PowerShell command line and write the following:
Set-SqlErrorLog -MaxLogCount 10
This will set maximum number of error log files to 10.
We can now go to the SQL Server error log configuration properties and verify this.
If we want to generate a script to set the maximum number of error log files to 12, run the below command.
Set-SqlErrorLog -MaxLogCount 12 -Script
This will create the T-SQL code to make the change as shown below.
Summary
It is very helpful to have these 2 new cmdlets in PowerShell. Explore these PowerShell cmdlets and make use of them. In the next tip, I will explore more cmdlets.
Note: If you have a PowerShell script doing Import-Module SQLPS, it will need to be changed to Import-Module SqlServer in order to take advantage of the new provider functionality and new cmdlets.
Next Steps
- Download and explore SQL Server 2016
- Check out SQL Server 2016 tips
- Read more about SQL Server Management Studio with Windows PowerShell Cmdlets
- Read more about SQL Server PowerShell tips
