Examples on how to add and remove SQL Server databases from a SQL Server Availability Group

DatabaseAvailabilityGroupMgmt.ps1

#Requires -Version 3.0

function Invoke-NonQuery
{
    [cmdletbinding()]
    param(
        [Parameter(Mandatory=$true)][string]$DatabaseName,
        [Parameter(Mandatory=$true)][string]$DatabaseServer,
        [Parameter(Mandatory=$true)][string]$Query,
        [int]$CommandTimeout=30 # The default is 30 seconds
    )

    $connectionString = "Data Source=$DatabaseServer;Initial Catalog=$DatabaseName;Integrated Security=True;Pooling=false"

    try
    {
        $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
        $connection.Open()

        $command = New-Object System.Data.SqlClient.SqlCommand($Query, $connection)     
        $command.CommandTimeout = $CommandTimeout
        $command.CommandType = [System.Data.CommandType]::Text
        $command.ExecuteNonQuery() | Out-Null
    }
    catch
    {
        throw $_.Exception
    }
    finally
    {
        if($command)
        {
            $command.Dispose()
        }

        if($connection)
        {
            [System.Data.SqlClient.SqlConnection]::ClearPool($connection)
            $connection.Close()
            $connection.Dispose()
        }
    }
}

function Get-DataTable
{
    [cmdletbinding()]
    param(
        [Parameter(Mandatory=$true)][string]$DatabaseServer,
        [Parameter(Mandatory=$true)][string]$DatabaseName,
        [Parameter(Mandatory=$true)][string]$Query
    )

    $connectionString = "Data Source=$DatabaseServer;Initial Catalog=$DatabaseName;Integrated Security=True;Enlist=False;Connect Timeout=5"
    
    try
    {
        $dataSet     = New-Object System.Data.DataSet     
        $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($Query, $connectionString)
        $dataAdapter.Fill($dataSet) | Out-Null
        return $dataSet.Tables[0]
    }
    catch
    {
        throw $_.Exception
    }
    finally
    {
        if($dataSet)
        {
            $dataSet.Dispose()
        }

        if($dataAdapter)
        {
            $dataAdapter.Dispose()
        }
    }
}

function Get-PrimaryReplica
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$Listener,
        [parameter(Mandatory=$true)][string]$AvailabilityGroupName
    )

    begin
    {
        $query = "SELECT replica_server_name 
                  FROM   sys.availability_replicas r
                         JOIN sys.availability_groups g ON g.group_id = r.group_id
                         JOIN sys.dm_hadr_availability_replica_states s ON s.replica_id = r.replica_id
                  WHERE  name = '$AvailabilityGroupName' AND role_desc = 'PRIMARY'"
    }
    process
    {
        Write-Verbose "$(Get-Date) - $($MyInvocation.MyCommand.Name) - QUERY: $query"
        return (Get-DataTable -DatabaseServer $Listener -DatabaseName "master" -Query $query).replica_server_name
    }
    end
    {
    }    
}

function Set-FullRecoverMode
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$DatabaseServer,
        [parameter(Mandatory=$true)][string]$DatabaseName
    )

    begin
    {
        $query = "IF (SELECT recovery_model_desc FROM sys.databases WHERE Name='$DatabaseName') <> 'FULL' BEGIN ALTER DATABASE [$DatabaseName] SET RECOVERY FULL END"
    }
    process
    {
        Write-Verbose "$($MyInvocation.MyCommand.Name) - QUERY: $query"
        Invoke-NonQuery -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Query $query
    }
    end
    {
    }  
}

function Get-Replica
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$DatabaseServer,
        [parameter(Mandatory=$true)][string]$AvailabilityGroupName
    )

    begin
    {
        $replicas = @()

        $query = "SELECT replica_server_name 
                  FROM   sys.availability_replicas r
                         JOIN sys.availability_groups g ON g.group_id = r.group_id
                         JOIN sys.dm_hadr_availability_replica_states s ON s.replica_id = r.replica_id
                  WHERE  name = '$AvailabilityGroupName' AND role_desc <> 'PRIMARY'"
    }
    process
    {
        Write-Verbose "$(Get-Date) - $($MyInvocation.MyCommand.Name) - QUERY: $query"

        Get-DataTable -DatabaseServer $DatabaseServer -DatabaseName "master" -Query $query | % {
            $replicas += $_.replica_server_name
        }
    }
    end
    {
        $replicas
    }    
}

