#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"
}

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: