Migrating your Access/SQL Server App to the Cloud


Have you ever wanted to share your Microsoft Access application to users outside of your LAN? Typically, this means either rewriting it as a Web application or implementing replication but the Cloud changes all that. The following article will show you simple steps to moving your Microsoft Access database to the Cloud.

I was introduced to Classic ASP programming back in 1999 and I immediately
saw it as a simple way to put my Access database on the web. I wrote a couple
of articles that described how to simulate an Access datasheet with full
update, insert and delete functionality. In 2006 I created a generic Access
form with the code to generate the stub (now called scaffolding) code that will
convert any Access table into a Classic ASP page. These articles are still
available on the Database Journal website.

Simulate Access Datasheet in ASP — Part I
Simulate
Access Datasheet in ASP — Part II

How to Display Any Access Table on the Web

However, I no longer recommend that route for the following reasons:

  1. Maintaining data integrity and consistency in Classic ASP
    for multiple users is nearly impossible.
  2. Your rich Access programs probably need more than simple,
    simulated datasheets.
  3. There’s an alternative … migrate to the Cloud.

Before we go any further, there is a caveat you need to be apprised of: this
solution only works for data stored in SQL Server. The client piece can
continue as an Access version n.0 application but the data must be up-sized to
some flavor of SQL Server. Thankfully, this is relatively simple and painless
albeit beyond the scope of this particular article.

What is The Cloud

I’m probably not the best authority to answer this question but I have been
researching it for about 4 months now and I think I have a few things figured
out. It’s worth mentioning that I’m not the only person confused about this
topic. There are at least three similar offerings that come to mind when one
thinks of "the Cloud" and though they may deliver a similar
experience, they each have their own peculiarities.

  • Your data hosted on the Internet
    (a.k.a. Internet enabled application)
  • SaaS is Software as a Service
    (formerly ASP or Application Service Provider)
  • Cloud Computing
    (through Google, Amazon, Microsoft Azure, etc.)

The Access solution I’m about to describe fits the first category, simple
SQL Server hosting through an ISP like discount ASP.net. SaaS more closely resembles Google Documents
where, in addition to hosting the user’s files, the software that manages the
file is itself hosted by the same provider and the user runs nothing more than
a browser.

I’m sure I’ll be chastised for oversimplifying things but that describes it
in a nutshell. The step to Cloud Computing adds the dimension of distributed
computing. I don’t know the specifics of Google or Amazon but from what I’ve
heard about Microsoft Azure, you get a lot more for your hosting dollar than
you will from a standard ISP. Applications hosted on Azure can be scaled up and
down at will to meet demand. Fail-over protection is automatic. In the event of
a hardware problem, the application and data files kick over to a mirror. If
the mirror fails, it switches to another node. You don’t get this kind of
up-time guarantee from a hosted ISP solution.

That having been said, what I’m about to describe as my hosted ISP solution
could just as easily be implemented on SQL Azure, assuring virtually 100%
availability and probably not for much more money than what I’m paying for my
hosted solution. I say "probably" because the fee schedule for SQL Azure
confuses me. I’ve seen pricing that suggests it costs $9.99 for a 1GB database
but there is also a $.15 per GB fee for outbound bandwidth, which would also
apply to maintenance transfers of backup files as well. If Windows Azure
pricing also applies, you will pay an additional $.12 per hour. I was afraid I
just couldn’t be sure how much this would add to the monthly fee, so I opted
for another service.

Take all that Azure pricing information with a grain of salt because as I
said, it confuses me immensely. Perhaps that’s why, when I decided to move my
SQL Server to "the Cloud", I sought out a hosted ISP solution.
Through discount ASP.net I’m able to host my MVC2/Framework 4.0
web applications
for $10 per month and for another $10 per month I get 500
MB of SQL Server database space in a single database. How the bandwidth limits
split between web and database access is a little unclear to me but it appears
I get about 82GB per month.

So, for $20 per month, I was able to decommission an old server running in
my basement and put my web and SQL Server in a hosted environment. But how does
one access their remote treasure?

Connecting To Your Hosted SQL Server

Once you have a hosted SQL Server you’ll need to upload your database. You
can do this in a number of ways. You could up-size your Access data directly to
the web database using an ODBC connection to it and I’ll show how to create the
DSN later. You could connect your SQL Server Management Services application to
the database and run table scripts to create and migrate the database, but this
would be arduous for more than a hand-full of objects. If you are comfortable
with the Export Data Wizard, you can use SSIS (SQL Server Integration Services)
to move the tables and then run scripts for the views, procs and functions.
Alternatively, discount ASP.net provides a way to restore a backup file online.
I haven’t had occasion to do this yet, but it seems straightforward enough.

Via SSMS

To connect SQL Server Management Studio, you’ll want to register the server.
Below is a screen shot of the properties window for my registered, remote SQL
Server. Once registered, you can treat it just as if it were on your network.
On my business network, the bandwidth is less than desirable so it slogs along
very slowly but when connected to broadband it’s as if the server were
in-house.

For my requirements, I know I won’t soon be passing the 500 MB size so to
avoid paying an additional $10 per month for each database I create, I’m
implementing Schemas in SQL Server 2008. You can see schemas in action by
opening the Adventure Works 2008 database. It’s a way of partitioning tables
into groups identified by their schema. So you might have the following table
designations:

dbo.aspnet_Users
dbo.aspnet_Roles
HumanResources.tblEmployees
HumanResources.tblLeaves
Sales.tblProducts
Sales.tblOrders

Via ODBC

To get to your hosted SQL Server from an Access MDB file, you’ll want to create
an ODBC connection. (For Access ADPs you should be able to connect to it
directly via the Connection properties.) Below are three screen shots of the
steps I used to connect to my discount ASP.net SQL Server. Once the DSN is
created, you need only link to it as you would to any SQL Server on your
network.

If you want to use a DSN-less connection, you can modify your table linking
code to point to the new server with code something like this…

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strCnn As String
 
strCnn = "ODBC;DRIVER={SQL Server};" & _
"SERVER=sql2k802.discountasp.net;" & _
 "DATABASE=SQL2008_datafast;" & _
 "UID=SQL2008_user;" & _
"PWD=xxxxxxxxx"

Set dbs = CurrentDb
 
Set tdf = dbs.TableDefs("Nerd_Dinners")
tdf.Connect = strCnn
tdf.RefreshLink
 
Set tdf = dbs.TableDefs("Nerd_RSVP")
tdf.Connect = strCnn
tdf.RefreshLink

If you hover your mouse over the linked table in Access, you’ll see the
connect string, with its reference to the remote server, as shown in the screen
shot below. From this point on, users simply run forms, reports and queries as
they usually would. However, since the database is hosted at a remote location,
in the "cloud" as it were, users need not be on a single LAN. Users
from different locations, cities or even countries for that matter may now
access the same live data simultaneously.

Is This the Holy Grail You’ve Been Seeking?

I can’t answer that question for you. What I can tell you is that for the
relatively low cost of $240 you can contract with an ISP and set up a SQL
Server to test it out. (If they allow you to get only the SQL Server space it
could cost only half that much.) Performance may be limited by the bandwidth
coming into your business or the sheer mass of data you need to pass might make
it impractical. Not to mention that performance will be affected by how well
the developer has optimized data access in the queries and tables used in the
application. It’s impossible for me to predict how well this will work for you.

Still, this is an option. It’s not quite "The Cloud" in a proper
sense but you can see "The Cloud" from here and it sure beats
replication or classic ASP datasheets.

Additional Resources

Internet.com Cloud Computing Showcase

»


See All Articles by Columnist

Danny Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles