What does “moving Access application to the cloud” mean?

It means you are moving your data to a hosted SQL Server database on the web. You no longer have the data locally and you are still running the Access frontend locally. You may or may not have some tables local, but for the most part, all of the data is in a SQL Server instance running somewhere else and is accessible via the web.

Why not just use Terminal Services?

To be fair, terminal services may be faster and provide a much smoother experience for your users. You can purchase a remote desktop app for your tablet or phone and even remote in, something you can’t do with a hosted backend. Sometimes it’s not possible to go this route: your client may not have the resources to do it or it’s too expensive because terminal services has licensing requirements and typically price by numbers of seats. Bottom line: if you can host your application on a terminal server do it, otherwise continue reading.

Step 1: Install SQL Server Express locally

You’d be surprised how many developers start by migrating from Access to the cloud as their first step, when they should instead migrate to SQL Server locally first. Migrating to SQL is a challenging proposition for a first timer, you’re going to need to change your database design to include fields not present in Access, (such as the RowVersion field), or creat additional views for each report. There is a TON of things you need to do BEFORE you migrate to the web, and if you choose to upload your database first to the cloud, you are going to be hampered  with a slower connection doing things you could have been doing faster locally.

Step 1a: Which version to install?

Generally speaking, it’s easier to migrate a SQL Server database from older version to later than the other way. Typically, you can install SQL Server Express 2008 R2 with tools unless you are certain your host on the web is using SQL Server 2012. If you don’t know where are you going to host your database, than go the safe route and use 2008, since it’s very easy to migrate to 2012 but almost impossible to downgrade to 2008.

Step 2: Optimize your database for local use

Your SQL migration may SLOW your Access database instead of making it faster. Rule of thumb: If the database is slow on your local SQL Server it’s just going to be slower on the web by many factors more. You MUST fix performance issues on your local machine before uploading it to the web.  That’s why I came up with this blog, to help others migrate without stumbling along the way. If you follow my tips at AccessExperts.com/blog/starthere you will be well on your way to optimal performance. If you’re not happy with the local performance, don’t upgrade to the web until it’s been optimized.

Step 3: Use SQL Server Security with a user table

You can’t use Microsoft Access security in the cloud, so you need to use SQL Server Security along with a user table. Why? Because many hosting companies will only provide a limited amount of SQL Server users for your app, typically 1 to 5 users max. That will not work with a user list of 6 or more, so you’re stuck with the same approach a website designer would use: Create a user table with usernames and passwords and authenticate users when they login against the table. (Yet another advantage Terminal Server has against the cloud, in terminal server you can use Active Directory security).

Under this security schema you will only have one SQL Server login that has rights to all tables. Use it to link tables to your frontend and while doing connections with ADODB from code. The later will require storing the credentials in your code, which also has it’s drawbacks: Anyone can open your access database with a text editor and find your password. We avoid the issue by scrambling the username and password in the code.

Step 3: Upload your database

You’re finally ready to upload your database to the host, mostly done by backing up your local database and then sending the file to your hoster for them to restore. They may or may not allow you to keep your current database name, so be ready to change your code as needed once it’s uploaded. To upload your database we recommend using SQL Server Migration Assistant For Access 6.0

Step 4: Optimize again, and again, and again

Now that you’re database is in the cloud you may or may not have performance issues: Forms may take too long, reports timeout and in general your application may be useless, leading you to question why you did this in the first place. Since there is no way to tell how your application will perform on the web until you actually upload, you will need to again do another round of performance tuning to get your app up to an acceptable speed, but how much will it require of you? Unfortunately, determining acceptable performance is very subjective. You may decide it’s ok but once your client gets their paws on it they may disagree. In general, I tend to consider performance acceptable when it’s 15 seconds or less, 2-3 seconds being standard and one second being optimal. It’s ok if reports take longer, but anything over 30 seconds will start gnawing on people.

Optimization as a function of declining return against your time

The more time you put into optimization the less and less return you get for it. At first you will achieve great results for your efforts by adding additional indexes, creating joins, offloading work to the server, creating stored procedures, etc. But those efforts will bring back less and less of a return on performance vs your time invested, leaving you with the choice of how much more you will put into it until the performance is acceptable. It’s for this reason we make sure our clients understand how difficult it can be to get it right, you just don’t know how much optimization it will require until they are satisfied.

It CAN be done, we do it all the time

I don’t want to mislead you, it’s doable but not easy. Give it your best shot, learn how to “cloud enable” your Access application, and you will be rewarded with a new skill set like any other.