[dba-VB] Access data across the internet

John,

A web service doesn't necessarily need a web browser interface. Any
application can use web services for as long as that application can connect
to the internet.

You are correct that you would have to learn a whole new paradigm but only
in so far as how you reference the web service in your application and of
course creating the web service itself. VS.NET already has template code to
get you started in the right direction. I can also help you get started. As
far as multi-user issues is concerned it's a non-issue because it's handled
behind the scenes by SQL server.

As far as SQL server is concerned, it's just another connection
requesting/updating some data.

I've created web services that some of the managers at work use with Excel
to load up a worksheet with data from a vendor-supplied MRP system. They
just click a button and VBA calls the event that handles the data pull via
the web service. I've also created web services used in a browser-based
application where the user can upload an image file (JPG) to a remote SQL
server where it is saved as a BLOB (binary object) and then displays the
uploaded image on the browser after it is successfully uploaded (again
invoking one of the exposed methods of the web service that specifically
handles that functionality).

The thing with VPN connectivity, FTP, firewalls and the like is that you
have a level of complexity that you have to deal with on the client side to
get it to work correctly and if something goes wrong you have several places
that you have to look into to determine what could have caused the problem.

With the web services approach you can narrow down the troubleshooting.

Here's a sample reference for a web service on my site --
http://www.ebarro.com/webservices/EBarroAPI.asmx

Eric

-----Original Message-----
From: jwcolby [mailto:jwcolby at colbyconsulting.com] 
Sent: Friday, August 28, 2009 8:43 AM
To: ebarro at verizon.net; Discussion concerning Visual Basic and related
programming issues.
Subject: Re: [dba-VB] Access data across the internet

Eric,

Thanks for the response.  I assume you are discussing a "browser based
application"?

If so there are a couple of issues that I see.
1) I would have to learn a whole new paradigm from scratch.
2) Multi-user issues.

The client is a SOHO with "employees" consisting mostly of family members,
who would work from their homes.  I understand the "setup" issues but at
least for now that would be a very small number of machines.  I just talked
to the client and, for example, he has two production scanners, one in CT
and the other in Nashville.  It would be nice for each scanner to drop the
scanned files (CSV files) into a common directory structure in the office in
CT.  The program would then pull the CSVs across the internet into the local
instance wherever that might be, import it into a local data store, process
the data, then export the tables back out into a fixed width file back in
the same server (different path) that the CSV came from.

You get the picture.  The thing that might make this work is that the files
are tiny, 80 kb is the biggest I have seen so far.  I am actually looking at
installing Hamachi on each of his "satellite offices" PCs, and building a
virtual network around that Hamachi client.

I don't contend that this solution works for a huge system but not every
system is huge!  And if it gets huge then the money will be there to support
hardware VPNs etc.  Or even a port to a web service at that point.

John W. Colby
www.ColbyConsulting.com


Eric Barro wrote:
> John,
> 
> This is a good candidate for a web service. A web service allows you 
> to expose certain methods to client machines outside the network.
> 
> 1. Basically in terms of servers you will need a web server and a 
> database server (one physical server is possible but two are better 
> due to security issues).
> 2. The web server exposes the web service application and takes care 
> of authentication to the database server. The web server is the only 
> one that is publicly accessible from the outside.
> 
> This saves you from having to set up VPN client software on the client 
> machines and also saves you from having to purchase hardware to run 
> the VPN on the server side.
> 
> Eric