11 March 2007

Upsizing To SQL Server

The Upsizing Wizard has been available with numerous versions of Microsoft Access to migrate data in a Microsoft Access (Jet) back end to the tables in a SQL Server database. It did a good job for the most part, but if your table structures had any discrepancies, some of the columns wouldn't migrate to SQL Server. Even SELECT queries might not be converted to views in SQL Server. Migrating data to SQL Server and linking to the tables was not a seemless operation, because invariably some glitches would keep the newly migrated data in the linked tables from being accessed by the original queries.

For SQL Server 2005, Microsoft created a better data migration tool, the SQL Server Migration Assistant for Access (SSMA Access). It will migrate the data from Microsoft Access 97 through Access 2003 (and likely 2007, too, but I haven't tried it yet) to SQL Server 2005, including the SQL Server 2005 Express Edition. This tool converts the original database objects into SQL Server database objects, loads these objects in SQL Server, and then migrates the data from the original tables into the new SQL Server tables. This is done in a single step.

Microsoft Access developers should have a little easier time migrating data with this tool when compared to the additional "fix-it" work required with the Upsizing Wizard, since a number of the issues with the Upsizing Wizard are fixed in the SSMA Access tool. In addition, network scanning, conversion assessment reports, and other features are now available in the SSMA Access tool.

No comments: