ftp snapshot - "The connection to the server was Reset"

[Steve S]

Hello all. I have a 285mb snapshot that I am trying to FTP to another SQL
Server. After I get the message "Bulk copied data into table xyz", I get the
following error messages:

Category:OS
Source:
Number: 12031
Message: The connection with the server was reset

Then, I get the error

The process could not retrieve file '.....table.idx' from the FTP
site 'x.x.x.x'. The step failed.

I realize that the ftp connection must have been broken, but I don't know why.
It's especially strange that it happens AFTER the .bcp file is ftp'd and then
bulk copied.

This works for a 29mb snapshot, but not the 285mb snapshot. Any ideas how to
fix this problem?

Thanks in advance for your help.

Steve S
s...@hmbnet.com

[JayDee]

Hi I have also experienced this problem today

I believe I have diagnosed why the problem is occurring but have no idea how
it can be fixed

basically the way SQL replication handles its FTP transfers is pretty
stupid.

Replication agents do not FTP all their files before processing them.
Instead, and for reasons I DON'T understand they transfer some files, then
process them and then transfer some more.

This works fine when your BCP's are quite small, but if they're of any large
size this can mean that the merge agent can take over 10 minutes to import
the data into the destination table on the destination DB.

This is enough time for any firewall hardware (USING DYNAMIC NAT / PAT) to
reset the FTP connection, because its been idle for a long period

SQL then tries to use this connection to get the rest of its files, assuming
(incorrectly) that it will still be available.

Because the firewall has reset the connection, it is no longer available,
the subscriber will not be able to retrieve any further files, and
replication will fail. the agent doesn't attempt to make a new connection

This seems to be VERY poor design on behalf of MS, I feel.
Wouldn't it be better to transfer all files over FTP and THEN process them?
Or if this is not possible then always retry a new FTP (open) command to the
target FTP server after one has failed?

If ANYONE out there can tell me a workaround or this, perhaps a way of
making the Merge agent to 'retry' the connection, or send keepalive packets
to keep the existing connection open, I'd be very very grateful!

JD

"Steve S" <s...@hmbnet.com> wrote in message
news:148d01c0e549$244cf350$b1e62ecf@tkmsftngxa04...