SQL Server over the Internet...big picture questions

[Tom]

All:

I’m looking for some suggestions given the following scenario:

Current Scenario:
Typical FE/BE MSAccess database, nothing special. One main table with
several thousand records (that will stay fairly static), and one
significant child table that will grow quarterly by 1 record for each
record in the main table. Several other support tables, but they are
small in comparison. Lots of business logic embedded in the code.
Been in use for 7 years so it is reasonably well tested out, but
showing its age a little.

The database is used to manage data from multiple facilities. Due to
original design decisions, each facility has its own separate BE
file. The FE allows the user to choose which BE to link to.

The system was designed for one client company and will not be sold/
used by anyone else (in other words, I only need to satisfy his needs
and don’t really need to worry about a wider audience).

Problems:
1. The client company has grown a little now and they have more than
one person working with the data at a time…everything used to be on a
single hard drive. That won’t work anymore.
2. Some of the users are now traveling and would like to access the
data while out of the office.
3. I don’t trust their backup plan – I think its haphazard at best,
non-existent at worst.

The client company is a small business – maybe 7 employees total and
not particularly computer savvy. Keeping individual desktop computers
running is about their limit of expertise.

Solution?:
I’d like to move their data to a hosted SQL Server database accessible
by the MSAccess FE now in use (modified as needed). I THINK that will
solve all three of the problems listed above, plus allow me easier
access to the data to help them out of the periodic problems they
encounter (I am physically remote from the client company). I
specifically do not want to move to a web based application – I’m
simply not willing to give up the MSAccess environment for a browser.

I have a fair amount of experience with MSAccess, some, but not much,
experience connecting to in-house SQLServer in a corporate environment
at my day job, and NO experience connecting to SQLServer over the
internet.

Questions:
• Will this work? If not, any alternate suggestions?
• What kind of speed can I expect? Especially when fetching large
number of records for reports (this is a report intensive database –
lots of different ways of slicing and dicing data for hard copy
printout).
• What programming gottchas should I be aware of that are unique to
connecting to a hosted SQLServer?
• How do I go about making local backup copies of the data in case
access to the hosted service is interrupted?
• How secure is this solution. There isn’t any financial data, etc
that would be a big draw to a hacker, but without the data the company
is screwed big time.
• Any suggestions on a host service that is reliable and has good tech
support?

Thanks

Tom

 

[Arvin Meyer MVP]

Before I answer anything. You MUST design and implement a good backup
system. It doesn't matter what else you do, no backup, no business. If the
building burned down and there was and offsite USB hard drive with the data.
You could be up and running in days.

If the server went down and the data was lost, it might be months before you
could restore some semblance of business order. In today's business
environment, that translates to bankruptcy is all but the most cash fat
environments.

If nothing else, get 2 500 GB USB drives, alternating one in use with one
offsite. If the business is worth more than $150 (the price of 2 drives) do
it now.

Now, for your questions:

• Will this work? If not, any alternate suggestions?

Yes, but there are better and probably cheaper solutions. Web apps need to
be just that. Running Access over the web, except on a VPN is neither secure
nor fast. It will take forever to render forms reports. So the solution is
to use Terminal Services which will run on the server at the central
business location, and not require any outside services.

• What kind of speed can I expect? Especially when fetching large
number of records for reports (this is a report intensive database –
lots of different ways of slicing and dicing data for hard copy
printout).

You will need to redesign the app as asp/asp.net to deliver reports with any
reasonable speed. Terminal server requires no redesign and runs almost as
quickly as the original app.

• What programming gottchas should I be aware of that are unique to
connecting to a hosted SQLServer?

Not many if you build a web app, but you will need a web app.

• How do I go about making local backup copies of the data in case
access to the hosted service is interrupted?

OOPS. Not secure to walk around with data on a laptop, but if you must, you
might just as well load it before you leave, and not use the web.

• How secure is this solution. There isn’t any financial data, etc
that would be a big draw to a hacker, but without the data the company
is screwed big time.

So why aren't you insisting on a backup? A VPN, whether it's a web app or a
terminal services app is very secure.

