In my previous post Schema Comparison Stress Testing I mentioned problems we met testing dbForge Schema Compare for SQL Server on large database. In this post I would like to give you some tips on processing large synchronization scripts.
Problem Description
Suppose you have compared two databases and want to synchronize them. You tried to generate the synchronization script but it’s about 100 Mb and simply couldn’t be stored in the memory. When you open it in SQL editor for execution, the application fails with ‘Out Of Memory’ error.
Solution
There are two solutions for this problem:
1. Use direct synchronization mode.
You can select this mode on the Output page of the synchronization wizard by setting the ‘Execute the script directly against the Target database’ option. This gives you less control over synchronization process, but saves your process’ memory by generating synchronization statements on the fly.
Tip:
If any error happens in this mode, you can navigate from the Error List window directly to an object that caused the error. In the Schema Update Script window you can analyze the script fragment that caused the error.
2. Do not try to open the synchronization script
If you choose to generate the synchronization script which is over 50 Mb, the program will suggest executing this script directly instead of opening it. The restriction of this mode is that all the errors if any appear are not added to Error List. You must trace them from Output window.
I hope these tips will help you to handle large synchronization of large databases gracefully.
