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!
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).
answered Jan 21 '10 at 02:04 PM