• Any suggestions on a host service that is reliable and has good tech
support?

I use GoDaddy for my web apps, and the client company servers for Terminal
Services. Yes there are gotchas with RDPs (Terminal Server connections) as
well. First, if you need more than 2 concurrent connections, you'll need a
licensed server. For Microsoft or Citrix, that can be expensive. But you can
use WinConnect very cheaply.

http://www.thinsoftinc.com/download_form.aspx?pid=wcsxp

The second (and only other gotcha I've ever encountered) was that printing
was impacted. The IT guys fixed that by buying a program, but I'd have to
research that. My biggest project over RDP (30 connections) didn't have that
problem.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

All:

I’m looking for some suggestions given the following scenario:

Current Scenario:
Typical FE/BE MSAccess database, nothing special. One main table with
several thousand records (that will stay fairly static), and one
significant child table that will grow quarterly by 1 record for each
record in the main table. Several other support tables, but they are
small in comparison. Lots of business logic embedded in the code.
Been in use for 7 years so it is reasonably well tested out, but
showing its age a little.

The database is used to manage data from multiple facilities. Due to
original design decisions, each facility has its own separate BE
file. The FE allows the user to choose which BE to link to.

The system was designed for one client company and will not be sold/
used by anyone else (in other words, I only need to satisfy his needs
and don’t really need to worry about a wider audience).

Problems:
1. The client company has grown a little now and they have more than
one person working with the data at a time…everything used to be on a
single hard drive. That won’t work anymore.
2. Some of the users are now traveling and would like to access the
data while out of the office.
3. I don’t trust their backup plan – I think its haphazard at best,
non-existent at worst.

The client company is a small business – maybe 7 employees total and
not particularly computer savvy. Keeping individual desktop computers
running is about their limit of expertise.

Solution?:
I’d like to move their data to a hosted SQL Server database accessible
by the MSAccess FE now in use (modified as needed). I THINK that will
solve all three of the problems listed above, plus allow me easier
access to the data to help them out of the periodic problems they
encounter (I am physically remote from the client company). I
specifically do not want to move to a web based application – I’m
simply not willing to give up the MSAccess environment for a browser.

I have a fair amount of experience with MSAccess, some, but not much,
experience connecting to in-house SQLServer in a corporate environment
at my day job, and NO experience connecting to SQLServer over the
internet.

Questions:
• Will this work? If not, any alternate suggestions?
• What kind of speed can I expect? Especially when fetching large
number of records for reports (this is a report intensive database –
lots of different ways of slicing and dicing data for hard copy
printout).
• What programming gottchas should I be aware of that are unique to
connecting to a hosted SQLServer?
• How do I go about making local backup copies of the data in case
access to the hosted service is interrupted?
• How secure is this solution. There isn’t any financial data, etc
that would be a big draw to a hacker, but without the data the company
is screwed big time.
• Any suggestions on a host service that is reliable and has good tech
support?

Thanks

Tom

 

[Tom]

Arvin:

Thanks for the thoughts and suggestions. Regarding backups, I've done
everything I can short of buying a plane ticket and flying there to
plug the external hard disks in myself. Unfortunately, that is not in
my scope of work, and while I like the owner of the company, not so
much that I am willing to buy the ticket out of pocket!

What I take out of your response is that unless I redesign as a web
app, run across a VPN or on Terminal Server, response time will be
unacceptable. A web app is a no-go. We are all comfortable with the
MSAccess UI and don't want to change. I don't know much about
Terminal Server, but I infer from your response that the company would
need some additional hardware/software to host it... that's a non-
starter too. The company using the database can barely keep their two
desktops operating... to say they are not computer savvy would be a
gross understatement.

So I guess that means finding a hosting site that will provide not
only the SQLSERVER but also a VPN connection.

Thanks

Tom

 

[Armen Stein]

Arvin:

Thanks for the thoughts and suggestions. Regarding backups, I've done
everything I can short of buying a plane ticket and flying there to
plug the external hard disks in myself. Unfortunately, that is not in
my scope of work, and while I like the owner of the company, not so
much that I am willing to buy the ticket out of pocket!

What I take out of your response is that unless I redesign as a web
app, run across a VPN or on Terminal Server, response time will be
unacceptable. A web app is a no-go. We are all comfortable with the
MSAccess UI and don't want to change. I don't know much about
Terminal Server, but I infer from your response that the company would
need some additional hardware/software to host it... that's a non-
starter too. The company using the database can barely keep their two
desktops operating... to say they are not computer savvy would be a
gross understatement.

So I guess that means finding a hosting site that will provide not
only the SQLSERVER but also a VPN connection.

Hi Tom,

Arvin's got lots of experience in this area, and like most of us he
has his opinions. Mine are a bit different, so here we go.

First of all, I've written a slideshow on techniques for using Access
as a client-server front-end to SQL Server databases. It's called
"Best of Both Worlds" at www.JStreetTech.com/Downloads.

Questions:
• Will this work? If not, any alternate suggestions?

Yes, you can connect an Access FE to a SQL Server backend over the
Internet. We do it all the time.

• What kind of speed can I expect? Especially when fetching large
number of records for reports (this is a report intensive database –
lots of different ways of slicing and dicing data for hard copy
printout).

Depends on the speed of your Internet connection. 2Mb or better would
be good. If you do the data processing in SQL Server (using views and
passthrough queries as much as possible), then you can get some pretty
decent speed. You won't mistakenly think you're on your LAN, but it
will work. You have to optimize your Access app to transmit as little
data as possible over the wire. See my slide deck above.

You could also start exploring SQL Server Reporting Services, but you
wouldn't have to do that right away. SSRS rocks.

• What programming gottchas should I be aware of that are unique to
connecting to a hosted SQLServer?

Not much. You'll need a good DSN-Less SQL relinking utility. It will
use an IP address instead of server name. Otherwise, all the same
technique you need for any SQL Server back-end.

• How do I go about making local backup copies of the data in case
access to the hosted service is interrupted?

As Arvin said, that isn't trivial. You could take a periodic SQL
backup and have it handy to restore on a local server, but that's a
bit of work to set up for your users.

• How secure is this solution. There isn’t any financial data, etc
that would be a big draw to a hacker, but without the data the company
is screwed big time.

A good hosting company will have automatic daily backups and good
firewalls. You would need strong passwords for SQL Server.

• Any suggestions on a host service that is reliable and has good tech
support?

Sure, look for companies that offer Windows + SQL Server hosting. We
offer it, and many others do too. Make sure you ask about external
ODBC access to the SQL Server - cheap companies like GoDaddy don't
support it.

We (and others) also offer Virtual Private Server plans with SQL
Server, VPN, Terminal Services, etc. These are more expensive but
offer a dedicated server and much more flexibility than shared plans.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Arvin Meyer MVP]

