Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Desktop App with External SQL Server

Posted on 2011-09-09
10
Medium Priority
?
229 Views
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.
0
Comment
Question by:APD_Toronto
  • 5
  • 4
10 Comments
 
LVL 26

Expert Comment

by:Nick67
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.
http://mobile.experts-exchange.com/Q_27289198.html
RDP over fast ADSL is workable if there isn't too much latency
0
 
LVL 9
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:  http://www.fmsinc.com/microsoftaccess/cloud/link-to-azure-sql-database.html

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:

http://www.JStreetTech.com/downloads

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.

Best,
Armen
0
 

Author Comment

by:APD_Toronto
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:Nick67
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.
0
 

Author Comment

by:APD_Toronto
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?
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 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 www.experts-exchange.com
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
0
 

Author Comment

by:APD_Toronto
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.
0
 

Author Comment

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

Expert Comment

by:Nick67
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.
0
 

Author Comment

by:APD_Toronto
ID: 36552433
thanks.  i'll definite cosider
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

877 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