notebooks/dotNETNotebooks/dbatools/06-AgentJobs.ipynb

{
  "metadata": {
    "kernelspec": {
      "name": ".net-powershell",
      "display_name": ".NET (PowerShell)",
      "language": "PowerShell"
    },
    "language_info": {
      "name": "PowerShell",
      "mimetype": "text/x-powershell",
      "file_extension": ".ps1",
      "version": "7.0",
      "pygments_lexer": "powershell"
    }
  },
  "nbformat_minor": 2,
  "nbformat": 4,
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# Working with Agent Jobs with dbatools\n",
        "\n",
        "You can use dbatools to administer your Agent jobs. Make sure that you have first run your create container notebook\n",
        "\n",
        "We have written a book which will give you a brilliant introduction to dbatools. It's called dbatools in a Month of Lunches and you can find it at https://beard.media/book\n",
        "\n",
        "The cell block below will create the variables that you will need for the rest of the notebook\n",
        "\n",
        "You can run the code by using the arrow keys and shift and enter"
      ],
      "metadata": {
        "azdata_cell_guid": "bb22d6b7-3379-462c-99e0-17644cd6535f"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "$FolderPath = $Env:USERPROFILE + '\\Documents\\dbatoolsdemo'\n",
        "$SqlInstances = 'localhost,15592', 'localhost,15593'\n",
        "$SqlCredential = Import-Clixml -Path $FolderPath\\sqladmin.cred\n",
        "Write-Output \" Creating connection to the containers\"\n",
        "try {\n",
        "    $SQL1 = Connect-DbaInstance -SqlInstance $SqlInstances[0] -SqlCredential $SqlCredential \n",
        "    $SQL2 = Connect-DbaInstance -SqlInstance $SqlInstances[1] -SqlCredential $SqlCredential\n",
        "    Write-Output \"We have a connection to the containers\"\n",
        "\n",
        "}\n",
        "catch {\n",
        "    Write-Output \"You haven't got a connection to the containers - Either they are still upgrading in which case try again in 30 seconds or the containers have not come up correctly\"\n",
        "    Write-Output \"Make sure the containers are running - the code is below in a block for you\"\n",
        "    Write-Output \"docker ps -a\"\n",
        "    Write-Output \"If they are read the logs - the code is below in a block for you\"\n",
        "    Write-Output \"docker logs dbatools_SQL2019_1\"\n",
        "    Write-Output \"docker logs dbatools_SQL2019-1_1\"\n",
        "}\n",
        "# Run this first to make sure output width does not mess with output - Update output buffer size to prevent clipping in Visual Studio output window.\n",
        "if( $Host -and $Host.UI -and $Host.UI.RawUI ) {\n",
        "    $rawUI = $Host.UI.RawUI\n",
        "    $oldSize = $rawUI.BufferSize\n",
        "    $typeName = $oldSize.GetType( ).FullName\n",
        "    $newSize = New-Object $typeName (500, $oldSize.Height)\n",
        "    $rawUI.BufferSize = $newSize\n",
        "  }"
      ],
      "metadata": {
        "jupyter": {
          "source_hidden": true
        },
        "azdata_cell_guid": "027eb9ed-0cbe-4bc5-95e6-14c04d741d95",
        "tags": [
          "hide_input"
        ]
      },
      "outputs": [],
      "execution_count": null
    },
    {
      "cell_type": "markdown",
      "source": [
        "## List the Agent jobs on the instance\n",
        "\n",
        "You can very quickly list all the agent jobs on an instance with dbatools"
      ],
      "metadata": {
        "azdata_cell_guid": "5be1ad92-499e-4040-9f0f-e296b4f11d22"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "Get-DbaAgentJob -SqlInstance $sql1 | Format-Table"
      ],
      "metadata": {
        "azdata_cell_guid": "58634944-9a9a-4b70-976d-a77d5a6cf097"
      },
      "outputs": [],
      "execution_count": null
    },
    {
      "cell_type": "markdown",
      "source": [
        "You can get info about a particular job"
      ],
      "metadata": {
        "azdata_cell_guid": "4db1ea57-667c-4d55-ab2a-cf0316c2756b"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "Get-DbaAgentJob -SqlInstance $sql1 -Job 'The Beard is Important'"
      ],
      "metadata": {
        "azdata_cell_guid": "a367f38d-edf8-4f57-9e4f-b0c6bf8f5eb3"
      },
      "outputs": [],
      "execution_count": null
    },
    {
      "cell_type": "markdown",
      "source": [
        "Job Steps"
      ],
      "metadata": {
        "azdata_cell_guid": "a2ad4b9c-d335-43f8-847c-c218e0932b9c"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "Get-DbaAgentJobStep -SqlInstance $SQL1 -Job 'The Beard is Important'"
      ],
      "metadata": {
        "azdata_cell_guid": "f2a77863-6c58-4f04-afcc-e8dfb5ec17d7"
      },
      "outputs": [],
      "execution_count": null
    },
    {
      "cell_type": "markdown",
      "source": [
        "You can remove jobs \n",
        "\n",
        "**PLEASE DON'T DO THIS IN PRODUCTION**"
      ],
      "metadata": {
        "azdata_cell_guid": "fb396890-c4b5-4635-8654-06cff6efcfca"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "Get-DbaAgentJob -SqlInstance $SQL2 | Remove-DbaAgentJob -Confirm:$false"
      ],
      "metadata": {
        "azdata_cell_guid": "57dc4034-f8c4-44ad-b5be-44988f53dc1e"
      },
      "outputs": [],
      "execution_count": null
    },
    {
      "cell_type": "markdown",
      "source": [
        "We have no jobs on SQL2"
      ],
      "metadata": {
        "azdata_cell_guid": "13d31934-319b-4440-a14d-9d24d535be2f"
      }
    },
    {
      "cell_type": "code",
      "source": [
        " $SQL2 = Connect-DbaInstance -SqlInstance $SqlInstances[1] -SqlCredential $SqlCredential\n",
        "Get-DbaAgentJob -SqlInstance $SQL2"
      ],
      "metadata": {
        "azdata_cell_guid": "36e7043a-6c66-4d5d-89d4-fb9694cc5cab"
      },
      "outputs": [],
      "execution_count": null
    },
    {
      "cell_type": "markdown",
      "source": [
        "If you have run through the notebooks, you might still have the Availability Group that was created in Notebook 03\n",
        "\n",
        "You would want your Agent Jobs to be the same on all replicas so that if you failover everything works\n",
        "\n",
        "dbatools has you covered"
      ],
      "metadata": {
        "azdata_cell_guid": "42cff201-9ce5-4065-987b-2163f172a95e"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "Copy-DbaAgentJob -Source $SQL1 -Destination $SQL2"
      ],
      "metadata": {
        "azdata_cell_guid": "7b23de5b-36ea-40c0-acd1-edf0a52eed28"
      },
      "outputs": [],
      "execution_count": null
    },
    {
      "cell_type": "markdown",
      "source": [
        "and just like that all jobs are on SQL2"
      ],
      "metadata": {
        "azdata_cell_guid": "623f4a38-4363-494a-ab90-625c8a905468"
      }
    },
    {
      "cell_type": "code",
      "source": [
        " $SQL2 = Connect-DbaInstance -SqlInstance $SqlInstances[1] -SqlCredential $SqlCredential\n",
        "Get-DbaAgentJob -SqlInstance $SQL2|Format-Table"
      ],
      "metadata": {
        "azdata_cell_guid": "3db8ce8d-b269-4965-bdcb-445a06581b1c"
      },
      "outputs": [],
      "execution_count": null
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Clean Up\n",
        "\n",
        "The 99-CleanUpContainers notebook will remove the containers, files and directory - it will leave the image so you do not have to download it again!"
      ],
      "metadata": {
        "azdata_cell_guid": "6a216fd0-3bf3-42a4-add8-ec977fb38dec"
      }
    }
  ]
}