I think you misunderstood. If you use SQL-Server without changing every
query and select statement (row source for every combo and list box) to a
stored procedure, your response times will still be unacceptable. You are
better off leaving in Access/Jet and running on a Terminal Server or using
terminal services (RDP)

I wouldn't touch that app without a backup plan if I were you. Just insist
on it. If anything goes wrong, You'll be the one blamed.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Arvin:

Thanks for the thoughts and suggestions. Regarding backups, I've done
everything I can short of buying a plane ticket and flying there to
plug the external hard disks in myself. Unfortunately, that is not in
my scope of work, and while I like the owner of the company, not so
much that I am willing to buy the ticket out of pocket!

What I take out of your response is that unless I redesign as a web
app, run across a VPN or on Terminal Server, response time will be
unacceptable. A web app is a no-go. We are all comfortable with the
MSAccess UI and don't want to change. I don't know much about
Terminal Server, but I infer from your response that the company would
need some additional hardware/software to host it... that's a non-
starter too. The company using the database can barely keep their two
desktops operating... to say they are not computer savvy would be a
gross understatement.

So I guess that means finding a hosting site that will provide not
only the SQLSERVER but also a VPN connection.

Thanks

Tom

 

[Armen Stein]

I think you misunderstood. If you use SQL-Server without changing every
query and select statement (row source for every combo and list box) to a
stored procedure, your response times will still be unacceptable. You are
better off leaving in Access/Jet and running on a Terminal Server or using
terminal services (RDP)