function Backup-Database
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$DatabaseServer,
        [parameter(Mandatory=$true)][string]$DatabaseName,
        [parameter(Mandatory=$true)][string]$FilePath
    )

    begin
    {
        $query = "BACKUP DATABASE [$DatabaseName] TO DISK = N'$FilePath' WITH NOFORMAT, NOINIT, NAME = N'$DatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD"
    }
    process
    {
        Write-Verbose "$(Get-Date) - $($MyInvocation.MyCommand.Name) - QUERY: $query"
        Invoke-NonQuery -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Query $query -CommandTimeout 0
    }
    end
    {
    } 
}

function Backup-DatabaseLog
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$DatabaseServer,
        [parameter(Mandatory=$true)][string]$DatabaseName,
        [parameter(Mandatory=$true)][string]$FilePath
    )

    begin
    {
        $query = "BACKUP LOG [$DatabaseName] TO DISK = N'$FilePath' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION"

    }
    process
    {
        Write-Verbose "$(Get-Date) - $($MyInvocation.MyCommand.Name) - QUERY: $query"
        Invoke-NonQuery -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Query $query -CommandTimeout 0
    }
    end
    {
    } 
}

function Test-DatabaseAvailabilityGroupMembership
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$DatabaseServer,
        [parameter(Mandatory=$true)][string]$DatabaseName,
        [parameter(Mandatory=$true)][string]$AvailabilityGroupName
    )

    begin
    {
        $query = "SELECT COUNT(*) FROM sys.availability_databases_cluster c JOIN sys.availability_groups g ON g.group_id = c.group_id WHERE name = '$AvailabilityGroupName' AND database_name = '$DatabaseName'"
    }
    process
    {
        Write-Verbose "$(Get-Date) - $($MyInvocation.MyCommand.Name) - QUERY: $query"
        return [bool](Get-DataTable -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query)[0]
    }
    end
    {
    } 
}

function Register-DatabaseWithAvailabilityGroup
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$AvailabilityGroupName,
        [parameter(Mandatory=$true)][string]$DatabaseServer,
        [parameter(Mandatory=$true)][string]$DatabaseName
    )
    begin
    {
        $query = "IF 0 = (SELECT COUNT(*) FROM sys.availability_databases_cluster c JOIN sys.availability_groups g ON g.group_id = c.group_id WHERE name = '$AvailabilityGroupName' AND database_name = '$DatabaseName')
                  BEGIN
                      ALTER AVAILABILITY GROUP [$AvailabilityGroupName] ADD DATABASE [$DatabaseName]
                  END"
    }
    process
    {
        Write-Verbose "$(Get-Date) - $($MyInvocation.MyCommand.Name) - QUERY: $query"
        Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query
    }
    end
    {
    }  

}

function Unregister-DatabaseFromAvailabilityGroup
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$AvailabilityGroupName,
        [parameter(Mandatory=$true)][string]$DatabaseServer,
        [parameter(Mandatory=$true)][string]$DatabaseName
    )
    begin
    {
        $query = "IF 0 <> (SELECT COUNT(*) FROM sys.availability_databases_cluster c JOIN sys.availability_groups g ON g.group_id = c.group_id WHERE name = '$AvailabilityGroupName' AND database_name = '$DatabaseName')
                  BEGIN
                      ALTER AVAILABILITY GROUP [$AvailabilityGroupName]
                      REMOVE DATABASE [$DatabaseName];
                  END"
    }
    process
    {
        Write-Verbose "$(Get-Date) - $($MyInvocation.MyCommand.Name) - QUERY: $query"
        Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query
    }
    end
    {
    }  

}

function Restore-Database
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$DatabaseServer,
        [parameter(Mandatory=$true)][string]$DatabaseName,
        [parameter(Mandatory=$true)][string]$FilePath
    )
    begin
    {
        $query = "RESTORE DATABASE [$DatabaseName] FROM DISK = N'$FilePath' WITH NORECOVERY, NOUNLOAD"
    }
    process
    {
        Write-Verbose "$(Get-Date) - $($MyInvocation.MyCommand.Name) - QUERY: $query"
        Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query -CommandTimeout 0
    }
    end
    {
    }  

}

function Restore-DatabaseLog
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$DatabaseServer,
        [parameter(Mandatory=$true)][string]$DatabaseName,
        [parameter(Mandatory=$true)][string]$FilePath
    )
    begin
    {

        $query = "RESTORE LOG [$DatabaseName] FROM DISK = N'$FilePath' WITH NORECOVERY, NOUNLOAD"
    }
    process
    {
        Write-Verbose "$(Get-Date) - $($MyInvocation.MyCommand.Name) - QUERY: $query"
        Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query -CommandTimeout 0
    }
    end
    {
    }  

}

