In this blog post, we are going to discuss how to migrate an Access database to SQL Azure using SSMA.
The first step is to download the SQL server migration assistant. I recommend SSMA for access v5.1 or more: http://www.microsoft.com/download/en/details.aspx?id=26709
Now, After Installation, open SSMA and You will see this launch wizard:
Click on Next:
2. Create a project:
Since we wish to migrate to SQL Azure, select value of “Migrate to” as “sql azure”
3. Time to add an access database:
4. After selecting the Access database, it’s time to select the objects/tables of the access database that we wish to migrate:
I am just going to migrate the whole database for this blog post:
5. Now, after selection, connect to SQL Azure:
6. Now, if you wish you can link tables.
For now, we will just migrate the database and so will not check the ‘link tables’ check box.
7. Now it will try to migrate the database. after successful migration, you will see message like this in the bottom part of the SSMA:
Data migration operation has finished.
19 table(s) successfully migrated.
0 table(s) partially migrated.
0 table(s) failed to migrate
8. This migration did not give any error. but I had played with it before and I had got an error earlier. The images below shows how to open the report and how the report looks like. What you can do in this case is that you can modify the required configuration and then try migrating.
Click on report besides the message “converted selected objects” to open the report.
This is how a sample report looks like:
So yes! That’s it. This is how one can migrate Access Database to SQL Azure.
And one consideration: I have tried migrating Access 2010 database, please check the version of the SSMA and the version of access database it supports before migration process.