Arvin, I love you man, but I disagree.

We build lots of Access/SQL apps without converting every combo to
sprocs, or even passthroughs. It really depends on the complexity.
Access really does an acceptable job of parsing SQL statement through
Jet to be processed on the back-end.

But I do agree that Terminal Services is a good solution also, if you
can get the local printing working well.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

[]

• What programming gottchas should I be aware of that are
unique to connecting to a hosted SQLServer?

Not much. You'll need a good DSN-Less SQL relinking utility. It
will use an IP address instead of server name. Otherwise, all the
same technique you need for any SQL Server back-end.

This is one of the reasons I'd say that you should do this only over
a VPN connection, because it gives you more control over server name
(your local DNS can handle resolution to a changing IP address).

As Arvin said, that isn't trivial. You could take a periodic SQL
backup and have it handy to restore on a local server, but that's
a bit of work to set up for your users.

Replication would be an option. For this kind of scenario, log
shipping replication might be sufficient.

A good hosting company will have automatic daily backups and good
firewalls. You would need strong passwords for SQL Server.

This answer completely omits the most important part of the security
issue: the data going across the Internet. I don't know if there's
some way to encrypt a direct connection to SQL Server across the
Internet, but it seems to me that the proper way to do it is to have
a VPN to the hosting service. This works transparently with no need
for special configuration of your app, since the remote server looks
like it's on the local LAN. And it's as secure as you need it to be.

 

[David W. Fenton]

But I do agree that Terminal Services is a good solution also, if
you can get the local printing working well.

Isn't that a solved problem, even with plain vanilla Windows
Terminal Server? All you need to do is install drivers on the WTS
for the printers used by the client computers. In a situation where
you are hosting an app for a finite number of fixed offices, this is
pretty easy to do. It's much harder if you're trying to support home
users, but that can also be handled on the client workstation end by
installing printer drivers that are compatible with what's already
on the server. That can mean sacrificing some advanced features of
particular printers, but most of what's lost seems to me to be
pretty trivial.

 

[Armen Stein]

This answer completely omits the most important part of the security
issue: the data going across the Internet. I don't know if there's
some way to encrypt a direct connection to SQL Server across the
Internet, but it seems to me that the proper way to do it is to have
a VPN to the hosting service. This works transparently with no need
for special configuration of your app, since the remote server looks
like it's on the local LAN. And it's as secure as you need it to be.

Absolutely, the VPN is the most secure for snooping and for data
security in general. However, the OP's "security" question emphasized
protecting against loss of data, so that's what I focused on.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Paul Shapiro]

I've used Access .adp's with remote sql servers, and performance can be
excellent. The catch is that the application has to be written to avoid
unnecessary data retrieval and to minimize round-trips to the server. Access
is like any other client-server application in this regard.

If you have a 50,000 row table and open a form with a record source of
'Select * From MyTable', it will be slow. If you open the form with a record
source of 'Select * From MyTable Where 1=0', i.e., not retrieving the entire
table, it will be fast. Load data one row at a time when the user makes a
selection from a Find-a-Record combo box, or enters some criteria.

For reporting, you either live with the long retrieval times, or do the data
processing on the SQL Server so you can return a smaller data volume.

As others suggested, Terminal Server lets you keep using the existing Access
application and avoid all of this. Terminal Server is essentially Remote
Desktop, but supporting multiple simultaneous users. Only the mouse
movements and keystrokes go from your computer to the Terminal Server, and
only the screen picture comes back to you. All the processing is happening
on the Terminal Server, so your FE and BE would be on the same machine, or
at least the same LAN. Performance would be pretty much what you see today
on the LAN, without having to change anything in your application.

 

[Arvin Meyer MVP]

Isn't that a solved problem, even with plain vanilla Windows
Terminal Server? All you need to do is install drivers on the WTS
for the printers used by the client computers. In a situation where
you are hosting an app for a finite number of fixed offices, this is
pretty easy to do. It's much harder if you're trying to support home
users, but that can also be handled on the client workstation end by
installing printer drivers that are compatible with what's already
on the server. That can mean sacrificing some advanced features of
particular printers, but most of what's lost seems to me to be
pretty trivial.

