Skip to content

Instantly share code, notes, and snippets.

@SQLvariant

SQLvariant/SqlAssessment ChecksSQL.ipynb

Created August 18, 2020 01:40
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save SQLvariant/668b6d3ec2b21a3b576e0c3554c1f2d7 to your computer and use it in GitHub Desktop.
Save SQLvariant/668b6d3ec2b21a3b576e0c3554c1f2d7 to your computer and use it in GitHub Desktop.
Download ZIP
PowerShell Notebook for working with the Get-SqlAssessmentItem & Invoke-SqlAssessment cmdlets from the SqlServer module.
Display the source blob
Display the rendered blob
Raw
SqlAssessment ChecksSQL.ipynb
{
"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": [
"<img src=\"https://raw.githubusercontent.com/SQLvariant/sqlworkshops/Notebooks-demo-ThrowAway/graphics/Tiger%20Logo%201.png\" width=\"10.65%\">\r\n",
"<img src=\"https://github.com/Microsoft/azuredatastudio/blob/master/samples/notebookSamples/Graphics/AzureDataStudioLogo.png?raw=true\" width=\"10%\">\r\n",
"\r\n",
"# This PowerShell Notebook will walk you through how to run the SQL Assesment cmdlets against one or many Instances of SQL Server"
],
"metadata": {
"azdata_cell_guid": "49ec53cf-4177-4191-9c7e-c24bcd471c0e"
}
},
{
"cell_type": "markdown",
"source": [
"Areas covered in this Notebook\r\n",
"+ Database-level recommendations\r\n",
"+ Get all checks available for an object:\r\n",
"+ Saving the results of your Checks to a table\r\n",
"+ Customizing rules"
],
"metadata": {
"azdata_cell_guid": "69f550bb-a733-4b87-a85c-05b4976d1f68"
}
},
{
"cell_type": "markdown",
"source": [
"First things first, you need to have the latest version of the SqlServer module installed."
],
"metadata": {
"azdata_cell_guid": "bf3894db-38b1-45ed-8ab9-add0687736a2"
}
},
{
"cell_type": "code",
"source": [
"Import-Module SqlServer"
],
"metadata": {
"azdata_cell_guid": "248623e4-02cf-4850-8fad-f239629cf784"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": ""
}
],
"execution_count": 1
},
{
"cell_type": "markdown",
"source": [
"For our fitst example, the following 6 statements will all give recommendations for local default instance. Use whichever option best fits your coding style."
],
"metadata": {
"azdata_cell_guid": "ef30d342-fc99-443a-afa0-ea0b519eb91f"
}
},
{
"cell_type": "code",
"source": [
"#Option 1\r\n",
"Get-SqlInstance -ServerInstance 'localhost' | Invoke-SqlAssessment\r\n",
"\r\n",
"#Option 2\r\n",
"$serverInstance = Get-SqlInstance -ServerInstance 'localhost'\r\n",
"Invoke-SqlAssessment $serverInstance\r\n",
"\r\n",
"#Option 3\r\n",
"Get-Item SQLSERVER:\\SQL\\localhost\\default | Invoke-SqlAssessment\r\n",
"\r\n",
"#Option 4\r\n",
"Invoke-SqlAssessment SQLSERVER:\\SQL\\localhost\\default\r\n",
"\r\n",
"#Option 5\r\n",
"cd SQLSERVER:\\SQL\\localhost\\default\r\n",
"Invoke-SqlAssessment -Verbose\r\n",
"\r\n",
"#Option 6\r\n",
"cd SQLSERVER:\\SQL\\localhost\r\n",
"Get-Item default | Invoke-SqlAssessment"
],
"metadata": {
"azdata_cell_guid": "7cd29bfb-d878-4ca0-8fce-9684632a8304"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"When everything is ok with the instance being checked, Invoke-SqlAssessment cmdlet is silent. To get some output use -Verbose common parameter:"
],
"metadata": {
"azdata_cell_guid": "a6eefded-6c1d-4a49-a313-bcfefe528bb0"
}
},
{
"cell_type": "code",
"source": [
"Invoke-SqlAssessment SQLSERVER:\\SQL\\localhost\\default -Verbose \r\n",
"#VERBOSE: No recommendations for [localhost\\instance1]."
],
"metadata": {
"azdata_cell_guid": "e1b504ef-ac84-45a6-aff8-7069d6c45457"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"These commands give recommendations for all instances on localhost:"
],
"metadata": {
"azdata_cell_guid": "ca1fe4c1-084c-4534-8c4b-5b4726665ba7"
}
},
{
"cell_type": "markdown",
"source": [
"## Database-level Recommendations\r\n",
"There are 3 different option to get recommendations for a database:"
],
"metadata": {
"azdata_cell_guid": "42b836ec-c358-476b-8ec4-94702f04265a"
}
},
{
"cell_type": "code",
"source": [
"#Option 1\r\n",
"$database = Get-SqlDatabase -ServerInstance 'localhost' -Name master\r\n",
"Invoke-SqlAssessment $database -Verbose\r\n",
"\r\n",
"#Option 2\r\n",
"Invoke-SqlAssessment SQLSERVER:\\SQL\\localhost\\default\\Databases\\master\r\n",
"\r\n",
"#Option 3\r\n",
"cd SQLSERVER:\\SQL\\localhost\\default\\Databases\\master\r\n",
"Invoke-SqlAssessment"
],
"metadata": {
"azdata_cell_guid": "21d4106f-7409-40b3-96a5-a1d04c955430"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Get recommendations for all databases on local instance:"
],
"metadata": {
"azdata_cell_guid": "c95bf482-84ad-4605-be84-bf983be99ad5"
}
},
{
"cell_type": "code",
"source": [
"Get-SqlDatabase -ServerInstance 'localhost' | Invoke-SqlAssessment -Verbose"
],
"metadata": {
"azdata_cell_guid": "f423667a-fbb4-46f0-8d32-5c6b1c7afafc"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Get all checks available for an object:"
],
"metadata": {
"azdata_cell_guid": "3c799e07-c255-448c-881d-3292daeb6d90"
}
},
{
"cell_type": "code",
"source": [
"$serverInstance = Get-SqlInstance -ServerInstance 'localhost'\r\n",
"Get-SqlAssessmentItem $serverInstance | Select Name, Description | Format-Table"
],
"metadata": {
"azdata_cell_guid": "9ea45791-685b-41f4-b0df-d7ec1b2a4cfa"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Run only somee of the checks available for an objects\r\n",
"There are three different options to allow you to Select only the checks you are interested in, and run them against the object:"
],
"metadata": {
"azdata_cell_guid": "50d73d6e-0f3e-4d9b-8cfd-3e5133ce0350"
}
},
{
"cell_type": "code",
"source": [
"#Option #1\r\n",
"$serverInstance = Get-SqlInstance -ServerInstance 'localhost'\r\n",
"$checks = Get-SqlAssessmentItem $serverInstance | Select Name, Description | Out-GridView -PassThru\r\n",
"Invoke-SqlAssessment $serverInstance -Check $checks\r\n",
"\r\n",
"#Option 2\r\n",
"cd SQLSERVER:\\SQL\\localhost\\default\r\n",
"$checks = Get-SqlAssessmentItem | Select Name, Description | Out-GridView -PassThru\r\n",
"Invoke-SqlAssessment -Check $checks\r\n",
"\r\n",
"#Add a check by its name:\r\n",
"Invoke-SqlAssessment -Check $checks,\"TF634\" -Verbose"
],
"metadata": {
"azdata_cell_guid": "c32b0c4d-49df-4062-898d-d6716e242640"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "code",
"source": [
"#Get recommendations for all instances with names ending with numbers:\r\n",
"dir SQLSERVER:\\SQL\\localhost | Where { $_.Name -Match '.*\\d+' } | Invoke-SqlAssessment\r\n",
""
],
"metadata": {
"azdata_cell_guid": "24f1d506-62ea-487b-a51d-5398173aec9b"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "code",
"source": [
"#Option 1\r\n",
"dir SQLSERVER:\\SQL\\localhost | Invoke-SqlAssessment\r\n",
"#Option 2\r\n",
"$instances = dir SQLSERVER:\\SQL\\localhost\r\n",
"Invoke-SqlAssessment $instances"
],
"metadata": {
"azdata_cell_guid": "edbe8ebf-7346-49f1-b23d-5bf9f55657d7"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "code",
"source": [
"dir 'SQLSERVER:\\SQLRegistration\\Database Engine Server Group' | \r\n",
"WHERE { $_.Mode -ne 'd'} | \r\n",
"foreach {\r\n",
" Get-SqlInstance -ServerInstance $_.Name -Credential (Get-Credential sa)\r\n",
" }"
],
"metadata": {
"azdata_cell_guid": "95e49ee0-fe61-4bc0-abb5-dbe6c44e5c72"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "code",
"source": [
"Get-SqlInstance -ServerInstance 'localhost,10002' -Credential (Get-Credential sa) | \r\n",
"Invoke-SqlAssessment"
],
"metadata": {
"azdata_cell_guid": "add669f3-25a2-456e-9006-a972d7648bb6"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Saving the results of your Checks to a table\r\n",
"\r\n",
"In some cases it may take a while to test & deploy the changes recommended by the SQL Assessment cmdlets. It is also sometimes handy to know when a recommendation was first detected. For those and other reasons, it can be handy to save off the results of the SQL Assessment cmdlets to a table. This can be easily accomplished by simply piping the results of the SQL Assessment cmdlet to the `Write-TableTableData` cmdlet (included in the `SqlServer` module). "
],
"metadata": {
"azdata_cell_guid": "757f01b2-09a9-4adb-8f9e-e2ac1c4ce6fc"
}
},
{
"cell_type": "code",
"source": [
"$SQLCred02 = Get-Credential sa\r\n",
"Get-SqlInstance -ServerInstance 'localhost,10002' -Credential $SQLCred02 | \r\n",
"Invoke-SqlAssessment |\r\n",
"Write-SqlTableData -ServerInstance 'localhost,10002' -Credential $SQLCred02 -DatabaseName BlankDB -SchemaName SQLAssessment -TableName Results -Force"
],
"metadata": {
"azdata_cell_guid": "3bd8dc94-384f-40fa-84ca-7f15e0dd8d10"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## Customizing rules\r\n",
"\r\n",
"Rules are designed to be customizable and extensible. Microsoft's ruleset is designed to work for most environments. However, it's impossible to have one ruleset that works for every single environment. Users can write their own JSON files and customize existing rules or add new ones. Examples of customization and complete Microsoft released ruleset are available in the [samples repository](https://aka.ms/sql-assessment-api). For more details on how to run the SQL Assessment cmdlets with custom JSON files, use the Get-Help cmdlet.\r\n",
"\r\n",
"### Options available with rule customization feature\r\n",
"\r\n",
"#### Enabling/disabling certain rules or groups of rules (using tags)\r\n",
"\r\n",
"You can silence specific rules when they aren't applied to your environment or until scheduled work is done to rectify the issue.\r\n",
"\r\n",
"#### Changing threshold parameters\r\n",
"\r\n",
"Specific rules have thresholds that are compared against the current value of a metric to find out an issue. If the default thresholds don't fit, you can change them.\r\n",
"\r\n",
"#### Adding more rules written by you or third parties\r\n",
"\r\n",
"You can string together rulesets by adding one or more JSON files as parameters to your SQL Assessment API call. Your organization might write those files or obtain them from a third party. For example, you can have your JSON file that disables specific rules from the Microsoft ruleset, and another JSON file by an industry expert that include rules you find useful for your environment, followed by another JSON file that changes some threshold values in that JSON file.\r\n",
""
],
"metadata": {
"azdata_cell_guid": "6ac5c2de-75d4-4071-a4f8-dfa3cd33c2df"
}
},
{
"cell_type": "markdown",
"source": [
"### Customization demo"
],
"metadata": {
"azdata_cell_guid": "e3fc8756-6e8c-4d2a-95f6-3d2c48285dd5"
}
},
{
"cell_type": "code",
"source": [
"###!!! parameter name \"profile\" has changed to \"configuration\"\r\n",
"# disable TF 634\r\n",
"Invoke-SqlAssessment -configuration $(join-path $binariesPath \"p1.json\")\r\n",
"\r\n",
"# disable all Trace Flag checks\r\n",
"Invoke-SqlAssessment -configuration $(join-path $binariesPath \"p2.json\")\r\n",
"\r\n",
"# add new check\r\n",
"Get-SqlAssessmentItem -configuration $(join-path $binariesPath \"p3.json\")\r\n",
"Invoke-SqlAssessment -configuration $(join-path $binariesPath \"p3.json\")\r\n",
"\r\n",
"# add probe\r\n",
"Invoke-SqlAssessment -configuration $(join-path $binariesPath \"p4.json\")\r\n",
"\r\n",
"# alter probe\r\n",
"Invoke-SqlAssessment -configuration $(join-path $binariesPath \"p5.json\")\r\n",
"\r\n",
"# add threshold parameter\r\n",
"Invoke-SqlAssessment -configuration $(join-path $binariesPath \"p6.json\")\r\n",
"\r\n",
"# override parameter\r\n",
"Invoke-SqlAssessment -configuration $(join-path $binariesPath \"p6.json\"),$(join-path $binariesPath \"p7.json\")\r\n",
"\r\n",
"# replace SQL probe with managed code\r\n",
"Invoke-SqlAssessment -configuration $(join-path $binariesPath \"p8.json\"),$(join-path $binariesPath \"p7.json\")"
],
"metadata": {
"azdata_cell_guid": "e08c6f27-1087-47c5-8dda-4f82382ee7ee"
},
"outputs": [],
"execution_count": null
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment