Desktop App with External SQL Server

Posted on 2011-09-09
Last Modified: 2012-05-12
Hello Experts,

Currently i  have an app wit the user interface and database both in Access (seperate files),.  As I'm planning to migrate the DB to an offsite SQL server(accross the country at that)with also an online portal accessed world wide, I'm worried about connectivity speed and dropped connections.

I'm not a network person by far, but my client expiriences too many dropped connections (maybe i should contact their service provider who is Rogers DSL Canada), also my connection is steady (also DSL), but I've noticed when connecting to the external server via SQL Management it was slower then when connecting to a local DB.

I'm looking for suggestions on how to make this quick and stable, as this is a dispatch application in a fast-paced environment.

Some random ideas i've had were moving the server close and somehow having a direct connection to the data center (if that is even possible), or having a failover server at the client and at the host, or having a T1 connection, which I am not sure what it is? I would like to keep the server outsoursed as my client does not have any IT department.

Any ideas will be appreciated.
Question by:APD_Toronto
  • 5
  • 4
LVL 26

Expert Comment

ID: 36515242
In all seriousness, Access is not the front end for what you are describing.  I am beginning to explore Azure, but that has distinct limits.
RDP over fast ADSL is workable if there isn't too much latency
ID: 36516825
We have Access-SQL Server apps that run just fine over WAN connections.  They run more slowly than over the LAN, but it is workable.  They tolerate slow & dropped connections very well - SQL Server doesn't get corrupted like an Access BE would.  The main issue is a) how fast and reliable you need your app to be, and b) how good your connection to the server will be.

I don't know how SQL Azure would compare with a hosted SQL Server database in a data center.  There are pros and cons.  You'll have more control and flexibility with your own database on your own (shared or private) server, but the simple subscription model of SQL Azure has its benefits too.  Luke Chung at FMS has some articles on using SQL Azure with Access:

You'll definitely need to optimize your Access application in either case.  I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

If Access doesn't seem like the answer because of your need for speed, then you'll need to start thinking about a rewrite in ASP.NET, which is a much bigger effort and a whole new learning curve if you aren't familiar with it.


Author Comment

ID: 36516852
For the remote desktop we are talking about 5 or 6 simultaneous users - can you use thats many RDP connections at once?

The best way to describe my appication is a taxi/delivery service, where reservations come in constantly, but there are few more details then just From/ To.

If I were to rewrite in ASP Classic, then we will be looking at time outs and some wait times, not to mention development time.

What I was acually looking for were hardware suggestion like moving the physical server closer or adding another failover server at the client site or changing connection types.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 26

Expert Comment

ID: 36517283
It has been my experience that when Access loses it's network connection, DAO based files bomb, and badly.  Now, SQL Server based backends don't get corruption, but the frontends can and do.  I have tried running local frontends to VPN backend on Telus ADSL in Alberta.  Forget itm. It's not happening.  Our Poweredge 1900 runs 7-8 RDP clients and hosts the backend without breaking a sweat.  3G+ clients work, but are slow and flakey due to 3G+'s high latency.  Physical proximity is not the issue.  Network robustness, latency and speed are.

Author Comment

ID: 36525584
are you saying I can run several of RDP sessions at the same time?  If so, I assume with different user IDs?

I'm more leaning towards sticking with Access as the front-end, and SQL as ack-end.

to give you an idea, my client is in torono, but the server is in vancouver.

Perhaps, i should be asking, what would be ideal upload/download speeds and what utility i can use to measure?
LVL 26

Accepted Solution

Nick67 earned 500 total points
ID: 36525711
If you license the server as a terminal server, you can run as many concurrent RDP sessions as you have licences and the server's hardware could support.
Virtually any server purchased in the last 3-4 years should be able to support 3-5 concurrent users without problem
<ideal upload/download speeds> 10 Gbps if you could get them <grin>
But you can't.
Speed is not really the limiting factor for RDP.
You aren't passing huge amounts of data.
To grasp the idea--satellite internet is fast, you can download large amounts of data quickly.
It's crappy for RDP.
Your mouse click has to go to orbit and back before anything happens.
That's latency, and high latency networks are crappy for RDP.

Shaw runs a fake 10.x.x.x addressed network.
RDP to static IP'd Telus servers is murderous, because they expect you to be accessing cached web pages, not ACTUALLY doing stuff on the internet.
I don't know what Rogers is like.

Have your Toronto guy do a tracert to your Vancouver server.  Look at hop many hops it takes, and how many ms each hop takes.
Fewer hops and low ms are best.

Here's my tracert to
Why it routes to NewYork before going to SoCal is anybody's guess, when I am in northern Alberta EE tracertHere's one to Telus's own server.
Much better
 Telus tracert

Author Comment

ID: 36526001
for ee i get 19 hops, 179ms w/1 timeout

for my web server i get 9 hops, 163ms, 2 timeouts

for my SQL server, hops 1-6 were 6ms, then 7-30 timed out.

What does all this mean?

<<Speed is not really the limiting factor for RDP.>>
but lke i said, i'm lening more towards keeping the FE in ACCESS on the users desktop, versus RDP, thats why i ask re supload/download speed.

Author Comment

ID: 36526037
Also, re- RDP. you aren't permitted to install Office on a server, correct?
LVL 26

Expert Comment

ID: 36552417
I thought I had put another post in here, but it must have gotten lost.
You can install Office on a server.
No Problem.
You can install the Access 2010 runtime on the server, which is free, also.

What you cannot legally do is install Office on a server and have machines that DON'T have Office installed accessing it as a means to avoid licence fees .
Other than that, it's all good.

Author Comment

ID: 36552433
thanks.  i'll definite cosider

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question