One would hope it's that easy, but while doing that most of the time,
installing drivers hasn't always solved the printing problem. I called the
IT firm handling the one client I know that had a printing problem with 1 of
their locations on Terminal Server. He used a product called Print2RDP
which, while not cheap ($150 for the 1 license) it was cheaper than spending
hours trying to solve the problem.

 

[Arvin Meyer MVP]

Arvin, I love you man, but I disagree.

We build lots of Access/SQL apps without converting every combo to
sprocs, or even passthroughs. It really depends on the complexity.
Access really does an acceptable job of parsing SQL statement through
Jet to be processed on the back-end.

Nice to be loved

I've never figured out ahead of time which queries Access will parse out to
SQL-Server's engine, and which it will call for the data. If you have a
formula, or even strong educated guesses, please write a white-paper or even
better, do a presentation for the MVPs or at TechEd. What I've done is just
rewrite them all, or more often, get an asp programmer to do a web interface
for the few pages that really need a web interface. When the work is
extensive though. Terminal Services has always proved cheaper. The most
expensive job I ever did cost $6800 for a powerful rack mounted Dell
PowerEdge server with Terminal Server and 31 licenses. Plus there was a few
hours of time to set up the VPN and about $500 for the VPN box and clients.

 

[Hey]

Hi Tom,

I hesitate to jump in here because I'm certainly no expert on Access,
computers, nor anything else really. A simple person with simple ideas--I
always error on the side of simplicity.

Years ago (9 or so) I had a client who used an old foxbase program I had
written in the 80's and was still happy with it. It did all he needed it to
do. However, he decided he'd like to have his 2 outlying offices have
access to the program as well as the 3 users he had at the main office. NO
computer types in the company at all--some had difficulty with the
"Start/Shutdown" concept! Phone company reps were talking T1 lines,
computer dudes and dudettes were talking servers and program upgrades...yada
yada yada. Thousands of dollars in immediate change costs, plus thousands
of continuing dollars every year for maintenence, fast connections, etc.

I replaced his old router with a router from Linksys that had 5 VPN
connections built in. $175 at the time.

I bought computers with small hard drives and without monitor, mouse or
keyboard. $450 each, at the time.

I bought the wiring do-dads so that one keyboard/mouse and monitor can
control 2 computers.

I put each new computer under the desk of an existing user, hooked the
wiring do-dads to the new computer and the original computer and explained
the "hot key" to switch back and forth to the user. I watched them do that
a couple times--plus gave them a cheatsheet in writing describing the
process and why it might be necessary (rebooting, etc). They taped it to
the wall above each computer. I put an icon on new computers to run the
foxbase program over the network, just like the existing users.

I set up the router as per instructions for the vpn and directing the
different connections to the 2 different computers. I set up VNC to
remotely control the new computers.

I went to the outlying offices (okay, so they were all local offices for me)
and installed the little program from Linksys that came with the router to
use an icon on the desktop to automatically log onto the vpn router over a
standard dsl connection (oh, yeah...at the time, all the offices did have to
have to upgrade to a static ip address type dsl--an additional $20 per month
per dsl connection at the time). I trained the users on how to click that
icon and what would happen. Gave a cheatsheet to each user which they ...
::guffaw:: taped to the wall above the computer (complete with
passwords!!!).

I also installed same software on the 2 owner's computers at their homes so
they could have access also. They'd access it at night only so they could
still get away with just the 2 new computers to control.

It all worked, without one line of program change. Now, they didn't need to
print, so at the time, that was a good thing as I recall.

But, today, we have many additional choices. There's
logmein.com/gotomypc.com and other remote control programs where the
connections are all encrypted and secure instead of needing that vpn router.
Plus, they let you print wherever you'd like.

I use logmein.com to provide support for my clients and I've never felt a
lag in printing to my own printer....and the screen updates are all that's
passed around. Although not quite as good as being there, not a bad trade
off for the $$$ saved.

The nice thing is, nobody needs to be a techno-wizard to set it up and
support it. Not even me!

