#PSTip Deleting a SQL mirroring endpoint with SMO and PowerShell

Note: This tip requires PowerShell 2.0 or above.

In earlier tips, we looked at how we can get all SQL endpoints, check if a SQL endpoint exists or not, and creating SQL mirroring endpoints. In this tip, we will see how we can delete the existing SQL endpoints.

Function Remove-SQLEndPoint {
    [CmdletBinding()]
    param (
        [string]$computername=$env:COMPUTERNAME,
        [string]$instancename,
        [string]$endpointname
    )

    Begin {
        Write-Verbose "Loading SQL SMO"
        Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
        Add-Type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
    }

    Process {
            try {
                $connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $ComputerName
                $connection.applicationName = "PowerShell SQL SMO"

                if ($instancename) {
                    Write-Verbose "Connecting to SQL named instance"
                    $connection.ServerInstance = "${env:computername}\${instancename}"
                } else {
                    Write-Verbose "Connecting to default SQL instance"
                }

                $connection.StatementTimeout = 0
                $connection.Connect()
                $smo = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList $connection
            }
            catch {
                Write-Error $_
            }

            try {
                if ($smo.Endpoints[$endpointname]) {
                    Write-Verbose "Dropping the endpoint ${endpointname}"
                    $smo.Endpoints[$endpointname].Drop()
                } else {
                    Write-Error "No end point exists with the name ${endpointname}"
                }
            }

            catch {
                Write-Error $_
            }
    }
}

For the purpose of removing the endpoints, we use the Drop() method of the Endpoint class. For error handling purpose, we check if the given endpoint name exists within all existing endpoints on the SQL server.

About the author: Ravikanth C

Ravikanth is the founder and editor of the PowerShell Magazine. He is also a PowerShell MVP who works at Dell Inc. He blogs at http://www.ravichaganti.com/blog and you can follow him on Twitter @ravikanth.

Related Posts

%d bloggers like this: