Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Aug 23, 2010

Migrating your Access/SQL Server App to the Cloud

By Danny Lesandrini

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



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM