|Product:||Syniti Data Replication, DBMoto|
|Summary:||Handling connection timeout and command timeout errors in Syniti DR/DBMoto|
If a connection timeout occurs when attempting a connection to the database server to process transactions, Syniti DR/DBMoto reports a connection error in the log, then retries the connection at the next mirroring interval.
Command timeout errors can cause replications to fail. They occur when, after initially connecting to the database server, the DBMS is unable to respond to a request from Syniti DR/DBMoto (via the .NET provider) within a time specified in the .NET provider connection string or the Syniti DR/DBMoto 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 DR/DBMoto 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
In this case, the timeout occurred because the Ritmo/i .NET Provider timeout setting (Init Timeout) is reached before the Db2 system responds to the Syniti DR/DBMoto request.
The following error occurred on SQL Server when a query took longer to respond than the time specified in the Syniti DR/DBMoto 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.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
Syniti DR/DBMoto is designed to recover from such errors during mirroring and synchronization by recording the last transaction processed and committing changes up to that point in the target database. However, if you see a pattern of timeout errors in the Syniti DR/DBMoto log, you should investigate and solve the problem. There can be many causes for such errors and most of them are database-specific. Here is some general information on how to handle timeout issues, followed by links to related knowledge base articles for specific databases.
The amount of time that Syniti DR/DBMoto 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 DR/DBMoto 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 the replication by selecting it in the Metadata Explorer and choosing Enable Replication from the right mouse button menu. The Enable Replication menu item should no longer display a check mark.
- Select the connection in the Metadata Explorer.
- From the right mouse button menu, choose Connection Properties to open the Connection Properties dialog.
- Scroll to the Dynamic Properties section.
By default, the Command Timeout value is set to -1, 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 DR/DBMoto. If, for example, you are using the Microsoft SQL Server .NET data provider and the timeout value is 20 seconds, you will set a higher value in the Syniti DR/DBMoto Command Timeout field.
- In the Command Timeout field, set the value to an appropriate number of seconds. The exact value depends upon your environment and what you are hoping to achieve by making the change.
- Scroll up to the General Properties section
- Click to highlight the Connection field.
- Click on the three dots ... at the end of the row to open the Connection Properties dialog.
- Scroll down 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.
- Click OK in the Connection Properties dialog.
- Enable the replication by selecting it in the Metadata Explorer and choosing Enable Replication from the right mouse button menu.The Enable Replication menu item should display a check mark.