Troubleshooting comparison and deployment performance problems
Published 05 June 2013
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 a long time.
If you're experiencing unacceptable performance while deploying data using the SQL Data Compare GUI, you may want to save the deployment 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 Deployment Wizard instead of clicking Deploy Now.
You might also consider using the command line interface (Professional Edition only) to create a comparison and deployment command and schedule it to run at a time when the SQL server is experiencing least activity.