question

sharon avatar image
sharon asked

Migrating Access to SQL

Can someone help me figure out the best tool to use when migrating huge access mdb file to sql server? what is the advantage of using Upsizing wizard verse SQL server migration assistant? what are the most important things I should consider in planning the migration? I would appreciate your comments from experience. Thanks!

sql-server-2005upgrading-access-to-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered

I'm not familiar with the SQL Server Migration Assistant, but have some experience with the Upsizing Wizard.

The upsizing process is fairly easy and gives very good results. One gotcha I noticed on the data side is that all of the Access text fields were "upsized" to SQL Server text columns. I spent an extra day going through the resulting SQL Server database and setting up appropriately sized varchar() specifications on most of these columns so I could use string functions and operators, as well as apply indexes when needed.

Something else to consider are any non-table structures you may have in your access database.

If you have any forms, queries, macros or reports, you'll need to include your new SQL Server tables in the Access database as linked tables in order for these objects to continue to work.

If you want to include your Access queries within the SQL Server database, you'll need to rewrite them for SQL Server as views for select queries or stored procedures for update or insert queries. This is not always straightforward, as the syntax is different between the two products (You may use IIF() in Access which you would need to translate to a CASE statement in SQL Server, for example).

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

Microsoft have an app for that, they call it their upsizing wizard, details are on technet

I have run through it previously to see what happens and its pretty straight forward.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have used the upsizing wizard in access several times and it is indeed quite painless most of the time.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.