Since my previous blog posts about Access Web Apps, I’ve had multiple questions on how to back them up.
If you’re on premise, you could deploy a backup plan that allows you to roll back your data (and structure) to previous versions, but that won’t cut it as your forms and rules won’t be backed up like that. So in any way, all scenarios considered here are both applicable to on premise and Online deployments of Access Web Apps.
The possibilities are … finite
The following table gives you an overview of the possibilities. There may be more out there, but these are my first thoughts.
Let’s first detail what the scenarios mean.
- Manual backup every day: find a minion in the organization that is willing to make a manual back up every day by opening the Access Web App in design mode, and saving it as a Snapshot.
- Automatic backup: automate your minion! The Access desktop application won’t let you script much, so it’ll be basic programmatic point and click to backup the file.
- Only back up SQL: takes a SQL data snapshot from the underlying SQL database (Azure or on prem)
- Hybrid: combines scenario 1 and 3. Take backups of the data itself (SQL data) every day, automatically, and ask users to make a Deployment snapshot every time they change something in the structure.
Next, these criteria are used to score the scenarios:
- Robustness: indicates the chance of something going wrong with the solution
- Automatic: indicates how many manual activities need to be performed, i.e. indicates the backup time
- Restore: indicates the restore time
- Scope: what data gets backed up?
- Saved to: where does the data go? (mostly for cloud-oriented customers, there are other possibilities)
- Cost: what will this cost me in terms of development investments (not counting staff effort)
Let’s start our discussion with the first scenario, “Manual backup every day”.
Access Web Apps can be saved as “Snapshots” from within the Access client. This saves both structure, data and views/macros. This is the ideal scope. It can be restored quickly and can even be restored on a different location. It is however not very robust because it is prone to user mistakes and is totally non-automated. This is the easiest scenario though.
Next, let’s see what automation can bring to this scenario. Say that we provide a VM that hosts the Access Desktop client and automatically performs virtual clicks to emulate the end user’s behavior from the previous scenario. The solution has all the benefits of the previous scenario, but has high risk of failure due to the simulated clicks. It will cost several days to create the automation script as well. This scenario probably won’t appeal to many.
Note that the Access Desktop client does not seem to feature automation or scripting functions to perform this snapshotting automatically, which led me to desperate measures 🙂
Next, we have a simple scenario that only backs up data. If the structure, views and macros never change, this might be your shot. The data from the Access Web Apps is saved to either an on premise SQL database or a SQL Azure database, hosted by Microsoft, in which case you only get read-only or read-write permissions on that individual database. So no ALTER statements, only CRUD operations. For backing up pure data, that’s ok. Fire up your favorite SQL back-up tool or ETL that data into your data center. Restore will take more time as you need to dump all data and recreate it, based on the constraints present in the data model. This scenario will cost you some effort and does not contain the entire Web App as it lacks views, structure and macro backup. So, it’s great if only the data changes over time.
Finally, we go for hybrid. If structure, views and macros don’t change every day, but data does and we want to automate things like in scenario 3, but still keep a record of changes in the views and structure. Basically, in this scenario, you take daily data-only backups. That will ensure that you get all data backed up every day (or even multiple times a day). The views and structure are only backed up whenever you change something in the definition of the Access Web App. This is the non-automated part, but doesn’t happen often and requires manual intervention anyway. So some good governance plan and procedures for changing the structure are mandatory here. This scenario is not so robust because any out-of-sync of data and structure backups will render both backup sets (data and structure) useless.
As a good consultant, I must say that it strongly depends on the situation what the best solution is.
If your users like clicking repeatedly, go for scenario 1 where they take snapshots themselves.
If you got money to spare and your developers like a challenge, but can’t bother your users, go for scenario 2.
If the structure rarely changes, go for scenario 3.
If you don’t like either of the previous scenarios, scenario 4 may be your only viable solution.
Hope this helps anyone.