Suggestions to combat comparison and synchronization performance issues

Category: How do I?
Date: 20 May 2009
Product: SQL Data Compare
How can I optimize SQL Data Compare to decrease the time taken to compare and synchronize large amounts of data?

SQL Data Compare can take a long time to compare databases depending on the environment. SQL Data Compare extracts the data from both databases and copies
it to a temporary location on the hard disk of the local computer. Once it has copied the information from both databases to a temporary folder, it then compares the data and returns the results.

If the databases being compared are not on the same computer as SQL Data Compare, the data has to be copied over the network to the local machine. Depending on the speed, location of database, disk write speed, memory, size of database, and processor speed, this process can take varying amounts of time.

If you are experiencing unacceptable performance while synchronizing data using the SQL Data Compare GUI, you may want to save the synchronisation script to a file, copy it over to the target SQL Server and run it locally using a SQL query utility such as Management Studio. You can save the script by selecting 'save script' on the last stage of the synchronisation wizard instead of clicking 'synchronize now'.

You might also consider using the command line interface (Professional Edition only) to create a comparison and synchronization command and schedule it to run at a time when the SQL server is experiencing least activity.

Document ID: KB200710000166 Keywords: SQL,Data,Compare,slow,remote,server,performance

Was this article helpful?

Search support