2

#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 {
    [CmdletBinding()]
    param (
        [string]$computername=$env:COMPUTERNAME,
        [string]$instancename,
        [string]$endpointname,
        [int]$endpointport
    )

    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])) {
                    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
                        $SQLEndPoint.Create()
                        $SQLEndPoint.Start()
                        $smo.Endpoints[$endpointname]
                    } 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
Filed in: Columns, Tips and Tricks Tags: , , ,

2 Responses to "#PSTip Creating a SQL TCP mirroring endpoint with SMO and PowerShell"

Leave a Reply

Submit Comment

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