function Remove-Database
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$DatabaseServer,
        [parameter(Mandatory=$true)][string]$DatabaseName
    )
    begin
    {
        $query1 = "IF db_id('$DatabaseName') IS NOT NULL
                   BEGIN
                        EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'$DatabaseName'
                   END"


        $query2 = "IF db_id('$DatabaseName') IS NOT NULL
                   BEGIN
                        USE MASTER; 
                        ALTER DATABASE [$DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
                        DROP DATABASE [$DatabaseName]
                   END"
    }
    process
    {
        Write-Verbose "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Removing backup history for $DatabaseName"
        Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query1 -CommandTimeout 0

        try
        {
            Write-Verbose "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Deleting $DatabaseName"
            Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query2 -CommandTimeout 0
        }
        catch
        {
            # dropping the database from the replicas seems to consistently throw the error below, but it actually does the delete.
            if( $_.Exception.Message -notmatch "ALTER DATABASE failed because a lock could not be placed on database" )
            {
                Write-Error $_.Exception
            }
        }
    }
    end
    {
    }  

}

function Enable-AvailabilityGroup
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$DatabaseServer,
        [parameter(Mandatory=$true)][string]$DatabaseName,
        [parameter(Mandatory=$true)][string]$AvailabilityGroupName
    )
    begin
    {

        $query = "
            begin try
                declare @conn bit
                declare @count int
                declare @replica_id uniqueidentifier
                declare @group_id uniqueidentifier
                set @conn = 0
                set @count = 30 -- wait for 5 minutes

                if (serverproperty('IsHadrEnabled') = 1)
                    and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
                    and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
                begin
                    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'{0}'
                    select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
                    while @conn <> 1 and @count > 0
                    begin
                        set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
                        if @conn = 1
                        begin
                            -- exit loop when the replica is connected, or if the query cannot find the replica status
                            break
                        end
                        waitfor delay '00:00:10'
                        set @count = @count - 1
                    end
                end
            end try
            begin catch
                -- If the wait loop fails, do not stop execution of the alter database statement
            end catch
            ALTER DATABASE [$DatabaseName] SET HADR AVAILABILITY GROUP = [$AvailabilityGroupName]"
    }
    process
    {
        Write-Verbose "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Enabling Availability Group for database $DatabaseName"
        Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query $query -CommandTimeout 0
    }
    end
    {
    }  

}

function Add-DatabaseToAvailabilityGroup
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$Listener,
        [parameter(Mandatory=$true)][string]$DatabaseName,
        [parameter(Mandatory=$true)][string]$AvailabilityGroupName,
        [parameter(Mandatory=$true)][string]$FilePath
    )

    begin
    {
        $timestamp = [System.DateTime]::Now.ToString("yyyyMMddhhmmss")
        $logFileBackupFilePath  = Join-Path -Path $FilePath -ChildPath "$($DatabaseName)_$timestamp.trn"
        $dataFileBackupFilePath = Join-Path -Path $FilePath -ChildPath "$($DatabaseName)_$timestamp.bak"

    }
    process
    {
        $primaryReplica    = Get-PrimaryReplica -Listener $Listener -AvailabilityGroupName $AvailabilityGroupName
        $secondaryReplicas = Get-Replica -DatabaseServer $Listener -AvailabilityGroupName $AvailabilityGroupName

        Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Primary Replica: $primaryReplica"
        foreach($replica in $secondaryReplicas)
        {
            Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Secondary Replica: $replica"
        }

        if( Test-DatabaseAvailabilityGroupMembership -DatabaseServer $primaryReplica -DatabaseName $DatabaseName -AvailabilityGroupName $AvailabilityGroupName) 
        {
            Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Database $DatabaseName is already a member of Availability Group '$AvailabilityGroupName'" -ForegroundColor Yellow
            return
        }
        

        Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Setting database recovery mode to 'FULL' for database $DatabaseName"
        Set-FullRecoverMode -DatabaseServer $primaryReplica -DatabaseName $DatabaseName


        Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Creating FULL backup at $dataFileBackupFilePath"
        Backup-Database -DatabaseServer $primaryReplica -DatabaseName $DatabaseName -FilePath $dataFileBackupFilePath
        if(-not $?) { return }

        
        Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Registering database with Availability Group $AvailabilityGroupName"
        Register-DatabaseWithAvailabilityGroup -AvailabilityGroupName $AvailabilityGroupName -DatabaseServer $primaryReplica -DatabaseName $DatabaseName
    

        if(-not $?) { return }
        foreach( $replica in $secondaryReplicas)
        {
            # restore the database backup to each Availability Group replica
            Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Restoring FULL backup to $replica"
            Restore-Database -DatabaseServer $replica -DatabaseName $DatabaseName -FilePath $dataFileBackupFilePath
        }


        if(-not $?) { return }
        Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Creating TRANS backup at $logFileBackupFilePath"
        Backup-DatabaseLog -DatabaseServer $primaryReplica -DatabaseName $DatabaseName -FilePath $logFileBackupFilePath
        


        if(-not $?) { return }
        foreach( $replica in $secondaryReplicas)
        {
            Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Restoring TRANS backup to $replica"
            Restore-DatabaseLog -DatabaseServer $replica -DatabaseName $DatabaseName -FilePath $logFileBackupFilePath

            if(-not $?) { return }

            Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Enabling $DatabaseName in Availability Group"
            Enable-AvailabilityGroup -DatabaseServer $replica -DatabaseName $DatabaseName -AvailabilityGroupName $AvailabilityGroupName
        }
    }
    end
    {
    } 
}

