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.
Upon first use, I’ll need to acquire a license for the tool as instructed by the included Readme file.
After filling out the form, they provide me a file which I will through onto a network file share and give to the tool…
ok, in the above pic, I didn’t need to specify the filename… just the directory it sat in… then the license imported correctly.
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.
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.
Yes Please…
I do not have any pre-existing applications which connected to this Access file… thus no need to link anything.
Let the Migration begin!
![SNAGHTML27afacf[4]](https://danielheth.files.wordpress.com/2011/12/snaghtml27afacf4.png "SNAGHTML27afacf[4]")
At this point I could see the connection and the data migration was occurring…
back on the server, my tables are now created…
On the Migration Wizard, the data is slowly being copied over…
This is where my SQL server is running a tad slow… a lot of data being input into the database…
Wizard complete, and I can see the newly arrived data on the server.
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.
Those tables with nullable primary keys were corrected to not allow null values.
Those without primary keys were ignored since SQL server doesn’t consider that a serious problem…
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!
danielheth 
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!
Are you wanting to retain the table structures?
Yes, I want to create table structures,and not migrate the data inside the tables. This option available in upsizing wizard in access 2010.
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?
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.