Windows Azure Mobile Services is a turnkey backend solution for making and delivering mobile and Windows store applications, providing an easy and efficient way to store structured data objects in the cloud. The goal behind Windows Azure Mobile Services is to allow developers to very quickly be able to connect their client applications, such as Windows 8 apps, IOs, or Android, to a cloud-based backend hosted on Windows Azure. Mobile Services provides the foundation for how you can build a richer client experience with the cloud.
Users of current Microsoft Access application should miss out on the “richer client experience”, so starting with this post, and over the next 4 or 5 posts, I will provide an end-to-end and step-by-step solution for moving the database to Windows Azure SQL Database, creating the Windows Azure Mobile Services, associating the new Mobile Service to the database, setting up and configuring security, creating a few pages in Mobile Services, and querying the underlying data.
In this first post, I’ll walk through the steps required to prepare the Access database for migration, and the database I’ll be using is the well-known Northwind database. Any Access database will work, but to get the full effect of a true migration, but the Northwind database has been around for a long time and is a widely-known sample database.
Requirements, Differences, and Items of Interest
Before I get to the meat of this walkthrough, there are few items I need to point out when migrating a Microsoft Access database to Windows Azure SQL Database. First, Access allows spaces in table and column names. You’re thinking “Yes, but so does SQL Server”. Yes, that is true. Windows Azure SQL Database allows spaces in table and column names too. But Windows Azure Mobiles Services (WAMS) does not. As such, it is possible to import the Access database into a SQL Database instance, but Mobile Services will not recognize them without removing the spaces.
Also, each table must have a primary key column simply named ‘id’ (lower case). In fact when you create a new table in Mobile Services, the service will automatically add the ‘id’ column for you. However, for Access, this means some renaming of columns prior to the migration process.
Chances are that your Access database has table and/or column names with spaces. Meaning, instead of a column named FirstName, the column is named [First Name]. Plus, your primary keys more than likely are not simply named ‘id’. This is the other reason I am using the Northwind database. It is close to a “real-world” scenario.
The Northwind database has roughly 20 tables, and luckily a lot of them have their primary keys called ‘ID’. They are uppercase, but we can fix that. Since Access is a relational database, it has Primary Key / Foreign Key relationships which will need to be taken into consideration during the migration process.
Taking a quick look at the Employee table shows several things that need to be addressed:
- Spaces: Nearly every column has a space in the column name, and some have forward slashes ‘/’ in their names.
- Data Types: Access uses different than SQL Database. Most of the data types can be converted during the migration process. We’ll discuss the Attachments data type in another blog post.
- Primary Key: The ID column name needs to be renamed to lower case.
Preparing the Access database for migration
So, with that, let’s begin. Preparing the Access tables and data for migration is a multi-step process:
- Export the tables to text files
- Apply the fixes in the text files
- Import the text files into Microsoft Access
It is possible to migrate the Access tables as they currently are into Windows Azure SQL Database and then apply the fixes in SQL, but there are two main reasons why it will be easier not doing it that way. First, applying the fixes in SQL will require hundreds of sp_rename statements. Second, and more importantly, it is not as simple as calling sp_rename. If the object being renamed has a dependency on it, the rename will fail. For example, the [First Name] column in the employee table has a constraint on it. Trying to rename the column results in the following:
Msg 15563, Level 16, State , Procedure sp_rename, Line 497
Object 'dbo.[Employees].[Last Name]' cannot be renamed because the object participates in enforced dependencies
The fix would be to also script out all of the dependencies and fix those. What you will end up with is a fairly lengthy script to fix to rename the objects (to clear up the spaces) and fix the names in the dependent objects.
For this example, keeping the constraints isn’t important. I just want the data. I can always apply the constraints later when the tables and data are in the SQL Database instance.
The first thing to do is export all of the tables to text files. There are several ways to export the data. Not being an Access guy, the first thing that came to mind was to do the obvious and walk through the export wizard (right mouse click on the table and select Export -> Text File. However, Microsoft Access has Macros which help automate and group tasks, which is much easier.
NOTE: This walkthrough uses Access 2013 so if you’re using earlier versions, your menus and options might be a bit different.
The idea is to find and use a Macro that will easily export the tables to text file with a delimited format and with the column names on the first row of the text file.
Click the CREATE tab and then select the Macro button which opens the Logic Designer for creating Macros.
In the Logic Designer is a new Macro called Macro1 with a dropdown of available macro actions to add to the macro. The problem is that the only “export” option is called ExportWithFormatting. Also present is the Action Catalog window, to the right of the Macro, which likewise lists the macro actions available. Expanding the Data Import/Export node in the Action Catalog window shows the same list of available import and export actions that are available in the dropdown, including the ExportWithFormatting macro.
The export files needed for this walkthrough don’t need any special formatting, so the trick here is to ensure that the DESIGN tab is selected (it appears when working with macros) and click the Show All Actions button. New macro actions appear in the Action Catalog, which is perfect because the one needed for this walkthrough is called ImportExportText. Double-click the ImportExportText macro action and it will show up in the macro design surface and display argument boxes for the ImportExportText macro. Set the properties and arguments as follows:
- Transfer Type: Export Delimited
- Table Name: Access table name
- File Name: Path where text file will be created
- Has Field Names: Yes
Add the ImportExportText action to the macro design surface once for each of the tables.
It is important in the step to make sure that the file name entered in the File Name property does not include spaces. As shown in the above figure, the Employee Privileges file name is EmployeePrivileges.txt. Not including spaces in the file name will make the import process much easier.
Once all of the export actions have been added, click the Run button on the toolbar. This will generate a file for each table which includes the data, as well as the column names on the first row. The columns are comma separated and use the double quotes (“”) as the text qualifier.
The next step in the process is to clean up the spaces in the names. For tables, that was taken care of by specifying the file names without spaces in the export process above. For columns, the process is a bit more laborious. Open each text file, and remove all of the spaces in the column names in the first row.
Next, ensure that any primary key columns are simply called ‘id’ (lower case without the quotes). Since the primary key column in the Employee table was already called ‘ID’, the change was simply changing it to lower case.
The Invoices table, for example, is a little different; its primary key column is named ‘Invoice ID’. Same thing here; simply need to change that to ‘id’.
Importing the Text Files
The next step is to import the text files back into Access. There are several reasons for this. First, it will be easier to migrate the data into Windows Azure SQL Database from Access. Second, exporting the data to text files to clean them means that primary key and auto-number information was lost.
The quickest and easiest way to address this is to create a new, blank desktop Access database. For this walkthrough, I called it NorthwindData. This is the database the data will be imported into to fix the primary key and auto-number issues.
The process for importing the data is the same for exporting the data using the ImportExportText action in a macro. The only difference here is setting the Transfer Type property to Import Delimited (which is the default action). The import properties should be set as follows:
- Transfer Type: Import Delimited
- Table Name: New Access table name
- File Name: Path to the corresponding text file
- Has Field Names: Yes
Add the ImportExportText action to the macro design surface once for each of the tables. Once all of the import actions have been added, click the Run button on the toolbar which will import each file.
Once the text files are imported as tables into the new Access database, the next step is to quickly go through each table and add the primary key back. Open each table, select the ‘id’ column, and click the Primary Key button on the toolbar.
The AutoNumber data type cannot be set here because there is already data in the table. That’s OK because this can be addressed after the table is migrated to Windows Azure SQL Database.
Now, you’ll probably notice as you go through the tables setting the primary key that exporting to, and importing from, a text file reset some of the data types on some of the columns. For example, the Notes field in the original database is a Long Text, but in the new database it is a Short Text. The Attachments column is no longer an Attachment data type but also a Short Text.
Not to worry, because as we’ll see in the next blog post, the migration tool we will use will help us with that.
The last step is to reset the relationships. Select the DATABASE TOOLS tab then click the Relationships button. Add the tables to the designer and recreate the relationships as they exist in the original Northwind database.
The intent of this post was to focus preparing a Microsoft Access data for migration to Windows Azure SQL Database which will ultimately be used as a backend for Windows Azure Mobile Services. In the next blog post we’ll actually walk through the migration process.