Scripting Options are listed on the second tab of the IDERA SQL Data Compare Options window. Those options affect the way the synchronization script is generated but do not affect the comparison operation.
- Transaction size. Determines the size of the script that will be wrapped in a single transaction. Depending on the size of the databases/tables being compared the synchronization script may get to be extremely large (for example, millions of insert/update/delete statements are needed to make database DB2 the same as DB1). Wrapping such large scripts in a single transaction may not be possible due to SQL Server transaction size limitations, and may not be acceptable for performance and other reasons. So, it is necessary to split the script into smaller "chunks" and wrap each of those "chunks" in individual transactions.
If the synchronization process is canceled or fails due to errors, only the current open transaction is rolled back. Changes committed prior to that transaction are permanent and cannot be reversed. To have an unlimited transaction size specify 0 as the max value. Having an unlimited transaction size ensures that all changes in the database are rolled back if an error occurs, but might be rejected by SQL Server.
Finding the right transaction size depends on many factors - please adjust this option carefully to suit your specific environment. - Batch size. Determines the size of data that is transmitted to SQL Server in one execution. A transaction normally contains many batches, but the batch itself is a single unit of work that cannot be divided any further.
Total compare from data to schema with IDERA SQL Comparison Toolset. Learn more > >