Options
SQL Azure data compare hiccup
SkipSailors
Posts: 5 Bronze 2
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
"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
Comments
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.
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?
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.
I will repeatedly halve the size of the transactions and see I can find a size that that succeeds.