This morning the SQL Server Engineering “Tiger” team announced the public preview of the new SQL Assessment API. This new SQL Assessment API has been launched with two new SQL PowerShell cmdlets to help you leverage it. You can get these new cmdlets by simply updating to the latest version of the SqlServer PowerShell module.
Update-Module SqlServer
The SQL Assessment cmdlets come with over 40 pre-configured Best Practices checks that can be run against both Windows & Linux SQL Server instances. These checks are based on the Open Source BPCheck project available in GitHub, and they automatically adjust and run only the checks which are relevant to the version of SQL Server you are running. Even better, you can build your own customized checks tailored to your specific environment, and include them in the SQL Assessment checks when they are run. Furthermore, you can even setup profiles of which checks you want to have run (you don’t have to accept all of the defaults). I highly recommend you checkout the official documentation on this new topic: https://docs.microsoft.com/sql/sql-assessment-api/sql-assessment-api-overview
My favorite check so far is the one that tells me if my SQL Servers are up to date with the latest available CU. Even better, I can leverage my Registered Servers file or Central Management Server to run that check against all the SQL Server Instances across my whole environments with just a few lines of PowerShell code!
dir 'SQLSERVER:SQLRegistrationDatabase Engine Server Group' | WHERE { $_.Mode -ne 'd'} | foreach { Get-SqlInstance -ServerInstance $_.Name | Invoke-SqlAssessment } | Out-GridView
NOTE: Please keep in mind that the SQL PowerShell above is not really what you would call a ‘Best Practice‘. It’s just meant as a way to easily illustrate what is possible. This is not quite how the code would look if I were wanting to run this across my entire environment, but it gets the point across for this demonstration.
These two new cmdlets are being presented as a ‘preview’ which means they could still change a little based on real-world customer feedback from people like you. While I have been testing out the usability of these cmdlets for several months now, I didn’t have access to a real-world SQL Server environment to test them out against. With that said, I’m sure there are plenty more improvements to be made, and I hope you’ll speak up and let the team know what to do by submitting ideas via aka.ms/sqlfeedback. I can’t wait to see how these cmdlets evolve now that you’re able to use them!
Here’s the full text of what that Message highlighted in the screenshot above says: “Product version 13.0.1742 is not the latest available. We recommend keeping your SQL Server up to date and install Service Packs and Cumulative Updates as they are released.”
