sql server connect: We have designed a thick client accounting system using Microsoft Access 2003.

The thick clients connect to MS SQL Server 2000 or 2005 via ODBC.

This works well in a LAN environment. We have customers that want us to
connect their many branches together now. These branch offices are located in
different geographical regions, which could stretched over 100 to 200km apart.

We know that having these systems designed in Web would solve our problems,
but since we do not have much time and the budget to convert all these
systems from Access 2003 to web, we thought that the fastest way out is to
connect the thick clients (located in these branch offices) to the Head
Quarters (which has the centralised MS SQL server databases) is still using
ODBC over a high speed broadband from between 1 MBps to 3 MBps.

To increase security, we though of creating different VPNs connect each
branch to the HQ, and have all these branch systems linked via ODBC over VPN
to the HQ.

Please advise me whether the above concept is alright or not, in terms of:
1. whether such networking will work in the first place? any need of doing
further software modification?
2. whether using VPN would solve security problems during data transfer
between offices?
3. whether the existing broadband speeds, as mentioned above, is sufficient
to do the job?

thank you all in advance
JOSEPH YOONG

Joseph,
I think you'll find that ODBC over a VPN connection to have very poor
performance. The overhead of the VPN, combined with the overhead at the
application layer is a bad recipe for client/server connectivity. I've
seen this with a couple different OBDC/SQL based applications. The
bottom line is almost always to either port the application to something
web-based or to use Terminal Services with or without Citrix.

Greg

[quoted text, click to view]
Hi Greg

Thanks a million for the reply.

I understand what you mean.
In my country, I have tested this ODBC over VPN with a 2MB fibre optic
broadband speed and it works OK for now.

This is only 1 client connected to 1 server. The distance between the client
and server is about 2km away. Not sure how this will fair if we have the
following scenarios:
1. distances between 50km to 500km or
2. clients between 5 to 100 per server?

I am not sure whether 2MB (upload and download speed will be sufficient for
now)

Web-based solution will definitely solve my situation here, I know. But I am
not willing to spend another 2 years for a major re-write. I have taken about
6 years already to complete this software, when web based solutions were
still in their infancy stage. .NET technologies were not yet available then.

Is there a quick way of transferring my MS Access 2003 windows based thick
client to .NET 2 web based soluton? if yes, do let me know. I don't mind
spending some money (not time) to do the conversion.

Otherwise, please elaborate how terminal services can work. What I know
about terminal services is that everything is done by the server. The client
PC merely acts as a "dummy" terminal, only transferring key strokes to the
connected server. How can this improve the performance of client / server?

I have heard of Citrix before, but frankly not sure what this is. Could you
please help me understand.

Thanks
regards
joseph yoong