0

#PSTip Check if a SQL endpoint exists or not

Note: This tip requires PowerShell 2.0 or above.

Before we make an attempt at creating a new database mirroring endpoint, we need to verify if an endpoint with the given name and/or port already exists. We cannot create an endpoint with the same name or at the same port number. So, how do we validate this?

For checking if an endpoint with the same name exists or not, we can simply index into the Endpoints property of the SQL SMO Server class.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList $env:computername
$smo.endpoints["MyEndPoint"]

The above code returns the endpoint object, if it exists. We can use this as a condition to check the endpoint existence. For example,

if ($smo.endpoints["MyEndPoint"]) {
    #Do something
}

But, how do we check if the port number we intend to use for the new endpoint is already in use or not? Well, let us use the Get-SQLEndpoint function we created in an earlier tip.

if (!((Get-SQLEndpoint -ComputerName "MySQLServer" -InstanceName "MyInstance").ListenerPort -contains 7777)) {
    #Create endpoint
} else {
    Write-Error "An endpoint with port number 7777 already exists"
}
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: