Remote replication sql 2000 -- win CE device

Question

Remote replication sql 2000 -- win CE device

Matthew Twynham

Old Hand

Points: 311
More actions
November 16, 2005 at 3:01 am

#92031

I have set up an L2TP tunnel for handheld CE devices to log into our network, all of which works perfectly, however, even though they can see the SQL server and can actually communicate with it, when the server tries to send teh database back to teh handheld it hangs after about 40 minutes.

It is only 1-2mb database, should take 10 minutes. I know the system works correctly, because it is still working through teh old L2TP connection, which unfortunately I don't have access to, to copy the settings. I don't know a huge amount about SQL, but I need to know if there are any specific firewall rules I should be setting to allow the data throughput?
Any suggestions would be much appreciated.

Matt

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Ian Yates SSCoach Points: 19738 More actions · Answer 1

Can your device copy a 1-2MB file successfully in an appropriate amount of time? I've seen some connections on desktops (not much experience with handheld and VPN though) that would appear to work fine, but either hang when sending files on one or both connections... Ended up being a firmware issue. If you can't copy a file of that size in a reasonable time, then SQL will work no better...

If the file copy does work... Hmmm... Port 1433 is used by SQL. Does even part of the DB come across - does the sync start?

Hope that might give you a pointer in the right direction

Matthew Twynham Old Hand Points: 311 More actions · Answer 2

Yes can copy by serial port in 10 minutes, have opened port 1433 in both directions.

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

I believe that you need to be able to communicate to the RDA "proxy" server (The one running IIS with the replication DLLs registered) on TCP port 80. The RDA server then talks to SQL on port 1433.

In my environment that is how it works, anyway.

hth jg

Matthew Twynham Old Hand Points: 311 More actions · Answer 4

Thanks Jeff, can you be a bit more specific, at present I have port 80 open inbound and outbound, I also have port 1433 open, do I need to specifically point these ports to the SQL server on 192.168.5.12 or do I just open the ports?

I think the IIS server is the same one, what is RDA server

I am also getting loads of fragmnted packets showing up on the firewall log, but strangely they are from the router IP address that the L2TP tunnel uses, not from the SQL server address.

Thanks

Jeff Gray SSChampion Points: 10667 More actions · Answer 5

Hello,

Something ATE the post I just tried to make.

You should only need to be able to communicate to your IIS server through the tunnel. This IIS server is the one that hosts the RDA replication dlls. (sscesa10.dll or sscesa20.dll) The RDA components communicate with the distributor and publisher via whatever TCP port you're using for SQL Server.

In my case, the IIS/RDA server is the same physical server as the distributor.

I'm not sure what to make of the fragged packets, without seeing them. I would suspect that if you can perform other functions properly through the tunnel, that at least the tunnel is working properly. There may be some issues with MTU that can be tuned around, but I've never had to do that.

jg

Matthew Twynham Old Hand Points: 311 More actions · Answer 6

Yes we have the same set up and the CE device actually suceeds in making a connection to the SQL server, it talks to the server database, becuase it shows a success, but then it just won't replicate.

I have changed the MTU size on the router to 576 was 1500, which I have trawled around the ineternet and seen as a likely figure, this seems to stop firewall showing the fragment packets being dropped, but replication still starts and never finishes.

Have been on this job for weeks and hair loss is now becoming a problem, so really appreciate your help.

Ian Yates SSCoach Points: 19738 More actions · Answer 7

Do your IIS logs have anything of interest in them? You could have IIS log a lot more info and see if there are any connection dropouts, etc.

Jeff Gray SSChampion Points: 10667 More actions · Answer 8

So you're saying that the Merge Agent on the SQL server indicates success, but the CE side fails? I love it when that happens.

Are you able to get at the error collection from the replication object on the CE device?

This would be the error collection that results from the call to the run method of the replication object in whatever CE client code you're using.

Matthew Twynham Old Hand Points: 311 More actions · Answer 9

There is no error collection because the egg timer stays on permanently and you have to crash the device, which then gives a disconnect error.

HELP!!!!

Jeff Gray SSChampion Points: 10667 More actions · Answer 10

I suspect that it is indeed a network problem, but I think that it would be a good idea to go back and double-check. Confirm that everything works as expected in the scenario that you believe is working. You've probably already done this

If the problem is confirmed to be due to the tunnel, which it probably is, then I would suggest that you take some network traces of both scenarios (1. Working and 2. Not working)

Compare the traces and see what traffic is not coming out the other side.

Jeff Gray SSChampion Points: 10667 More actions · Answer 11

OOPS. wrong button.

Hopefully you can set up the client side of the tunnel endpoint in such a way as to be able to capture packets from that side. If not, try it from the other side. If the tunnel is causing the problem, you will probably get more information from the client side as to what's missing.

If that leads nowhere, then you might have yet a latency issue with the tunnel, caused by packet sizes, retransmissions, or who knows what. These problems should show up in a trace, but they may not.

If that doesn't get you anywhere, I'd try creating a new publication with a much smaller slice of data, and try to subscribe to it.

I hope this can be solved, but it is getting to where it may not be fixable via this forum. I'll keep trying, though, as time permits.

hang in there.

jg

Jeff Gray SSChampion Points: 10667 More actions · Answer 12

Forgot to mention: I use Ethereal fior network tracing

It's free and it works very well http://www.ethereal.com

Matthew Twynham Old Hand Points: 311 More actions · Answer 13

Thanks for your help, do you think that there could be a possibility that the SQL server is trying to send the data back to a completely different IP address, not the handheld one??

Jeff Gray SSChampion Points: 10667 More actions · Answer 14

I believe that the SQL server is communicating with the RDA components on IIS, not directly with CE. While it is possible, it is not too likely that the IP addresses are getting hosed.

Consider that in order to even make a connection via TCP, there is the 3-way handshake that has to take place.

the SYN, SYN ACK, SYN ACK ACK tango, so to speak. These packets traverse the IP network when the client tries to open a TCP socket. Once that completes, the socket is opened and the client can send the request to the replication component.

Try using the web browser on the CE device, and navigate to the /replication/sscesaXX.dll on the server. You should get a page back that just has the word "BODY" in it. At least that's what the sscesa10.dll does.

I'm pretty sure that the TCP, and thus the IP is at least using the right addresses, or the merge agent wouldn't ever get the message to start working. There must be something else in the mix.