Solved

Desktop App with External SQL Server

Posted on 2011-09-09
10
218 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 26

Accepted Solution

by:
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 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Many of us in IT utilize a combination of roaming profiles and folder redirection to ensure user information carries over from one workstation to another; in my environment, it was to enable virtualization without needing a separate desktop for each…
I'm a big fan of Windows' offline folder caching and have used it on my laptops for over a decade.  One thing I don't like about it, however, is how difficult Microsoft has made it for the cache to be moved out of the Windows folder.  Here's how to …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now