Home > SQL Azure Migration > Migrating Access Databases to SQL Azure

Migrating Access Databases to SQL Azure


Step 1: Download and Install “Microsoft SQL Server Migration Assistant 2008 for Access v 4.2” from

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5abe098d-c7e1-46c6-994a-09a2856eef0b&displaylang=en

Step 2: Register SSMA 2008 for Access and download License and install the License

Step 3: Run SSMA 2008 for Access v 4.2

image

Click Next to create a new Migration Project

image

Specify Project Name and Select Project File Location and select Migration To: “SQL Azure

image

Click “Add Databases” to Select the Access Database(s) you want to migrate

If you are not sure the location of your database , click “Find Databases” to find it

image

You can search using the following criteria

1. Specify a folder such as C:\Databases

2. Specifying a part of the file name

3. Created or Updated Date

4. Size of the database

5. Owner of the database

image

After adding you databases, Click “Next” to proceed

image

Select the Tables and Queries you want to Migrate

image

Specify “SQL Azure” Server Name, username and password and the click “Browse”

image

Click “Yes” to Continue

image

You can select any existing SQL Azure database, or Create New SQL Azure Database

image

Specify Database Name, You can select Database Size as 1 GB or 10 GB and then click “Create”

image

Click “Ok” to proceed

image

If you still want to continue using Access as Front End for your SQL Azure database, check “Link Tables” option to create Linked Tables in your Access database and click “Next” to proceed

image

Once the scripts are prepared and ready, you will be prompted to confirm the SQL Azure information again

image

Enter the Password and click Connect

image

Review the List of tables and its associated Indexes and Triggers ready to be migrated and then click “Ok” to proceed

image

The above Legends explains the directions of data migration

image

You can click on legends “Blue Arrow” to change the direction, in the above screenshot, we have selected “Order Details” not to be migrated

Click “Ok” should start the migration process

After migration, you should be able see the tables migrated in SQL Azure server

image

For all the tables, a TimeStamp field will be added as last column for enabling continuous Data Migration

image

Using SQL Azure Metadata Explorer, You can generate Reports to identify the issues in migration or Migrate data from one or multiple tables

image

Sample Migration Report

image

image

Table level report

image

Now you can access your data in SQL Azure using SQL Server Management Studio or SQL Azure Management Portal or Any Applications supporting SQL Azure connections

image

Happy Migration !!!

Advertisements
Categories: SQL Azure Migration
  1. z
    July 1, 2011 at 12:20 pm

    How do you find out what the Server Name is?

    Thank you

    Like

  2. July 1, 2011 at 12:30 pm

    If you are referring to SQL Azure Server name, you have to login to SQL Azure Management Portal and check under your subscriptions

    Hope this helps !!!

    Like

  3. z
    July 1, 2011 at 12:51 pm

    Okay, what about SQL server 2008 or Denali?

    Thanks.

    Like

    • July 1, 2011 at 1:08 pm

      For SQL Server 2008 or Denali, specify your Server Name where you installed SQL Server, For ex. SERVERA, if you have installed a specific instance, then use SERVERA\INSTANCENAME

      Like

  4. z
    July 1, 2011 at 2:52 pm

    Thanks!!! I did this for VS 2010 a few years back.

    Like

    • July 1, 2011 at 3:24 pm

      If it was installed with VS2010, the instance name should be COMPUTERNAME\SQLEXPRESS

      Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: