SQL Azure data compare hiccup

SkipSailors

I wonder if I could get some insight on the following error message. I received this when I tries to comapre an on-premise db to an azure instance. This is the first time I have tried to do this with SQLCompare, so I am ready to accept n00b advice, here. I have been using SQL Data Compare for some years with on-prem DBs. My version reports 9.1.0.365

"The following error message was returned from the SQL Server:

[10054] A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

The following SQL command caused the error:

SELECT [PeopleID], [ForumID], [LastMark]
FROM [dbo].[ForumMarkRead] WITH (NOLOCK) ORDER BY [PeopleID], [ForumID]"

TIA

Chris Auckland

Thanks for your post.

Looking at the error message, it seems that this kind of thing is fairly common when connecting to Azure databases, so MS suggest the application needs to have some retry logic. [http://msdn.microsoft.com/en-us/library/ff394106.aspx]

Both SQL Compare and SQL Data Compare contain retry logic, so you shouldn't get this error because of a lack of retry attempts.

I've asked the dev team if they know what else might be up, and they think it could be caused by a firewall not being properly set up. This will cause the retry to give up and let the error through.

SkipSailors

That's an interesting idea. "firewall not being properly set up", ...
Which firewall?
The on-prem database is on a domain in a data center
I have a VPN connection to that database
I can query the on-prem database without trouble
I can open the Azure instance in SSMS and query it with not trouble

I don't see right off how Compare is hitting firewalls differently than SSMS. Can you describe how a firewall is "properly set up" in this context?

Chris Auckland

Sorry for the delay, I missed your last reply.

If you don't have any problems with SSMS, then it's unlikely to be a firewall problem (as you say).

One thing you could try would be to use the 'application option' to split transactions into a specified size. It might be the case that some larger transactions will never succeed, no matter how many times we retry.

SkipSailors

Better. The comparison was successful when I limited the transaction size to 10MB, and then failed in the Synchronization Wizard.

I will repeatedly halve the size of the transactions and see I can find a size that that succeeds.

Chris Auckland

That sounds like progress. Let me know how you get on.