Migrating Access Data to SQL 2008r2


I recently spent some time building an Access database file full of 2010 US Census data… As an Access database, it’s not very useful since I want to use the data within as a feed for my Windows 8 Metro application.  I’ll need to move this data into my SQL 2008r2 server to make it available to my application.

To do this I’ll be using the Microsoft SQL Server Migration Assistant (SSMA) toolkit I recently installed.

 

image

Upon first use, I’ll need to acquire a license for the tool as instructed by the included Readme file.

SNAGHTML26d9097

image

image

After filling out the form, they provide me a file which I will through onto a network file share and give to the tool…

image

SNAGHTML27292d9

ok, in the above pic, I didn’t need to specify the filename… just the directory it sat in… then the license imported correctly.

SNAGHTML27426ed

SNAGHTML2766802

SNAGHTML276c8e6

SNAGHTML27702f9

SNAGHTML2774749

SNAGHTML2776831

After specifying the database, I had to give it a minute or so to completely read in and load my database file… the 2010 Census file is right at 100mbs.

SNAGHTML2785904

Then specify my SQL server, destination Database name, and my authentication method… I’m in a domain so I’ll simply use Windows Authentication since my Domain account has the appropriate level of access to the SQL Server.

SNAGHTML2795a3c

Yes Please…

SNAGHTML27a5a9a

SNAGHTML27a9633

I do not have any pre-existing applications which connected to this Access file… thus no need to link anything.

SNAGHTML27afacf

Let the Migration begin!

SNAGHTML27afacf[4]

At this point I could see the connection and the data migration was occurring…

image

SNAGHTML27d947e

back on the server, my tables are now created…

image

On the Migration Wizard, the data is slowly being copied over…

SNAGHTML27dd516

This is where my SQL server is running a tad slow… a lot of data being input into the database…

SNAGHTML27f5b55

SNAGHTML28012e1

Wizard complete, and I can see the newly arrived data on the server.

image

According to the Migration report… there were Warnings… but no Errors.  The Warnings mostly related to database quality… IE:  some tables did not have primary keys, others had their primary key nullable value configured.

SNAGHTML2823fe9

Those tables with nullable primary keys were corrected to not allow null values.

image

Those without primary keys were ignored since SQL server doesn’t consider that a serious problem…

image

That and I’m not worried about those two tables considering they were used during the “Import” process from the Census .dp files and won’t be used again.

It’s time to integrate the new found data into my Windows 8 application!

If you have any questions or comments on this, please leave them below!

Advertisements

5 thoughts on “Migrating Access Data to SQL 2008r2

  1. Hi Daniel, liked your post, very well explained with pictures and all. I’m interested if there is an option not to migrate data, because i just need empty sql database for future use. Thanks!

  2. Yes, I want to create table structures,and not migrate the data inside the tables. This option available in upsizing wizard in access 2010.

  3. Great tutorial. Thanks, this saved me a lot of work. One question – You have a Demo & Pop tables for each State. Any reason why these can’t be just two tables for the whole of USA?

  4. Indeed they could be combined. In this article I was simply migrating the database they provided very basic instructions on building… the result of one of my other articles.
    There are several things about their table structures that I find unusual.

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