0

#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.

Filed in: Columns, Tips and Tricks Tags: , , ,

Leave a Reply

Submit Comment

© 2016 PowerShell Magazine. All rights reserved. XHTML / CSS Valid.
Proudly designed by Theme Junkie.
%d bloggers like this: