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 AzureIf 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.
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.
Creating a Migration Project with SQL Azure as the DestinationStart 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.
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 SchemaTo 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 CommandAt 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.
2. Dismiss the Convert, Load, and Migrate dialog assuming everything worked.
Using SSMA to Verify the Migration ResultTo 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.
Creating Linked Tables to SQL Azure for your Access SolutionTo 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.
Check Video Migrate Your Database to SQL Azure