Options for you?
me

All:

I’m looking for some suggestions given the following scenario:

Current Scenario:
Typical FE/BE MSAccess database, nothing special. One main table with
several thousand records (that will stay fairly static), and one
significant child table that will grow quarterly by 1 record for each
record in the main table. Several other support tables, but they are
small in comparison. Lots of business logic embedded in the code.
Been in use for 7 years so it is reasonably well tested out, but
showing its age a little.

The database is used to manage data from multiple facilities. Due to
original design decisions, each facility has its own separate BE
file. The FE allows the user to choose which BE to link to.

The system was designed for one client company and will not be sold/
used by anyone else (in other words, I only need to satisfy his needs
and don’t really need to worry about a wider audience).

Problems:
1. The client company has grown a little now and they have more than
one person working with the data at a time…everything used to be on a
single hard drive. That won’t work anymore.
2. Some of the users are now traveling and would like to access the
data while out of the office.
3. I don’t trust their backup plan – I think its haphazard at best,
non-existent at worst.

The client company is a small business – maybe 7 employees total and
not particularly computer savvy. Keeping individual desktop computers
running is about their limit of expertise.

Solution?:
I’d like to move their data to a hosted SQL Server database accessible
by the MSAccess FE now in use (modified as needed). I THINK that will
solve all three of the problems listed above, plus allow me easier
access to the data to help them out of the periodic problems they
encounter (I am physically remote from the client company). I
specifically do not want to move to a web based application – I’m
simply not willing to give up the MSAccess environment for a browser.

I have a fair amount of experience with MSAccess, some, but not much,
experience connecting to in-house SQLServer in a corporate environment
at my day job, and NO experience connecting to SQLServer over the
internet.

Questions:
• Will this work? If not, any alternate suggestions?
• What kind of speed can I expect? Especially when fetching large
number of records for reports (this is a report intensive database –
lots of different ways of slicing and dicing data for hard copy
printout).
• What programming gottchas should I be aware of that are unique to
connecting to a hosted SQLServer?
• How do I go about making local backup copies of the data in case
access to the hosted service is interrupted?
• How secure is this solution. There isn’t any financial data, etc
that would be a big draw to a hacker, but without the data the company
is screwed big time.
• Any suggestions on a host service that is reliable and has good tech
support?

Thanks

Tom

 

[David W. Fenton]

The most
expensive job I ever did cost $6800 for a powerful rack mounted
Dell PowerEdge server with Terminal Server and 31 licenses. Plus
there was a few hours of time to set up the VPN and about $500 for
the VPN box and clients.

My first major WTS-hosted app involved a $2K Compaq rack server and
15 CALs. The WAN/VPN was already in place, so there wasn't any cost
for that. It wasn't exactly a huge organization, but it had the
infrastructure in place already because there was an administrative
office and 5 "branches." It was remarkably easy and inexpensive to
roll out the whole thing.

 

[David W. Fenton]

I use logmein.com to provide support for my clients and I've never
felt a lag in printing to my own printer....and the screen updates
are all that's passed around. Although not quite as good as being
there, not a bad trade off for the $$$ saved.

What you describe works fine for one user controlling one remote
computer. But does require 2 PCs per user as multiple users can't
remote control the same computer at once. This is where Windows
Terminal Server comes in, of course, as it allows remote control
sessions from multiple users to a single server.

 

[Arvin Meyer MVP]

My first major WTS-hosted app involved a $2K Compaq rack server and
15 CALs. The WAN/VPN was already in place, so there wasn't any cost
for that. It wasn't exactly a huge organization, but it had the
infrastructure in place already because there was an administrative
office and 5 "branches." It was remarkably easy and inexpensive to
roll out the whole thing.

This install was that expensive because I wanted a server that could grow.
The PowerEdge server had 6 SCCI hard drives, redundant power supplies and
was a dual Xeon 2.8 MHz with 4 GB of RAM. When the fans turned on, it
sounded like a WWII aircraft carrier with the planes getting ready for
take-off. We had the VPN already on the server side, but bought a little
NetScreen 10 for the client side.