This task isn't like the recent all-nighter you pulled to prepare and file your Federal Income Taxes. Maybe the stress level is just as high, but for most of us, gathering receipts and other related documents, filling out the forms, and writing that check is a known quantity. This one isn't, and that's why I want to offer some observations in my personal experiences, which I hope will prove to be relevant and helpful to you too.
Practice Makes PerfectGetting your data safely out of your Office 365 AWAs and into more stable long-term storage is a once-in-a-lifetime event--at least that's our hope. That means you probably don't have a lot of experience doing it so far. I sure hadn't done it that much until recently.
So, while you still have plenty of time to practice, it's a good idea to take a run at one or more of the options available to do that.
As I outlined recently, you have three main options to rescue your data:
- Convert the SQL Azure tables to local Access tables in an accdb.
- Convert the SQL Azure tables to SharePoint lists in your current O365 site.
- Extract the SQL Azure tables as a dacpac and deploy them via that dacpac to a new SQL Azure, or SQL Server database.
It's All About the DataKeep in mind that we're NOT talking about the interface yet. We're focusing only on rescuing your data.
Follow a Strategy, Not a Reaction
I've found there are advantages and disadvantages to each of the three main strategies listed above. Here's a review of the pros and cons I've observed personally for the first option. I hope this can help you decide which way to go with your own AWAs. Let's start with what I think is probably the simplest one for most Access users. In future blog posts I'll tell you more about my experience with the Export to SharePoint Lists option, and revisit the dacpac option.
Convert Linked SQL Azure Tables to Local ACCDB Tables
This is, in my opinion, the easiest and quickest way to get your data into a new home, albeit not the one I'd recommend for most of us. First, let's review the method; then we can review some of the Pros and Cons I've observed.
Here are the basic steps. You start with the template web app file for your Access Web App.
|Figure 2. Create a Reporting, Read-Only ACCDB|
- Navigate to an appropriate folder, select an appropriate name for your accdb, and save the read-only accdb.
|Figure 3. Save your Reporting database with ODBC Links to all of the tables in your AWA|
|Figure 4. Accdb with Read-Only links to your AWAs tables|
|Figure 5. Connection String showing the "ExternalReader" UserID|
- The intent behind this automated process was to make it easier to develop reports for your AWA, using the powerful Access reporting engine. For that purpose, Read-Only tables are the best choice. For this data rescue task, it really doesn't matter very much whether the linked tables are Read-Only or Read-Write. And, since creating this accdb is a built-in function, let's run with it. There's another option--creating Read-Write links, but we're not going to spend a lot of time on them because, frankly, it doesn't matter all that much at the moment.
- As you can see I Figures 5 and 6, the SQL Azure database behind your AWA contains more tables than you will probably need. The ones we don't want are identified by the inclusion of the question mark, ?, in their names.
|Figure 6. Filtering Out Unneeded Tables for Deletion|
|Figure 8. Select and convert ODBC linked tables to local Access tables|
All of the data in the tables you selected for conversion from SQL Azure to local Access should be intact, ready for your next project.
The Dark Side of the Cloud
There is a dark lining to this cloud, though. So far, I've not been able to retain any relationships between tables that are clearly related in the SQL Azure database, with proper Foreign Key restraints.
|Figure 10. Relationship information didn't survive the trip from SQL Azure to Access.|
|Figure 11. Indexes migrate from SQL Azure to Access|
Summary and ConclusionObviously, this strategy has pros and cons.
It's quick and relatively easy to do.
- Creating the read-only accdb is a built-in function.
- Filtering tables for deletion or conversion is manual to some extent, but you can make the conversion in bulk after selecting a group of tables to convert.
- There's nothing terribly complex or unfamiliar about converting linked tables to local tables in the accdb.
It retains some important meta-data.
- This method does retain indexes on the converted tables.
It loses some important meta-data.
- So far in my testing, at least, conversion to local tables isn't able to preserve Foreign Key restraints.
There is one more method to cover, exporting your tables to SharePoint lists. We'll visit that one next.