With the latest update to SQL Server 2016 SSMS there are some new interesting features added to the SQL Server Powershell making managing SQL Server through Powershell more easier and efficient.
The Detailed information on the addition can be found in the below link,
https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/
In this blog we are going to see how we can add this new module to our existing software and we are going to look at one interesting CMDLET "Get-SQLErrorlog"
Lets get started with updating the SSMS, Download the latest SSMS from the below link irrespective of if you want to install a new instance of SSMS or upgrade the existing instance
https://msdn.microsoft.com/en-us/library/mt238290.aspx
The Install or upgrade should be pretty straight forward, once the installation is complete verify your SSMS Version 13.0.15700.xx and above will have the new SQL Server module.
As mentioned in the Above SQL Server Team Blog (https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/)
"The SQL PowerShell module that ships with SSMS has changed from SQLPS to SqlServer (there is no change to the module used by SQL Agent). This means that if you have a PowerShell script doing Import-Module SQLPS, it will need to be changed to be Import-Module SqlServer in order to take advantage of the new provider functionality and new CMDLETs. The new module will be installed to “%Program Files\WindowsPowerShell\Modules\SqlServer” and hence no update to $env:PSModulePath is required"
So Now lets go and check if the SSMS update has installed the new module
There you go, Now we have the new SQLServer Module installed, Let check it out as i mentioned before I am going use Get-SQLErrorlog CMDLET in the SQLServer Module for the testing
More about Get-SQLErrorlog @ https://technet.microsoft.com/en-us/library/mt759806.aspx
There you Go...We can get the errorlog as simple as that!
Now Let see how we can play with it on Powershell Console, I use PowerGUI as my powershell editor but you can use Windows native editor or any other 3rd party editors for Powershell,
As a First step I try to Import the new module SQLServer but it throws an error as shown in the below image, this is because most of you would have SQLPS module already loaded in the PSenvironment which conflicts with the SQLServer module.
All you need to do now is remove the SQLPS module and add the new SQLServer module as shown in the below image, you can now see the new CMDLETS in the SQLServer module which you can now work with.
Ok, Now I want to select first 10 rows from my SQL Server errorlog, lets see how it can done using powershell...
Last I want to see all the errors in my errorlog last 24 hours, lets see how we can do it,
We can now monitor SQL errorlog in various ways using powershell with this new CMDLET.
In further articles we can see how we can use other CMDLETS in the new Powershell SQLServer module.
The Detailed information on the addition can be found in the below link,
https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/
In this blog we are going to see how we can add this new module to our existing software and we are going to look at one interesting CMDLET "Get-SQLErrorlog"
Lets get started with updating the SSMS, Download the latest SSMS from the below link irrespective of if you want to install a new instance of SSMS or upgrade the existing instance
https://msdn.microsoft.com/en-us/library/mt238290.aspx
The Install or upgrade should be pretty straight forward, once the installation is complete verify your SSMS Version 13.0.15700.xx and above will have the new SQL Server module.
As mentioned in the Above SQL Server Team Blog (https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/)
"The SQL PowerShell module that ships with SSMS has changed from SQLPS to SqlServer (there is no change to the module used by SQL Agent). This means that if you have a PowerShell script doing Import-Module SQLPS, it will need to be changed to be Import-Module SqlServer in order to take advantage of the new provider functionality and new CMDLETs. The new module will be installed to “%Program Files\WindowsPowerShell\Modules\SqlServer” and hence no update to $env:PSModulePath is required"
So Now lets go and check if the SSMS update has installed the new module
There you go, Now we have the new SQLServer Module installed, Let check it out as i mentioned before I am going use Get-SQLErrorlog CMDLET in the SQLServer Module for the testing
More about Get-SQLErrorlog @ https://technet.microsoft.com/en-us/library/mt759806.aspx
There you Go...We can get the errorlog as simple as that!
Now Let see how we can play with it on Powershell Console, I use PowerGUI as my powershell editor but you can use Windows native editor or any other 3rd party editors for Powershell,
As a First step I try to Import the new module SQLServer but it throws an error as shown in the below image, this is because most of you would have SQLPS module already loaded in the PSenvironment which conflicts with the SQLServer module.
All you need to do now is remove the SQLPS module and add the new SQLServer module as shown in the below image, you can now see the new CMDLETS in the SQLServer module which you can now work with.
Ok, Now I want to select first 10 rows from my SQL Server errorlog, lets see how it can done using powershell...
Last I want to see all the errors in my errorlog last 24 hours, lets see how we can do it,
We can now monitor SQL errorlog in various ways using powershell with this new CMDLET.
In further articles we can see how we can use other CMDLETS in the new Powershell SQLServer module.
