{"id":86235,"date":"2019-08-21T03:00:51","date_gmt":"2019-08-21T11:00:51","guid":{"rendered":"http:\/\/devblogs.microsoft.com\/scripting\/?p=86235"},"modified":"2019-08-22T05:55:39","modified_gmt":"2019-08-22T13:55:39","slug":"executing-sql-server-assessments-from-powershell","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/executing-sql-server-assessments-from-powershell\/","title":{"rendered":"Executing SQL Server Assessments from PowerShell"},"content":{"rendered":"

Summary<\/strong>: Using the SQLServer module cmdlets to review and monitor SQL Server instance and database configuration<\/p>\n

Q: Hey, Doctor Scripto!<\/p>\n

I have loads of SQL Servers in my area of responsibility and I know they all need certain configuration settings but I’m never confident that they are all set just right so I spend a long time every month visiting them all over RDP to give reassure myself. How can I automate this work?<\/p>\n

\u2014AB<\/p>\n

A: Hello AB, I know the very person that can answer that question for you. It’s my good friend Jonathan Allen. Let me introduce him to you.<\/p>\n

He’s a SQL Server PFE from the UK. He also speaks at and attends PowerShell and Data and AI conferences in the UK and Europe.<\/p>\n

As a side note \u2013 He help runs the SQLBits conference once a year. Take it away Jonathan!<\/p>\n

Thanks, Doctor Scripto!<\/p>\n

To solve this challenge you need to take a look at SQL Server Assessments, the latest thing in the SQL Server PowerShell module \u2013 available here on the PowerShell Gallery<\/a>.<\/p>\n

How do we get the module?<\/h4>\n

As the SQLServer module is published on the PowerShell Gallery it is available right at your command line. Check if you have SQLServer already available with this command and check the Version information in the results<\/p>\n

Get-Module SQLServer <\/strong><\/p>\n

If you don\u2019t see any results or you see any version older than 21.1.18147 then you need to run<\/p>\n

# get the latest (possibly prerelease) version of SQLServer module\nUpdate-Module sqlserver -RequiredVersion 21.1.18147 -Scope CurrentUser<\/strong><\/p>\n

(We use -Scope CurrentUser to avoid having to run this as Administrator)<\/p>\n

Let’s check that we have the two commands we need by listing the commands in the module with<\/p>\n

Get-Command -Module SqlServer -Name *sqlassessment*<\/strong><\/p>\n

This should give us the results of:<\/p>\n\n\n\n\n\n\n
CommandType<\/td>\nName<\/td>\nVersion<\/td>\nSource<\/td>\n<\/tr>\n
———-<\/td>\n—-<\/td>\n——-<\/td>\n——<\/td>\n<\/tr>\n
Cmdlet<\/td>\nGet-SqlAssessmentItem<\/td>\n21.1.18147<\/td>\nSqlServer<\/td>\n<\/tr>\n
Cmdlet<\/td>\nInvoke-SqlAssessment<\/td>\n21.1.18147<\/td>\nSqlServer<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Now that we have the cmdlets we need, let’s take a look at what they do, first of all let’s see the what Get-SQLAssessmentItem does. I have a SQL Server 2014 test instance on my laptop so I’ll use that<\/p>\n

# see what can be in the instance assessment\nset-location SQLSERVER:\\SQL\\localhost\\sql2014\nGet-SqlAssessmentItem <\/strong><\/p>\n

We get 45 results that point to instance level features such as TraceFlags usage, Deprecated Features, Extended Events sessions, update status, plan use and more.<\/p>\n

We can see some of the assessments done on databases by running<\/p>\n

# see what can be in the database assessment\nSet-Location SQLSERVER:\\SQL\\localhost\\sql2014\\databases\nGet-SqlDatabase | Get-SqlAssessmentItem | Group-Object<\/strong><\/p>\n

Here we see properties relating to Statistics, Query Store, Indexes, Constraints etc.<\/p>\n

So, how do we actually run an assessment then?<\/h4>\n

Still working against the test instance on my laptop, we can run an assessment with this command<\/p>\n

# running an assessment\n$SQLInstance = “$ENV:COMPUTERNAME\\SQL2014”\nGet-SqlInstance -ServerInstance $SQLInstance | Invoke-SqlAssessment<\/strong><\/p>\n

This will give us a lot of results (at least my test instance did!) but they all share the same properties -we have CheckId, CheckName, HelpLink, Message, Severity, TargetPath, TargetType. Here’s one result with example values for each property<\/p>\n\n\n\n\n\n\n\n\n\n
CheckId<\/td>\nSqlServer.Server.PlansUseRatio<\/td>\n<\/tr>\n
CheckName<\/td>\nAmount of single use plans in cache is high<\/td>\n<\/tr>\n
HelpLink<\/td>\nhttps\/\/docs.microsoft.com\/sql\/database-engine\/configure-windows\/optimize-for-ad-hoc-workloads-server-configuration-option<\/td>\n<\/tr>\n
Message<\/td>\nAmount of single use plans in cache is high (66%). Consider enabling the Optimize for ad hoc workloads setting on heavy OLTP ad-hoc workloads to conserve resources.<\/td>\n<\/tr>\n
Severity<\/td>\nWarning<\/td>\n<\/tr>\n
TargetPath<\/td>\nServer[@Name=[ComputerName]<\/em>\\SQL2014′]<\/td>\n<\/tr>\n
TargetType<\/td>\nServer<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Now, getting a stream of results on your console from one computer is hard to analyse, let alone if you run this against multiple servers so let\u2019s look at collecting information into a management database from multiple instances. Let’s create a database on our test server<\/p>\n

# step 1 – we need a database\n$qry =\n@’\ncreate database SQLAssessmentDemo\ngo\n‘@<\/strong><\/p>\n

#step 2 – what instance are we storing the data in\n$SQLManagementInstance = “$ENV:COMPUTERNAME\\SQL2019”\nInvoke-Sqlcmd -ServerInstance $SQLManagementInstance -Database ‘master’ -Query $qry<\/strong><\/p>\n

Now we can use that database to store the output from Write-SQLTableData like this<\/p>\n

$SQLManagementInstance = “$ENV:COMPUTERNAME\\SQL2019”\nGet-SqlInstance -ServerInstance $SQLInstance |\nInvoke-SqlAssessment |\nWrite-SqlTableData -ServerInstance $SQLManagementInstance -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force<\/strong><\/p>\n

Get-SqlDatabase -ServerInstance $SQLInstance |\nWhere-Object status -eq ‘normal’ |\nInvoke-SqlAssessment |\nWrite-SqlTableData -ServerInstance $SQLManagementInstance -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force<\/strong><\/p>\n

This creates a table called Results in the Assessment schema in our SQLAssessmentDemo database and places the results of the Instance assessment and the database assessment in there.<\/p>\n

We need to alter the Results table just a little to make it easier to analyse the collected data and keep it as a live record of the SQL Server assessments that we run by adding a CollectionDate column.<\/p>\n

$SQLManagementInstance = “$ENV:COMPUTERNAME\\SQL2019”\n$qry =\n@’\nALTER TABLE ASSESSMENT.Results\nADD CollectionDate datetime not null default cast(getdate() as varchar(17))\n‘@<\/strong><\/p>\n

Invoke-Sqlcmd -ServerInstance $SQLManagementInstance -Database ‘SQLAssessmentDemo’ -Query $qry<\/strong><\/p>\n

This makes sure that each time we run an assessment the results are stamped with the date and time that the collection was run and we can then track changes in our servers or make sure that remediation work is progressing as we want it to.<\/p>\n

Once that column is added then we can run the assessment against multiple servers as and when we want to with this<\/p>\n

# multiple server collection\n$SQLManagementInstance = “$ENV:COMPUTERNAME\\SQL2019”\n$Instances = @(“$ENV:COMPUTERNAME\\SQL2014”, “$ENV:COMPUTERNAME\\SQL2016”, “$ENV:COMPUTERNAME\\SQL2019”)\nforeach ($SQLInstance in $Instances) {\n#instances\nWrite-Output “Assessing $SQLInstance”\nGet-SqlInstance -ServerInstance $SQLInstance |\nInvoke-SqlAssessment |\nWrite-SqlTableData -ServerInstance $SQLManagementInstance -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force<\/p>\n

#databases\nGet-SqlDatabase -ServerInstance $SQLInstance |\nWhere-Object status -eq ‘normal’ |\nInvoke-SqlAssessment |\nWrite-SqlTableData -ServerInstance $SQLManagementInstance -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force\n}<\/strong><\/p>\n

Once this runs successfully then we can step into SQL Server Management Studio or PowerBI and start to review \/ analyse our Results table to see where we need to take action first.<\/p>\n

Where can I get more information?<\/h4>\n

Introducing SQL Assessment API (Public Preview)\nhttps:\/\/techcommunity.microsoft.com\/t5\/SQL-Server\/Introducing-SQL-Assessment-API-Public-Preview\/ba-p\/778570<\/a><\/p>\n

Github API\nhttps:\/\/github.com\/microsoft\/sql-server-samples\/tree\/master\/samples\/manage\/sql-assessment-api<\/a><\/p>\n

Install SQL Server PowerShell module\nhttps:\/\/docs.microsoft.com\/en-us\/sql\/powershell\/download-sql-server-ps-module?view=sql-server-2017<\/a><\/p>\n

So, AB that is all there is to starting to use SQL Server Assessment cmdlets to monitor your SQL Server estate.<\/p>\n

I invite you to follow me on Twitter<\/a> and Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a>, or post your questions on the Official Scripting Forum<\/a>. See you tomorrow. Until then, peace.<\/p>\n

Your good friend, Doctor Scripto<\/p>\n

PowerShell, Doctor Scripto, Jonathan Allen, SQL Server<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Summary: Using the SQLServer module cmdlets to review and monitor SQL Server instance and database configuration Q: Hey, Doctor Scripto! I have loads of SQL Servers in my area of responsibility and I know they all need certain configuration settings but I’m never confident that they are all set just right so I spend a […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1739,1738],"tags":[1740,1854,377,176],"class_list":["post-86235","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-doctor-scripto","category-powershell","tag-doctor-scripto","tag-jonathan-allen","tag-powershell","tag-sql-server"],"acf":[],"blog_post_summary":"

Summary: Using the SQLServer module cmdlets to review and monitor SQL Server instance and database configuration Q: Hey, Doctor Scripto! I have loads of SQL Servers in my area of responsibility and I know they all need certain configuration settings but I’m never confident that they are all set just right so I spend a […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/86235","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=86235"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/86235\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=86235"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=86235"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=86235"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}