Thanks to Chris Yates for hosting this month’s T-SQL Tuesday (https://chrisyatessql.wordpress.com/2016/07/06/t-sql-tuesday-080/)! This month’s topic is fairly broad (a SQL-related present so I wanted to put together a quick post whose information definitely would have saved me some time if it had existed a few years ago.

I’ve seen this particular Always On issue occur at two different customers. Both would occasionally have issues where one (or more) of the replicas would fail to restart their communication within the availability group. The error message was usually something like this: The Database Mirroring endpoint cannot listen on port 5022 because it is in use by another process. 

In a large, complex corporate environment, the DBAs may not always have permission to run the netstat commands necessary to figure out what application is “stealing” the port. Even if they’re able to figure out which application is causing the port conflict, it may take a fair amount of time to change that application’s behavior.

Since a communication failure within an AG is usually a “hair on fire” kind of event, a quick fix may be desired. The quickest fix I’ve found is to change the port on which the AG endpoint is listening. While the Microsoft help pages on this contain enough information to put together the right script eventually, the first time this happened to me I really would have liked a blog post specific to this issue that gave me the right script to use.

In keeping with this T-SQL Tuesday’s theme, below is my SQL Server present. Please note that I used 5023 as an example but your choice can be flexible depending on what else is consuming ports on your machine. Hopefully this helps somebody (or me if I manage to travel back in time and encounter this same issue):

ALTER ENDPOINT [<endpoint name>]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
GO

ALTER AVAILABILITY GROUP [<AG name>]
MODIFY REPLICA ON ‘<replica name>’ WITH (ENDPOINT_URL = ‘<server name>:5023’)
GO

If this post helped you and you live in the Louisville, KY area, please consider registering for SQL Saturday 531 on August 6, 2016 and attend my Always On session there – you’re guaranteed to get what you pay for since SQL Saturdays are free!

Thanks for reading! This weekend I’m competing in rounds 3 and 4 of the The Hoosier Tire US Formula First Championship Series at Mid-Ohio Sports Car Course so I’ll be interrupting the SQL Server stuff usually posted here with some news about my first event of the 2016 racing season.

• AlwaysOn
• T-SQL Tuesday