Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



MSrepl_errors - TCP Provider: The semaphore timeout period has expired & Communication link failure
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-06-15







Scenario:

Source:
A Windows Server 2012 R2 Dual-Node Clustered Server, in Local Data Center, running Microsoft SQL Server 2014 Standard Edition.

Destination:
- Microsoft Azure Virtual Machines (x 8) running Microsoft SQL Server 2014 Web Edition.
- 4 VM's in Azure East US2 Region
- 4 VM's in Azure Central US Region

Data Replication Configuration:
- 30GB OLTP DB with 10 tables set for Replication.
- Microsoft SQL Server Transactional Replication sending data real-time in Push Mode.
- Distribution DB hosted on Source Server

Networking:
Virtual Network between Local Data Center and Microsoft Azure Cloud.


Issue:

Two unique errors seen in Distribution DB on a daily basis:

select * from Distribution.dbo.MSrepl_errors order by [time] desc

"TCP Provider: The semaphore timeout period has expired."
"Communication link failure."


Resolution:

Modify the Distribution Agent Profile "-LoginTimeout" setting from default of 15 to 45.


From MSDN:

-LoginTimeOut login_time_out_seconds
Is the number of seconds before the login times out. The default is 15 seconds.


Resources:

Replication Distribution Agent