Migrating Access Jet Databases to SQL Azure
In this blog, I’ll describe how to use SSMA for Access to convert your Jet database for your Microsoft Access solution to SQL Azure. This blog builds on Access to SQL Server Migration: How to Use SSMA using the Access Northwind 2007 template. The blog also assumes that you have a SQL Azure account setup and that you have configured firewall access for your system as described in the blog post Migrating from MySQL to SQL Azure Using SSMA.
Creating a Schema on SQL Azure
If you are using a trial version of SQL Azure, you’ll want to get the most out of your free 1 GB Web Edition database. By using a SQL Server schema, you can accommodate multiple Jet database or MySQL migrations into a single database and limit access to users for each schema via the SQL Server permissions hierarchy.
SSMA for Microsoft Access version 4.2 doesn’t support the creation of a database schema within the tool, so you will need to create the schema using the Windows Azure Portal. Launch the Windows Azure Portal with your Live ID and follow the steps as shown below.
Click on the Database node in the left hand navigation pane.
Expand out the subscription name for your Azure account until you see your databases
Select the target database that you created when you first connected to the Azure portal – see Migrating from MySQL to SQL Azure Using SSMA for how the SSMADB was created for this blog.
Click on the Manage command to launch the Database Manager. You will log in into SQL Azure database as shown below.
Once in the Database Manager, you will need to press the New Query command as shown below so that you can create the target schema for the Northwind2007 database.
Now that you have the new query window, you can do the following steps as illustrated below.
- Type in the Transact-SQL command to create your target schema: create schema Northwind2007
- Press the Execute command in the toolbar to run the statement.
- Click on the Message window command to show that the command was completed successfully.
You are now ready to use SSMA for Access to migrate your database to SQL Azure into the Northwind2007 schema.
Creating a Migration Project with SQL Azure as the Destination
Start SSMA for Access as usual, but close the Migration Wizard that starts by default. The Migration Wizard will end up creating the tables in the dbo schema instead of the Northwind2007 schema that you created. Follow the steps shown below to create your manual migration project.
- Click on the New Project command.
- Enter in the name of your project.
- Select SQL Azure for the Migration To option and click OK. If you forget to select SQL Azure, you’ll need to create a new project again because you can’t change the option once you have competed the dialog.
The next step is to add the Northwind2007 database file to the project and connect to your SQL Azure database as shown below.
- Click on the Add Databases command and select the Northwind2007 database.
- Expand the Access-metadata node in the Access Metadata Explorer to show the Queries and Tables nodes and select the Tables checkbox.
- Click on the Connect to SQL Azure command
- Complete the connection dialog to your SQL Azure database
Choosing the Target Schema
To change the target schema, you need to Modify the default value from master.dbo to database name and schema that you created for your SQL Azure database – in this example – SSMADB.Northwind2007 following the steps below.
- Click on the Modify button in the Schema tab.
- Click on the […] brose button in the Choose Target Schema dialog.
- Choose the target schema – Northwind2007 - and then click the Select and the OK button.
Migrate the Tables and Data with the Convert, Load, and Migrate Command
At this point, you are ready to proceed with the standard migration steps for SSMA which includes (ignoring errors):
Click on the Tables folder for the Northwind2007 database in the Access Metadata Explorer to enable the migration toolbar commands.
Click on the Convert, Load, and Migrate command to do all the steps to compete the migration with the one command.
Click OK for the Synchronize with the Database dialog as shown below to create the tables in the Northwind2007 schema within the SSMADB database.
Dismiss the Convert, Load, and Migrate dialog assuming everything worked.
Using SSMA to Verify the Migration Result
To verify the results, you can use the Access and SQL Azure Metadata Explorers to compare data after the transfer as follows.
- Click on the source table Employees in the Access Metadata Explorer
- Select the Data tab in the Access workspace to see the data
- Click on the target table Employees in the SQL Azure Metadata Explorer
- Select the Table tab in the SQL Azure workspace to see the schema or the Data tab to view the data.
You can also use the SQL Azure Database Manager to view the table schema and data as described at the end of the blog post Migrating from MySQL to SQL Azure Using SSMA.
Creating Linked Tables to SQL Azure for your Access Solution
To make your Access solution use the SQL Azure tables, you need to create Linked tables to the SQL Azure database. To create the Linked tables, you need to select the Tables folder in the Access Metadata Explorer as shown below.
Right click on the Tables folder and select the Linked Tables command. SSMA will create a backup of the tables in your Access solution file and then create the Linked Table that connects to the table in SQL Azure.
Summary
As you can see, migrating your Access solution that uses Jet tables as easy as:
- Creating a target schema in your target SQL Azure database.
- Creating a project with the Migrate To option set to SQL Azure.
- Following the normal steps for migrating schema and data within SSMA.
- Verifying the reports within SSMA or through the SQL Azure Database Manager.
- Creating Linked Tables to the SQL Azure database within your Access solution using SSMA.
Additional SQL Azure Resources
To learn more about SQL Azure, see the following resources.