SQL Data Compare Hangs
acgritt
Posts: 4
I have created a MSBuild task for my company that does SQL Schema Compare and Data Compares. As of late we have added a few more projects to our CC.NET build server and we noticed several timeout issues. When I traced down the problem it was in the Data Compare code when I called the following line:
ComparisonSession session = new ComparisonSession());
session.CompareDatabases(_database1, _database2, tableMappings, SessionSettings.Default);
At this point the MSBuild process will start bouncing between 65 and 100 percent processor usage and never returns from this call. When I run things normally via the command line or via the CC.NET console this runs fine, but when it runs as part of a service is when it hangs. To test this I created a service that does nothing but call the msbuild script and the same thing happens. I have even tried upgrading from version 6 to version 7 of the Red-Gate SQL Toolbelt and have the same results.
Does anyone have any ideas what could be causing this? I am comparing 2 SQL 2005 databases, one that I just created and the other is the "static" release version.
ComparisonSession session = new ComparisonSession());
session.CompareDatabases(_database1, _database2, tableMappings, SessionSettings.Default);
At this point the MSBuild process will start bouncing between 65 and 100 percent processor usage and never returns from this call. When I run things normally via the command line or via the CC.NET console this runs fine, but when it runs as part of a service is when it hangs. To test this I created a service that does nothing but call the msbuild script and the same thing happens. I have even tried upgrading from version 6 to version 7 of the Red-Gate SQL Toolbelt and have the same results.
Does anyone have any ideas what could be causing this? I am comparing 2 SQL 2005 databases, one that I just created and the other is the "static" release version.
Comments
Ordinarily, an API methof like CompareDatabases would not simply terminate -- either the method completes or throws an exception. I do know that with Continuous Integration tools such as NAnt, there is normally a configurable timeout value that will terminate the running process when the timeout is reached.
Based on that I would assume that MSTask is terminating the data comparison operation because of the timeout. Can tasks run by MSTask be given a really long timeout? Can you run your SDK program interactively to see how long it should take and set the timeout higher?
If not, you may want to look into tweaking the comparison session so that it compares less data by implementing where clauses or comparing only a few tables at a time in batches.
Hopefully this helps!
Scenario 1:
Console application runs that shells out to MSBuild to run a build script which includes calling a custom extension that does a SQL Database Data Compare using the SQL Comparison SDK. This task runs for 4 minutes and completes without issue.
Scenario 2:
Windows service runs that shells out to MSBuild to run the same build script from Scenario 1. The task runs for 25 minutes before the service terminates the process because it is taking too long.
Both the Console Application and the Windows Service run under the same user account. Nothing else is running on the machine in either scenario. No exceptions are thrown by the SQL Comparison SDK in either case. The only noticeable difference is under the windows service as soon as the code calls CompareDatabases on the CompareSession object is called the method never returns and runs for the 25 minutes at peak processor usage until it is terminated by the service.
I am trying to figure out what would be causing the CompareDatabases to not return properly when it is run under the Service context versus the Console context. Does the Data Compare require UI interactivity even from the SDK calls? Is there something about running under a windows service that the SDK doesn't handle well when doing a Data Compare even when the service is setup to run under the same user account as the console application? I have noticed that it doesn't matter if I use NT Authentication or SQL Authentication. In both cases the processor spikes and is never heard from again. To further test I set the timeout on the service to 60 minutes. Even then the process never exited until it was terminated by the service. The databases being compared have minimal data in them (enum tables).
Redgate Software
Red Gate