POWERSHELL -SQL Assessment

June/July 2019 Microsoft – powershell is providing another great feature with SMO and SQL Server module as SQL Assessment.

This is a great feature where Microsoft- SQL Server is providing the assessment for your sql server with general recommendation on the configuration. it would really help and solve most of the problem DBA is having and DBA has to write multiple code and difficult task for them and it is one of the most important activity DBA to perform.

I am very glad to see it. this will make DBA support at next level. used for SQL Server 2012 onwards and works for both SQL Server Windows and Linux

eg:

#To get the Assessment for Named instance ‘.\test1’ :

PS>Get-SqlInstance -ServerInstance ‘.\test1’ | Invoke-SqlAssessment

 

#Database level assement for Named instance ‘.\test1’ – testdb

PS>Get-SqlDatabase -ServerInstance ‘.\test1’ -Database testdb| Invoke-SqlAssessment

 

 

This will provide all recommendation by Microsoft… Cool.

We can import this in table and can work on it.

Get-SqlInstance -ServerInstance ‘.\inst1’ | Invoke-SqlAssessment -FlattenOutput |
Write-SqlTableData -ServerInstance ‘.\inst1’ -DatabaseName ‘db1’ -SchemaName dbo -TableName table_SqlAssessment -Force

Get-SqlDatabase -ServerInstance ‘.\inst1’ -Database db1 | Invoke-SqlAssessment -FlattenOutput |
Write-SqlTableData -ServerInstance ‘.\inst1’ -DatabaseName ‘db1’ -SchemaName dbo -TableName table_SqlAssessment