#PSTip Creating a SQL TCP mirroring endpoint with SMO and PowerShell

Note: This tip requires PowerShell 2.0 or above.

In today’s tip, we will see how we can use PowerShell and SMO to create a SQL TCP mirroring endpoint. We can use the Create() method of Microsoft.SqlServer.Management.Smo.Endpoint class to create an endpoint. In the following function, I used the code we published in earlier tips to verify whether an endpoint exists or not. So, to be able to use this function, you need the Get-SQLEndpoint function from an earlier tip.

Function New-SQLMirroringTCPEndpoint {
    param (

    Begin {
        Write-Verbose "Loading SQL SMO"
        Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
        Add-Type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=, 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
                $smo = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList $connection
            catch {
                Write-Error $_

            try {
                if (!($smo.Endpoints[$endpointname])) {
                    if (!((Get-SQLEndPoint -computername $computername -instancename $instancename).ListenerPort -contains $endpointport)) {
                        Write-Verbose "Creating a mirroring endpoint named ${endpointname} at port ${endpointport}"
                        $SQLEndPoint = New-Object Microsoft.SqlServer.Management.Smo.Endpoint -ArgumentList $smo, $endpointname
                        $SQLEndPoint.EndpointType = [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring
                        $SQLEndPoint.ProtocolType = [Microsoft.SqlServer.Management.Smo.ProtocolType]::TCP
                        $SQLEndPoint.Protocol.Tcp.ListenerPort = $endpointport
                        $SQLEndPoint.Payload.DatabaseMirroring.ServerMirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::All
                    } else {
                        Write-Error "An endpoint with specified port number ${endpointport} already exists"
                } else {
                    Write-Error "An endpoint with name ${endpointname} already exists"

            catch {
                Write-Error $_

The way we use this function is simple.

#To create an endpoint on the local computer with default SQL instance
New-SQLMirroringTCPEndpoint -endpointname TestEndPoint -endpointport 8888

#To create an endpoint on a remote computer with  a named SQL instance
New-SQLMirroringTCPEndpoint -computername server01 -instancename mySQLInstance -endpointname testendpoint -endpointport 9999

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