| Product: | Syniti Data Replication, DBMoto |
| Version: | All |
| ID: | 1603 |
| Summary: | How to optimize performance in replicating to SQL Server using the BulkInsert option in replication configuration. |
Q: We are replicating from an IBM i/iSeries/AS400 to SQL Server and some of the files are huge, up to 75M records. What is the best way to set up the replication? Should I use a SQL Server DTS package?
A: We have recently performed some tests measuring the speed of DTS versus Syniti DR/DBMoto using the SQL Server .NET Data Provider to connect to SQL Server, and using the default bulk insert setting for the replication. Syniti DR/DBMoto was faster than DTS when configured this way.
- Configure the source connection using Ritmo .NET provider to access data on the i/iSeries/AS400.
- Configure the target connection using SQL Server .NET Data Provider to connect to SQL Server.
- In the Replication Properties, go to the Preferences tab.
- Locate the Insert Mode property.
- Check that the value is set to BulkInsert.
- Specify the block size. The typical range is 100 to 200, but you should try different values to achieve optimal performance. A block size of 100 is recommended for medium size tables. If the records are large, reduce the block size, and if the records are smaller, increase the block size.
- Save the replication properties.
- Test the settings by running a replication to check improvements in performance.
- If necessary, adjust the block size.