notebooks/dotNETNotebooks/dbatools/03AvailabilityGroups.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": ".net-powershell",
            "display_name": ".NET (PowerShell)",
            "language": "PowerShell"
        },
        "language_info": {
            "name": "PowerShell",
            "version": "7.0",
            "mimetype": "text/x-powershell",
            "file_extension": ".ps1",
            "pygments_lexer": "powershell"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Availability Groups\n",
                "\n",
                "***You will need to have followed the steps in the 00-CreateContainers notebook to use this 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",
                "Setting up availabilty groups is hard\n",
                "\n",
                "with dbatools it is a little easier :-)\n",
                "\n",
                "The next block sets the variables for the instances and folder paths for this Notebook and checks the connection - Refer to the first notebook for any issues"
            ],
            "metadata": {
                "azdata_cell_guid": "2c9b66ba-ece4-4545-8436-6765d59c58aa"
            }
        },
        {
            "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": "1e4cb3a8-f760-47eb-888e-410d51a6b95e",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "**NEVER EVER DO THIS IN PRODUCTION**\n",
                "unless you need to delete all of your user databases for some reason\n"
            ],
            "metadata": {
                "azdata_cell_guid": "9b60d31b-81b8-4202-98ac-566e257e459d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaDatabase -SqlInstance $SQL2 -ExcludeAllSystemDb | Remove-DbaDatabase -Confirm:$false"
            ],
            "metadata": {
                "azdata_cell_guid": "8ab4cf29-dd7e-4b84-b4f0-18bec0f8c04c"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Containers\n",
                "\n",
                "dbatools is not able to create availability groups in containers with one command unfrotunately, so we will create an availability group below in a different way, which is cool because we can show you a bunch of other dbatools commands! If you are not using containers you can create an availability group with dbatools like this\n",
                "\n",
                "```\n",
                "$params = @{\n",
                "    Primary = $SqlInstances[0]\n",
                "    PrimarySqlCredential =  $SqlCredential \n",
                "    Secondary =   $SqlInstances[1]\n",
                "    SecondarySqlCredential = $SqlCredential \n",
                "    Name = 'TheBeard_AG'\n",
                "    Database = 'pubs','NorthWind','AdventureWorks2017'\n",
                "    ClusterType = \"None\"\n",
                "    SeedingMode = \"Automatic\"\n",
                "    FailoverMode = \"Manual\"\n",
                "    Confirm = $false\n",
                " }\n",
                "\n",
                "# execute the command\n",
                " New-DbaAvailabilityGroup @params\n",
                "```\n",
                "\n",
                "There are many options that you can use here. Read the docs or get our book [beard.media\\book](.\\[beard.media\\book)\n",
                "\n",
                "Lets create an availability group to use with this notebook\n",
                "\n",
                "First we shall create a master key on each instance"
            ],
            "metadata": {
                "azdata_cell_guid": "941a71ae-c71d-4c07-8d0f-044b05880bc3"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "New-DbaDbMasterKey -SqlInstance $SQL1 -Credential $SqlCredential -Confirm:$false\n",
                "New-DbaDbMasterKey -SqlInstance $SQL2 -Credential $SqlCredential -Confirm:$false"
            ],
            "metadata": {
                "azdata_cell_guid": "e0b0a9c5-5a2f-4d8c-bebe-f5d943e3dbd0"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Certificate\n",
                "\n",
                "Next we will create a certificate for the endpoints on SQL1, back it up and restore it on SQL2. You can do this for any certificate that you require by the way :-)"
            ],
            "metadata": {
                "azdata_cell_guid": "71820142-74b8-485f-a003-b195eee49b8b"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "New-DbaDbCertificate -SqlInstance $sql1 -Name hadr_cert -Subject hadr_cert -Confirm:$false\n",
                "\n",
                "$EncryptionPassword = ConvertTo-SecureString -AsPlainText \"Password4567!!\" -force\n",
                "Backup-DbaDbCertificate -SqlInstance $SQL1 -Suffix $null -Certificate hadr_cert -Path '/var/opt/mssql/backups/' -EncryptionPassword $EncryptionPassword -Confirm:$false\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "4e106822-f926-407b-a5fb-e82ce9143f31"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Now we will use `Get-DbaFile` to ge the certificate path and then restore the certificate to SQL2\n",
                "This will prompt you to ask for input. If you did not wnat this, you can use `-Force` I am also showing verbose output here. All dbatools commands can output verbose information if you use the `-Verbose` switch"
            ],
            "metadata": {
                "azdata_cell_guid": "eca9b513-89cb-487d-a7c9-415dc6ae7f16"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$Certificate = (Get-DbaFile -SqlInstance $sql2 -Path '/var/opt/mssql/backups/' -FileType cer).FileName\n",
                "Restore-DbaDbCertificate -SqlInstance $SQL2 -Path $Certificate -DecryptionPassword $EncryptionPassword  -Verbose -Confirm:$false\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "e27d4ece-59b3-45de-b9bb-41f31f6e47b4"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Endpoints\n",
                "Now that we have the certificates on each instance, we can create the endpoints using the certificate and start them"
            ],
            "metadata": {
                "azdata_cell_guid": "692534b0-a869-47fb-91e6-6a20acb92d1b"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "New-DbaEndpoint -SqlInstance $sql1 -Name hadr_endpoint -Port 5022 -Certificate hadr_cert \n",
                "New-DbaEndpoint -SqlInstance $sql2 -Name hadr_endpoint -Port 5023 -Certificate hadr_cert\n",
                "\n",
                "Start-DbaEndpoint -SqlInstance $SQL1 -EndPoint hadr_endpoint\n",
                "Start-DbaEndpoint -SqlInstance $SQL2 -EndPoint hadr_endpoint"
            ],
            "metadata": {
                "azdata_cell_guid": "f44bea89-3e8a-4f57-aabd-a3b9773970c4"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Create the Availability Group\n",
                "\n",
                "we have to use T-SQL to get the right information only because we are using containers"
            ],
            "metadata": {
                "azdata_cell_guid": "2688fc30-2f16-42a3-9ac2-b2dced559cdc"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "\n",
                "$query = @\"\n",
                "CREATE AVAILABILITY GROUP [TheBeard_AG]\n",
                "    WITH (CLUSTER_TYPE = NONE)\n",
                "    FOR REPLICA ON\n",
                "        N'beardsql01' WITH (\n",
                "            ENDPOINT_URL = N'tcp://beardsql01:5022',\n",
                "\t\t    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,\n",
                "\t\t    FAILOVER_MODE = MANUAL,\n",
                "\t\t    SEEDING_MODE = AUTOMATIC,\n",
                "                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)\n",
                "\t\t    ),\n",
                "        N'beardsql02' WITH ( \n",
                "\t\t    ENDPOINT_URL = N'tcp://beardsql02:5023', \n",
                "\t\t    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,\n",
                "\t\t    FAILOVER_MODE = MANUAL,\n",
                "\t\t    SEEDING_MODE = AUTOMATIC,\n",
                "\t\t    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)\n",
                "\t\t    );\n",
                "\t\t\n",
                "ALTER AVAILABILITY GROUP [TheBeard_AG] GRANT CREATE ANY DATABASE;\n",
                "\"@\n",
                "\n",
                "Invoke-DbaQuery -SqlInstance $sql1 -Query $query\n",
                "Write-Output \"Created AG\"\n",
                "$query = @\"\n",
                "ALTER AVAILABILITY GROUP [TheBeard_AG] JOIN WITH (CLUSTER_TYPE = NONE);\n",
                "\t\t \n",
                "ALTER AVAILABILITY GROUP [TheBeard_AG] GRANT CREATE ANY DATABASE;\n",
                "\"@\n",
                "\n",
                "Invoke-DbaQuery -SqlInstance $sql2 -Query $query\n",
                "Write-Output \"Added SQL2 to the AG\""
            ],
            "metadata": {
                "jupyter": {
                    "source_hidden": true
                },
                "azdata_cell_guid": "0a3a56f8-543f-43d1-bac7-66a32b62f9b3"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "we can check that the availability groups on an instance using `Get-DbaAvailabilityGroup`"
            ],
            "metadata": {
                "azdata_cell_guid": "9894843a-8724-45ee-8aed-8c937a64199a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaAvailabilityGroup -SqlInstance $sql1 "
            ],
            "metadata": {
                "azdata_cell_guid": "ee767f15-01f3-433d-8eb1-a38206725550"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Excellent, we have an availablity group - Lets check the replicas"
            ],
            "metadata": {
                "azdata_cell_guid": "16cdb6ae-d398-48a6-aa2f-9feb706ed3e6"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaAgReplica -SqlInstance $sql1"
            ],
            "metadata": {
                "azdata_cell_guid": "e5852957-f787-4cf9-9211-3cce1aa73957"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "We have two replicas - Right now they are not synchronising because there are no databases, You can use this command to see the rol up status for the replicas\n",
                "\n",
                "Let's check the databases"
            ],
            "metadata": {
                "azdata_cell_guid": "01e2559d-64bd-445a-acb6-322da4e22b58"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaAgDatabase -SqlInstance $SQL1"
            ],
            "metadata": {
                "azdata_cell_guid": "ec33338b-b433-4454-8a54-1005244a2ef5"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "There are no databases - Lets add the databases"
            ],
            "metadata": {
                "azdata_cell_guid": "04bff52c-d11f-444b-8fd1-c922a43e40cb"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Compatability Level"
            ],
            "metadata": {
                "azdata_cell_guid": "2e320064-9e96-4616-901b-02772d151cd6"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaDbCompatibility -SqlInstance $Sql1,$sql2 | ft"
            ],
            "metadata": {
                "azdata_cell_guid": "ec6e87a6-5a1f-45bf-beed-e86da444daa8"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Recovery Mode\n",
                "\n",
                "We need to set the recovery mode of the databases to full"
            ],
            "metadata": {
                "azdata_cell_guid": "e92f8b0f-215f-4781-a434-117b77c951a3"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaDatabase -SqlInstance $SQL1 -ExcludeSystem | Set-DbaDbRecoveryModel -RecoveryModel Full -Confirm:$false"
            ],
            "metadata": {
                "azdata_cell_guid": "0af9f482-42a4-4f3a-b114-4766ab7ccb4c"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Backup\n",
                "\n",
                "Of course, having set the recovery model to full we need to back up the databases otherwise they are in PSUDOSIMPLE mode. You can see this. If we check the recovery model, it will show full"
            ],
            "metadata": {
                "azdata_cell_guid": "f50728e2-da30-4f54-baf4-8bd60ef7476d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaDbRecoveryModel -SqlInstance $sql1 -Database AdventureWorks2017, Northwind, pubs | Format-Table"
            ],
            "metadata": {
                "azdata_cell_guid": "d1be8219-bb73-48bd-a632-deb31a102a38"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "but if we use `Test-DbaDbRecoveryModel` you can see that the *Actual* recovery model is SIMPLE"
            ],
            "metadata": {
                "azdata_cell_guid": "dc020671-59fd-495a-a4ca-c42690c501f9"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Test-DbaDbRecoveryModel -SqlInstance $sql1 -Database AdventureWorks2017, Northwind, pubs | Format-Table"
            ],
            "metadata": {
                "azdata_cell_guid": "2dea0572-d711-4239-a780-79dee340743b"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "If we backup the databases (like we did in the BackupsandRestores notebook"
            ],
            "metadata": {
                "azdata_cell_guid": "c171a283-55ad-4f13-9fee-28c8c6c8cb10"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Backup-DbaDatabase -SqlInstance $SQL1 -Path /var/opt/mssql/backups/SQL1 -Database  AdventureWorks2017, Northwind, pubs -Type Full -IgnoreFileChecks\n",
                "Backup-DbaDatabase -SqlInstance $SQL1 -Path /var/opt/mssql/backups/SQL1 -Database  AdventureWorks2017, Northwind, pubs -Type Log -IgnoreFileChecks"
            ],
            "metadata": {
                "azdata_cell_guid": "0111bfa4-f5b0-4b52-9d75-dfed5a61e635"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "and test our recovery model again"
            ],
            "metadata": {
                "azdata_cell_guid": "f4b25e72-33b1-4d24-aeb2-0db6e02b69ee"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Test-DbaDbRecoveryModel -SqlInstance $sql1 -Database AdventureWorks2017, Northwind, pubs | Format-Table"
            ],
            "metadata": {
                "azdata_cell_guid": "ea330838-073e-40b5-b8a6-2a1b484d18be"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "All our databases are now in FULL recovery :-)\n",
                "\n",
                "Let's add them to the Availability Group"
            ],
            "metadata": {
                "azdata_cell_guid": "1325cbeb-ce86-44ca-9534-f330d30cdcbd"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$AddAgDbConfig = @{\r\n",
                "    SqlInstance = $SqlInstances[0] \r\n",
                "    SqlCredential = $SqlCredential \r\n",
                "    AvailabilityGroup = 'TheBeard_AG' \r\n",
                "    Database = 'Northwind','Pubs','AdventureWorks2017' \r\n",
                "    Secondary = $SqlInstances[1] \r\n",
                "    SecondarySqlCredential = $SqlCredential \r\n",
                "    SeedingMode = 'Automatic' \r\n",
                "}\r\n",
                "Add-DbaAgDatabase @AddAgDbConfig"
            ],
            "metadata": {
                "azdata_cell_guid": "f50fdcda-7955-4aa5-90df-3de59f447234"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Let's have a look at them\n"
            ],
            "metadata": {
                "azdata_cell_guid": "e588d271-ec23-4c00-bc92-c94309e8ec16"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$sql1 = Connect-DbaInstance -SqlInstance $SqlInstances[0] -SqlCredential $SqlCredential\r\n",
                "$sql2 = Connect-DbaInstance -SqlInstance $SqlInstances[1] -SqlCredential $SqlCredential\r\n",
                "Get-DbaAgDatabase -SqlInstance $SQL1 | Format-Table"
            ],
            "metadata": {
                "azdata_cell_guid": "08c4ae05-accb-4e65-ad32-c8d14778b9ca"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "You can look in SSMS if you like :-)\n",
                "\n",
                "![AG](.\\images\\ag.png )"
            ],
            "metadata": {
                "azdata_cell_guid": "099e298b-1ef6-487d-b8f0-b2ac18f0cab3"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Suspending and resuming data movement\n",
                "\n",
                "You can use dbatools to suspend and resume data movement for your Availability Group databases. The code below will prompt you to confirm that you wish to suspend them."
            ],
            "metadata": {
                "azdata_cell_guid": "09f8bb78-5225-4274-aa94-9a3953097eb4"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaAgDatabase -SqlInstance $sql1  | Suspend-DbaAgDbDataMovement"
            ],
            "metadata": {
                "azdata_cell_guid": "1c6fdfcb-bd1b-4bae-9701-76bf7728c0b0"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "You can check that they are suspended using `Get-DbaAgDatabase` You will see that the `SynchronizationState` has changed to not synchronising and the `IsSuspended` property is set to true"
            ],
            "metadata": {
                "azdata_cell_guid": "065dfc0b-d8bd-4367-8e99-8dd9b8fc5d7d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaAgDatabase -SqlInstance $sql1 |Format-Table"
            ],
            "metadata": {
                "azdata_cell_guid": "944e247f-d8f1-423e-b226-1f1ed24a7328"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "If you want to see if any databases in your Availability Group are not synchronised you can use the code below (if you are looking on an asynchronous replica then you would change Synchronized to Synchronizing."
            ],
            "metadata": {
                "azdata_cell_guid": "6134d82f-7fe4-48ce-8c76-b3fc63e6207c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaAgDatabase -SqlInstance $SQL1 | Where SynchronizationState -ne 'Synchronized' | Format-Table"
            ],
            "metadata": {
                "azdata_cell_guid": "ee41ba40-d7b9-452e-bfe9-11327ec2f0de"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "You can add the capability to resume data movement using dbatools as well with `Resume-DbaAgDbDataMovement` this time we will use `-Confirm:$false` so there are no prompts"
            ],
            "metadata": {
                "azdata_cell_guid": "ee186dfb-0509-47d2-8fd6-087775be307a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaAgDatabase -SqlInstance $SQL1| Resume-DbaAgDbDataMovement -Confirm:$false"
            ],
            "metadata": {
                "azdata_cell_guid": "a31a588b-1f64-4bc6-84a3-a05b2b26f8de"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Removing a database from an Availability Group\n",
                "\n",
                "You can also remove a database from the availablity group. This code will prompt you to confirm the changing action which is usual dbatools behaviour\n"
            ],
            "metadata": {
                "azdata_cell_guid": "5e5859a9-17e2-4e3f-9d1e-66f872098572"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Remove-DbaAgDatabase -SqlInstance $SQL1 -AvailabilityGroup TheBeard_AG -Database pubs"
            ],
            "metadata": {
                "azdata_cell_guid": "37a35902-4e63-48f4-87c1-7aa3cfa141f4"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "We can check the user databases on the instances with `Get-DbaDatabase`"
            ],
            "metadata": {
                "azdata_cell_guid": "19ef7135-fa49-4668-8212-fbd9a3fd1f21"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$sql1 = Connect-DbaInstance -SqlInstance $SqlInstances[0] -SqlCredential $SqlCredential\r\n",
                "$sql2 = Connect-DbaInstance -SqlInstance $SqlInstances[1] -SqlCredential $SqlCredential\r\n",
                "Get-DbaDatabase -SqlInstance $SQL1, $SQL2 -ExcludeSystem | Select SqlInstance, Name,Status"
            ],
            "metadata": {
                "azdata_cell_guid": "5349b135-9530-4a75-b33c-59809a6e50b4"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "You can see that the pubs database on the secondary is in restoring state.\n",
                "\n",
                "You can also use `Get-DbaDatabase` to see the Availability Group synchronisation state and the Availability Group that it is joined to by getting some of the properties that are not displayed by default"
            ],
            "metadata": {
                "azdata_cell_guid": "ba2dd7a3-0242-4527-a34a-af1a070a4833"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaDatabase -SqlInstance $SQL1, $SQL2 -ExcludeSystem | Select SqlInstance, Name, AvailabilityDatabaseSynchronizationState, AvailabilityGroupName "
            ],
            "metadata": {
                "azdata_cell_guid": "aacd05e3-7fa1-4988-bd42-86a0ba8f77bd"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "You can see that the pubs database is not joined to the Availability Group"
            ],
            "metadata": {
                "azdata_cell_guid": "8e4d43b3-7a29-4686-aaaa-20e927b98adf"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Failover\n",
                "\n",
                "You can use dbatools to failover the Availability Group. Lets take a look at the Availability Group"
            ],
            "metadata": {
                "azdata_cell_guid": "c77b631e-77ca-4a05-88d6-f757a19eb43a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaAvailabilityGroup -SqlInstance $SQL1"
            ],
            "metadata": {
                "azdata_cell_guid": "196cd758-6790-478f-ac9f-04f87f27b898"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "The primary replica for the Availability Group is SQL1. Lets fail it over to SQL2"
            ],
            "metadata": {
                "azdata_cell_guid": "60a5a82a-f5db-4148-8f70-f4ed4acfd3e3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                " Invoke-DbaAgFailover -SqlInstance $SQL2 -AvailabilityGroup TheBeard_AG"
            ],
            "metadata": {
                "azdata_cell_guid": "6941a8f4-c5aa-4c68-9deb-5d9c55534088"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "It has prompted us for confirmation and then told us that the cluster type that we have used for the containers does not support this type of failover so we need to use the force                     parameter"
            ],
            "metadata": {
                "azdata_cell_guid": "65611420-57cd-4e1c-a2ab-380ce4caf1d2"
            }
        },
        {
            "cell_type": "code",
            "source": [
                " Invoke-DbaAgFailover -SqlInstance $SQL2 -AvailabilityGroup TheBeard_AG -Force"
            ],
            "metadata": {
                "azdata_cell_guid": "5711a6dd-315e-432d-9c95-8e38bc192d88"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "When we check the Availability Group on SQL1 again"
            ],
            "metadata": {
                "azdata_cell_guid": "d5ab74ea-f030-4d06-aada-adb952b18d9c"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "$sql1 = Connect-DbaInstance -SqlInstance $SqlInstances[0] -SqlCredential $SqlCredential\r\n",
                "$sql2 = Connect-DbaInstance -SqlInstance $SqlInstances[1] -SqlCredential $SqlCredential\r\n",
                "$SqlInstances = $sql1,$sql2\r\n",
                "Get-DbaAvailabilityGroup -SqlInstance $SQL1"
            ],
            "metadata": {
                "azdata_cell_guid": "78af97f5-1ddf-4550-a9b2-c632a88785a0"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "You can see by comparing the output that the primary replica has swapped to the other container."
            ],
            "metadata": {
                "azdata_cell_guid": "86299665-e0ff-44dd-9e85-f4a9a7fc88f5"
            },
            "attachments": {}
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DbaDatabase -SqlInstance $SqlInstances -SqlCredential $SqlCredential -ExcludeSystem | Select SqlInstance, Name, AvailabilityDatabaseSynchronizationState, AvailabilityGroupName "
            ],
            "metadata": {
                "azdata_cell_guid": "4c0fc424-1ce6-4f44-ab97-317b107f4b85"
            },
            "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": "cbda04ea-2fec-4fcf-a0dc-4f44ac1dbbd0"
            }
        }
    ]
}