{ "metadata": { "kernelspec": { "name": "powershell", "display_name": "PowerShell" }, "language_info": { "name": "powershell", "codemirror_mode": "shell", "mimetype": "text/x-sh", "file_extension": ".ps1" } }, "nbformat_minor": 2, "nbformat": 4, "cells": [ { "cell_type": "markdown", "source": [ "# SQL Server Assessment Tool\n", "\n", "Unlike other notebooks, **do not execute all cells of this notebook!** \n", "\n", "A single assessment may take awhile so fill out the variables and execute the cell that matches the desired environment to perform the assessment needed. Only one of these cells needs to be executed after the variables are defined.\n", "\n", "## Notebook Variables\n", "\n", "| Line | Variable | Description |\n", "| ---- | -------- | ----------- |\n", "| 1 | ServerInstance | Name of the SQL Server instance |\n", "| 2 | Group | (Optional) Name of the server group, if known | " ], "metadata": { "azdata_cell_guid": "86ecfb01-8c38-4a99-92a8-687d8ec7f4b0" } }, { "cell_type": "code", "source": [ "$ServerInstance = \"\"\r\n", "$Group = \"\"" ], "metadata": { "azdata_cell_guid": "db21129e-9bda-4db9-8d61-d2b264a3cad8" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "## Notebook Steps\r\n", "1. Ensure that the proper APIs and modules are installed per the prerequisites notebook\r\n", "2. Define a service instance and group corresponding to the SQL Server instances to be assessed\r\n", "3. Choose an example below that corresponds to the appropriate task\r\n", "4. Execute only that example's code block and wait for results\r\n", "5. Fix any recommended issues and rerun Assessment API until clear" ], "metadata": { "azdata_cell_guid": "541f6806-f8d2-4fc5-a8fb-6d42947d1a64" } }, { "cell_type": "markdown", "source": [ "### Example 1 Get-SqlInstance\r\n", "Pipe the output of the Get-SqlInstance cmdlet to the _Get-SqlAssessmentItem_ cmdlet to pass the instance object to it to perform an assessment on the entire instance. " ], "metadata": { "azdata_cell_guid": "c6f94c36-0566-4963-acb8-4a419758d26e" } }, { "cell_type": "code", "source": [ "Get-SqlInstance -ServerInstance $serverInstance | Get-SqlAssessmentItem" ], "metadata": { "azdata_cell_guid": "420e135e-0190-476b-812d-f716ec619ed3" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "### Example 2 - Get-Item\r\n", "Get a list of available databases in the instance using the _Get-Item_ cmdlet and pipe it to _Get-SqlAssessmentItem_. " ], "metadata": { "azdata_cell_guid": "30bcf8ed-cfd4-4e3b-b634-acdafab13437" } }, { "cell_type": "code", "source": [ "Get-Item SQLSERVER:\\SQL\\$serverInstance\\default | Get-SqlAssessmentItem" ], "metadata": { "azdata_cell_guid": "4525cbe7-719a-4cc3-8ebd-5279731c3979" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "### Example 3 - Get-SqlDatabase\r\n", "Or, use the _Get-SqlDatabase_ cmdlet without specifying a name to get a full list of database objects." ], "metadata": { "azdata_cell_guid": "def37aca-fe6e-48ad-9794-09fd9ba77740" } }, { "cell_type": "code", "source": [ "Get-SqlDatabase -ServerInstance $serverInstance | Get-SqlAssessmentItem" ], "metadata": { "azdata_cell_guid": "da57fa74-86b1-4d2f-a419-4035c10f0d3a" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "### Example 4 - Export Assessment to SQL Table\r\n", "Invoke assessment for the instance and save the results to a SQL table by piping the output of the _Get-SqlInstance_ cmdlet to the _Invoke-SqlAssessment_ cmdlet. The results are piped to the _Write-SqlTableData_ cmdlet. The _Invoke-Assessment_ cmdlet is run with the -**FlattenOutput** parameter in this example. This parameter makes the output suitable for the _Write-SqlTableData_ cmdlet. The latter raises an error if the parameter is omitted." ], "metadata": { "azdata_cell_guid": "aeaa588a-a3a2-4bc3-9b4b-794427b77649" } }, { "cell_type": "code", "source": [ "Get-SqlInstance -ServerInstance $serverInstance |\r\n", "Invoke-SqlAssessment -FlattenOutput |\r\n", "Write-SqlTableData -ServerInstance $serverInstance -DatabaseName SQLAssessmentDemo -SchemaName Assessment -TableName Results -Force" ], "metadata": { "azdata_cell_guid": "28ce8df2-1da8-4462-8e91-62646642d4b1" }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "### Example 5 - SQL Server (SSMS) Locally Registered Servers\r\n", "To gather locally saved SQL registered servers, use the SQL Server PowerShell module installed from the [Pre-requisites notebook](..\\prereqs.ipynb). " ], "metadata": { "azdata_cell_guid": "3a14a231-2ab3-45db-9afa-fbd369f4ec0f" } }, { "cell_type": "code", "source": [ "Import-Module SqlServer\r\n", "$RegisteredServers = Get-ChildItem SQLSERVER:\\SQLRegistration -Recurse | Where-Object {$_.IsLocal -eq $True}\r\n", "$RegisteredServers.Refresh() \r\n", "$RegisteredServers | Format-List -Property *" ], "metadata": { "azdata_cell_guid": "c373d5c0-3eaa-4196-8cd4-2e7d9d1896a1", "tags": [] }, "outputs": [], "execution_count": null }, { "cell_type": "markdown", "source": [ "### Example 5 - Specifying a Central Management Server\r\n", "Many large organizations use [Registered Servers](https://docs.microsoft.com/en-us/sql/ssms/register-servers/register-servers) to organize their networks within editors like SSMS or ADS. To take advantage of running the SQL Server Assessment API against all or a specific Registered Server Group, specify the CMS instance below. \r\n", "\r\n", "![](CMS.png)\r\n", "\r\n", "Take advantage of the [dbatools CMS module](https://dbatools.io/cms/) for this task. " ], "metadata": { "azdata_cell_guid": "cd3b800e-003f-432c-88aa-5738aba11378" } }, { "cell_type": "code", "source": [ "# Get a list of all servers stored on the CMS instance \r\n", "# Note that this list is pipable to all dbatools commands\r\n", "\r\n", "if($SqlInstance -And $Group){\r\n", " Get-DbaRegServer -SqlInstance $SqlInstance -Group $Group\r\n", "}else{\r\n", " Get-DbaRegServer -SqlInstance $SqlInstance\r\n", "}" ], "metadata": { "azdata_cell_guid": "4a7bd412-5099-47b4-a737-455e01b0c172" }, "outputs": [], "execution_count": null } ] }