Handling Connection Timeout Errors in Syniti Replicate

Summary

If a connection timeout occurs when Syniti Replicate attempts to connect to the database server to process transactions, it reports a connection error in the log and then retries the connection at the next mirroring interval.

Scenario A

Command timeout errors can cause replications to fail. These errors occur when, after initially connecting to the database server, the DBMS is unable to respond to a request from Syniti Replicate (via the .NET provider) within a time specified in the .NET provider connection string or the Syniti Replicate connection properties. Error messages such as the one below (in this case for a connection to IBM Db2 for i) are reported in the Syniti Replicate log:

System.Data.Sql400.Sql400Exception: Open Connection Error: WSAETIMEDOUT (10060): Connection timed out. 
A connection attempt failed because the connected party did not properly respond after a period of time, 
or established connection failed because connected host has failed to respond
   at System.Data.Sql400.Sql400Connection.Open()

In this case, the timeout occurred because the Ritmo/i .NET Provider timeout setting (Init Timeout) was reached before the Db2 system responded to the Syniti Replicate request.

Scenario B

The following error occurred on SQL Server when a query took longer to respond than the time specified in the Syniti Replicate Command Timeout setting. In this particular case, the exception was thrown when reading the transaction log.

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()....

Syniti Replicate is designed to recover from such errors during mirroring and synchronization by recording the last processed transaction and committing all changes up to that point in the target database. However, if you notice a recurring pattern of timeout errors in the Syniti Replicate log, you should investigate and resolve the underlying issue. These errors can have many causes, most of which are specific to the database environment.

Solution

The amount of time that Syniti Replicate waits before returning a timeout error to the log depends, by default, on the timeout setting determined by the .NET Data Provider connection properties. You can change this setting either via the .NET data provider or you can override it using the Syniti Replicate Connection Properties dialog. Changing the default timeout value can affect the performance of your replications and should be carefully tested.

To modify the default command and connection timeout values in the Management Center (Enterprise Manager):

If your replication is already running, disable it by right-clicking the replication in the Metadata Explorer and selecting Enable Replication. The Enable Replication menu item should no longer display a check mark.
Select the connection in the Metadata Explorer.
Right-click the connection and select Connection Properties to open the Source Connection Properties dialog box.
Access the Dynamic Properties section.
By default, the Command Timeout value is set to 60, which means that the value provided by your .NET data provider will be used. Before proceeding, check to see what value is used by your .NET provider so that you have a general idea what value to set in Syniti Replicate.
To ensure stable operation in high-latency environments, configure the Syniti Replicate Command Timeout to a value between 180 and 300 seconds, regardless of the provider’s lower default.
Access the General Properties section and select the Connection field.
Click the ellipses (...) button at the end of the Connection field.
On the Connection String list and modify the value in the Connect Timeout field. Set the value to match the setting of the Command Timeout field.
Click OK to close the dialog box.
Click OK in the Connection Properties dialog box.
Enable the replication by right-clicking it in the Metadata Explorer and selecting Enable Replication. The Enable Replication menu item should display a check mark.