Configuring SQL Server Always On Availability Groups requires precise setup of various components, including the endpoints that facilitate communication between replicas. Error 35106 indicates a conflict in the TCP port allocation for these endpoints, a situation that can halt the configuration or expansion of your Availability Groups. This guide by SQLOPS walks you through resolving this port conflict to ensure seamless communication within your Always On setup.
Exploring Error 35106 in SQL Server
Encountering Error 35106 signals a TCP port conflict where the designated port for a SQL Server endpoint is already in use, possibly by another instance of SQL Server or a different application, leading to issues in establishing or maintaining Availability Groups.
Common Causes for Error 35106:
- Overlapping endpoint configurations across SQL Server instances.
- Port conflicts with non-SQL Server applications.
Steps to Overcome Error 35106
Identify the Port Conflict
Objective: Determine which application or service is currently using the port designated for your SQL Server endpoint.
Use Netstat or TcpView:
Tools like netstat in command prompt or TcpView from Sysinternals can help identify which process is bound to the conflicting port.
Reconfigure the Existing Endpoint
If the conflicting port is currently assigned to another SQL Server endpoint, consider reconfiguring that endpoint to use a different port, thereby resolving the conflict.
Alter Endpoint Command:
Adjust the port of the existing endpoint using T-SQL, ensuring it no longer conflicts with the intended port for your new Availability Group endpoint.
ALTER ENDPOINT [ExistingEndpointName]
STATE = STARTED
AS TCP (LISTENER_PORT = [NewNonConflictingPort])
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES);
Configure a New Endpoint
With the port conflict resolved, proceed to configure your new endpoint for the Always On Availability Group on the now available port.
Create Endpoint Command:
Create the new endpoint using T-SQL, specifying the unique port that was previously in conflict but is now free for use.
CREATE ENDPOINT [NewEndpointName]
STATE = STARTED
AS TCP (LISTENER_PORT = [PreviouslyConflictingPort])
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES);
Validate Endpoint Configuration
Ensure that the newly configured endpoint is operational and not experiencing any conflicts, enabling effective communication for Always On Availability Groups.
Check Endpoint Status:
Verify the status and configuration of your SQL Server endpoints, ensuring that the new settings have taken effect without issue.
SELECT name, state_desc, port FROM sys.database_mirroring_endpoints
Error 35106, while indicative of a common configuration pitfall within SQL Server Always On setups, can be effectively managed with careful attention to port allocations and endpoint configurations. By following SQLOPS’s guidelines, administrators can swiftly resolve port conflicts, ensuring robust communication pathways for Always On Availability Groups.
Encountering configuration challenges with SQL Server Always On Availability Groups? SQLOPS provides expert guidance and support, helping you navigate the intricacies of SQL Server to achieve optimal database performance and reliability. Explore our range of services and discover how we can assist in enhancing your SQL Server deployments at SQLOPS.
