What are the challenges you face when working across database platforms? Take the survey

SQL Data Compare Hangs

acgrittacgritt 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.


  • Options

    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!
  • Options
    It is the build server (CC.NET) that is terminating the task. However, when the task is run from the Console version (akin to running from the command line) the task runs fine and the whole build process with Data Compare takes about 4 minutes. Under the Service version, CC.NET is setup to terminate the process if it takes more than 25 minutes to run which is what is happening here.
  • Options
    I'm not sure I can be of any more help. SQL Data Compare engine is never going to just disappear. If a problem occurs it will throw an exception. So maybe your configuration is not noticing exceptions thrown by the CompareDatabases method. The only other possibility I can think of is that your CI server is terminating the task because it is taking too long.
  • Options
    For better clarification here is what is happening:

    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).
  • Options
    The only part of Data Compare Engine that requires UI interactivity is the licensing part, if you haven't got a Toolkit license on the appropriate machine. But I'd have thought you'd have noticed that if it was running as a console application.
    Software Developer
    Redgate Software
  • Options
    Something you could try if you've got Visual Studio installed on that machine is to attach the debugger to the service process when it's in the "hung" state, then hit pause and see exactly what's going on in the call stack of any active threads - that might give some more clues as to where things are going wrong.
    Robert Chipperfield
    Red Gate
Sign In or Register to comment.