function Remove-DatabaseFromAvailabilityGroup
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$Listener,
        [parameter(Mandatory=$true)][string]$DatabaseName,
        [parameter(Mandatory=$true)][string]$AvailabilityGroupName,
        [parameter(Mandatory=$false)][switch]$DeletePrimary,
        [parameter(Mandatory=$false)][switch]$Force
    )

    begin
    {
    }
    process
    {
        $primaryReplica    = Get-PrimaryReplica -Listener $Listener -AvailabilityGroupName $AvailabilityGroupName
        $secondaryReplicas = Get-Replica -DatabaseServer $Listener -AvailabilityGroupName $AvailabilityGroupName

        Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Primary Replica: $primaryReplica"
        foreach($replica in $secondaryReplicas)
        {
            Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Secondary Replica: $replica"
        }

        if( -not $Force -and -not (Test-DatabaseAvailabilityGroupMembership -DatabaseServer $primaryReplica -DatabaseName $DatabaseName -AvailabilityGroupName $AvailabilityGroupName) ) 
        {
            Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Database $DatabaseName is not a member of the Availability Group '$AvailabilityGroupName'" -ForegroundColor Yellow
            return
        }

        Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Removing $DatabaseName from Availability Group $AvailabilityGroupName"
        Unregister-DatabaseFromAvailabilityGroup -AvailabilityGroupName $AvailabilityGroupName -DatabaseServer $primaryReplica -DatabaseName $DatabaseName

        if(-not $?) { return }

        foreach( $replica in $secondaryReplicas)
        {
            Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Removing database $DatabaseName from secondary replcia $replica"
            Remove-Database -DatabaseServer $replica -DatabaseName $DatabaseName
        }

        if($DeletePrimary)
        {
            Write-Host "$(Get-Date) - $($MyInvocation.MyCommand.Name) - Removing database $DatabaseName from primary replcia $replica"
            Remove-Database -DatabaseServer $primaryReplica -DatabaseName $DatabaseName
        }
    }
    end
    {
    } 
}



<#

$listener          = "contoso-listener"
$availabilityGroup = "contoso-ag"
$databaseName      = "TEST"
$fileShare         = "\\sqlserver-0\backups"

# EXAMPLE: add a databaes to an availabilty group
Add-DatabaseToAvailabilityGroup -Listener $listener -DatabaseName $databaseName -AvailabilityGroupName $availabilityGroup -FilePath $fileShare 

# EXAMPLE: delete the database from the secondary replicas (only)
Remove-DatabaseFromAvailabilityGroup -Listener $listener -DatabaseName $databaseName -AvailabilityGroupName $availabilityGroup

# EXAMPLE: delete the database from the secondary replicas (only), even if the database is no longer in the Availability Group
Remove-DatabaseFromAvailabilityGroup -Listener $listener -DatabaseName $databaseName -AvailabilityGroupName $availabilityGroup -Force

# EXAMPLE: delete the database from the secondary replicas and the primary replica
Remove-DatabaseFromAvailabilityGroup -Listener $listener -DatabaseName $databaseName -AvailabilityGroupName $availabilityGroup -DeletePrimary 

# EXAMPLE: delete the database from the secondary replicas and the primary replica, even if the database is no longer in the Availability Group
Remove-DatabaseFromAvailabilityGroup -Listener $listener -DatabaseName $databaseName -AvailabilityGroupName $availabilityGroup -DeletePrimary -Force    

#>