Using SQL Server Migration Assistant to Move Access Tables to SQL Azure
[This article was contributed by the SQL Azure team.]
The new version (v4.2) of SQL Server Migration Assistant that came out on August 12th, 2010 allows you to move your Microsoft Access Tables and queries to SQL Azure. In this blog post I will walk through how to use the SQL Server Migration Assistant migration wizard to move the North Wind database to SQL Azure.
Download
You can download and install the SQL Server Migration Assistant for free from here, note that you will want the 2008 version of the download that aligns with SQL Azure which is built upon the SQL Server 2008 code base.
Migration Wizard
Once you have it downloaded and installed, start it up and you will be presented with a migration wizard to migrate the data.
The first page of the wizard gets you started with a migration project.
Make sure to select SQL Azure as the option for Migration To: drop down. In the next page of the wizard we are asked to select one or more Microsoft Access 2010 databases to upload to SQL Azure.
In this case I am uploading the Northwind database from My Documents folder. After clicking Next, SQL Server Migration Wizard takes a few minutes to scan the Access databases that you selected and then prompts you for the tables and queries to upload to SQL Azure.
In this example I am going to upload all the tables in the database and none of the queries. Click Next takes me to the next wizard page.
Here I need to enter in the information to connect to my SQL Azure database. The database must exist, since the SQL Server Migration Assistant doesn’t have the ability to create a new database. You can create a new database via the SQL Azure Portal that is also a good place to get your SQL Azure server name, administrator login, and password if you need them. Clicking Next takes you to the Link Tables step.
This screen gives you the option of linking your tables in SQL Azure to your Access database using ODBC links. The local tables you chose to migrate will be renamed with the suffix “_local” and the linked tables will be created with the original table names. This allows you to continue to use all of the forms, queries, reports, macros and modules in your Access application against the SQL Azure database without having to make any other application changes. With your back-end data moved to SQL Azure, you can now access your data from anywhere in the world and your data is stored in a managed, scalable environment.
One scenario I have run into in the past is that you have several people that do not share a file server want to update your Access database, which requires each of them to get the Access database in serial, perform the updates and email it to the next person. When you link your tables and store the data in SQL Azure, all of the users can update their data in parallel, meaning that you can pass out the Access file to everyone at once.
Not clicking the Link Tables check box means that the data (in my example just the tables) will be uploaded and the Access database will not be changed.
Once you click Next the SQL Server Migration Assistant will start migrating your data, it will look like this:
In my case since the database was empty, I got an addition dialog to confirm what schemas I wanted to move from Access to SQL Azure, that dialog looked like this:
If you choose to link tables, The SQL Server Migration Assistant will ask for the login and password to use when connecting to SQL Azure. The dialog looks like this:
This gives you the option of storing a different login/password inside the Access database then the one you used to migrate the schema and data to SQL Azure. For example, if you want the Microsoft Access database to have read-only access to the SQL Azure database you could enter a user with read-only permissions. For more information about creating a read-only user see this blog post. It considered best practice not to embed your SQL Azure Administrator login and password into the Access database. Access will than warn you that the login and password are stored as clear text:
One more thing to note is that the login is in the form user@server. Once the SQL Server Migration Assistant is done you are presented with a Close button, however if you have errors you should view a report for each section of the migration
In my case I wanted to see what the 6 errors where about when migrating North Wind. I never expect the database to transfer 100% clean between any two different databases technologies. Here is what my report looks like:
In this case it appears like a data type in Access is not supported in SQL Azure, I need to find out what those types are and make some adjustments to my SQL Server Migration Assistant solution to correctly convert those data types. The SQL Server Migration Assistant lets me drill down into the tables to see specific error, and gives me an estimate of the time it will take to correct the error, hopefully they are all roughly the same problem and I will not have to spend considerable time getting it to work.
Interested in proofing the data to make sure it got migrated correctly? Consider using the CTP of Project Houston, you can read more about Houston here.
Summary
That was just a quick summary of the SQL Server Migration Assistant migration wizard. The assistant is extremely robust with a reloadable solution file that lets you tune your migration, and visualize what is going on. Do you have questions, concerns, comments? Post them below and we will try to address them.